SQL injection attacks have been compromising web applications for over a decade. According to research from Positive Technologies, the prevalence of SQL injection vulnerabilities has remained consistently high year over year. Their most recent study found SQLi at #2 in the top 10 most critical web application vulnerabilities.
Industry verticals most affected by SQL injection over a recent 12 month period include:
- Education – 15% of attacks
- IT – 14% of attacks
- Retail – 12% of attacks
High profile examples of SQLi-related data breaches include:
- Equifax – exposed personal information of 143 million consumers
- Heartland Payment Systems – 130 million credit cards compromised
- TalkTalk – leaked 157,000 customer records containing sensitive private data
Clearly, if your business collects or transmits sensitive information online, SQL injection attacks pose a major risk. Keep reading to learn how to properly test for and ultimately prevent SQLi vulnerabilities.
What Exactly is a SQL Injection Attack?
SQL injection involves inserting malicious SQL syntax into application input fields to gain unauthorized access to the database layer of an application. It takes advantage of improper coding practices that don‘t properly validate user input.
For example, consider the following vulnerable login code:
String query = "SELECT * FROM users WHERE name = ‘" + inputUsername + "‘ AND pass = ‘" + inputPassword + "‘";
This query constructs a SQL statement by concatenating unvalidated user input into the string. An attacker can manipulate this behavior to bypass authentication:
--inputUsername = admin‘ -- --inputPassword = foo
This input gets inserted into the query:
SELECT * FROM users WHERE name = ‘admin‘ -- ‘ AND pass = ‘foo‘
The double dashes comment out the remainder of the query, allowing the attacker to log in as admin without supplying the real password.
Through common SQL injection points like unvalidated input, attackers can access and destroy database contents, execute administration operations on the database server, recover secret data, and in some extreme cases issue commands to the underlying operating system. Clearly, SQLi is an extremely dangerous vulnerability if left unaddressed.
Conducting Manual SQL Injection Testing
The first step in securing against SQLi attacks is thoroughly testing your application to identify vulnerabilities. While automated tools excel at finding common issues quickly, nothing beats intelligently performed manual testing to uncover logical flaws. Let‘s explore some useful manual SQL injection testing techniques:
Injecting Single Quotes
In SQL, single quotes are used to delimit string literals. Injecting an additional, unexpected quote character into an unsafe parameteter can frequently cause a syntax error exposing the flaw:
https://example.com/login?username=‘
The application may return the SQL interpreter‘s complaint about unmatched quotes, confirming exploitability.
Commenting Out Queries
We previously saw how comments can be leveraged to ignore certain query conditions entirely. Test parameters expecting numeric values by appending comment syntax:
https://example.com/login?userId=105‘--
This passes 105 as the userId, while everything after — is ignored. If successful, the application‘s behavior exposes the SQLi vulnerability.
Parameterizing Values
Instead of concatenating user input directly into the constructed query, best practice is to separate values using placeholders:
SELECT * FROM users WHERE name = ?
Input data is then supplied securely through parameters instead of mashing statements and values. This makes SQL injection impossible.
Introducing Time Delays
Certain SQL functions like BENCHMARK() and the WAITFOR DELAY command can be used to introduce measurable time delays in responses based on injected numeric values. By parameterizing delays and calculating differences in response times, SQL injection potential can be confirmed.
These examples demonstrate basic but extremely effective manual testing tactics. Skilled testers can uncover logical vulnerabilities through intelligent probing that automated scanners may miss.
Leveraging Automated SQL Injection Tools
While manual testing is extremely useful, running comprehensive test cases by hand is tedious and time-consuming. Thankfully, security researchers have developed many excellent automated SQL injection tools over years of battle against SQLi attacks:
SQLMap
SQLMap is likely the most popular free open source penetration testing tool focused specifically on detecting and exploiting SQL injection flaws. Completely customizable through command line flags or a configuration file, SQLMap supports integration with many popular databases including MySQL, Oracle, Postgre, Microsoft SQL Server and more.
SQLMap implements checks for many types of SQL injections from boolean blind to time delays to UNION queries and can fingerprint backend database types and schema structures. Available attacks range from simple data extraction to operating system command execution.
python sqlmap.py -u "http://example.com/login?username=test" --batch --banner
This example runs sqlmap against the specified URL, suppresses unnecessary output through –batch, and retrieves database server banner info via –banner.
sqlninja
sqlninja is an open source SQL injection tool written in Perl that scans web requests in real-time for injections, automatically generates test attacks exploiting found vulnerabilities, and performs queries like dumping database contents. sqlninja transparently proxies all web traffic through itself to conceal its presence and evade detection.
perl sqlninja.pl -listen 3000 -target 192.168.100.150
This launches sqlninja on port 3000 and sets the target for back-end database fingerprinting and attacks to IP 192.168.100.150. Any non-SSL web traffic routed through port 3000 will be actively probed for SQLi using numerous techniques.
Plenty of Other Options
Many more excellent automated SQL injection tools have been developed, both open source and commercial, including JSQL, Havji, SQL Power Injector, Netsparker, Acunetix, Burp Suite, and OWASP ZAP. Experiment with multiple automation options suited to your testing needs.
Preventing SQL Injection Vulnerabilities by Design
While access to exploitable SQL injection flaws allows attackers potential impact, enough precautions can reduce risk to negligible levels:
Validate All User-Supplied Input
The first line of defense is validating all user-controlled input before incorporation into SQL queries. Whitelists, blacklists, size limits, type checking, and business rule checks should all be used to constrain what values an attacker can inject.
For example, this PHP code accepts a numeric userId variable constrained to max length 10 digits:
$inputUserId = $_GET[‘userId‘];
if (strlen($inputUserId) > 10 || !is_numeric($inputUserId)) {
die("Invalid input");
}
Escape All Input Prior to SQL Usage
Even after validation, input should be escaped to sanitize dangerous characters before interpolation into SQL strings. This means stripping quotes, semicolons, dashes, whitespace, and more.
In PHP, the mysql_real_escape_string function handles this:
$escapedUsername = mysql_real_escape_string($inputUsername);
Exclusively Use Parameterized Queries
As mentioned earlier, parameterized SQL queries completely separate user input data from SQL language elements, making injection impossible. Use parameterized queries for all application database access.
Below uses PHP PDO parameter binding:
$query = $db->prepare("SELECT * FROM users WHERE name = :username");
$query->bindValue(‘:username‘, $inputUsername);
SQL literals are passed securely through placeholders rather than splicing strings.
Enforce Least Privilege Rules
The database user account used by an application should have minimum permissions required for intended functionality and no more. Avoid overprivileged roles like db_owner that enable destructive actions. This significantly limits damage from potential successful attacks.
Here Bob‘s application account has only SELECT and INSERT access:
GRANT SELECT, INSERT ON application.* TO ‘bob‘@‘appserver.com‘;
Implement a Web Application Firewall
WAFs provide deep packet inspection of all network traffic flowing to web applications, enabling policy-based blocking of SQLi and other attacks. Installing a WAF provides critical redundancy behind smart application coding and validation practices.
Remediating Existing SQL Injection Vulnerabilities
If testing reveals existing issues, systematically remediate each validated vulnerability through:
Patching vulnerable code
Unsafe practices like concatenated query construction must be refactored to use modern APIs with parameterized queries.
Sanitizing unsanitized user input
All previously unvalidated web inputs must be checked for allowable formats, lengths, special chars etc.
Migrating risky SQL statements to stored procedures
Encapsulate complex query logic ignorant of input values into procedures called with parameters.
Switching verbose error messages to generic pages
Friendly debug output leaks backend details attackers leverage to recon targets.
Apply additional across-the-board hardening:
Web Application Firewalls
Install a WAF to monitor all traffic and block SQLi attack patterns.
Insider Threat Scanning
Inspect internal actions for risks like developers exporting full databases.
Database Access Logging
Audit all queries, logins, and actions against critical data stores.
Following this comprehensive guide will significantly improve resilience against SQL injection and prevent future compromises.
Conclusion
SQL injection remains one of the most dangerous and prevalent web application vulnerabilities – but with vigilance, it is preventable. This guide equipped you with multiple methods for finding flaws through manual probing and automated scanning. We explored various secure coding best practices like input validation, sanitization, and parameterized queries that mitigate injection risks. Additionally, supplemental controls like minimized database privileges, web application firewalls, visibility into insider actions provide overlapping defenses.
By consistently applying these tools and techniques as part of an ongoing application security program, you can rest easy knowing your web systems and sensitive data remain safe from SQLi attacks over the long term.
