An SQL injection needs just two conditions to exist – a relational database that uses SQL, and a user controllable input which is directly used in an SQL query.
In the example below, it shall be assumed that the attacker’s goal is to exfiltrate data from a database by exploiting an SQL injection vulnerability present in a web application.
Supplying an SQL statement with improper input, for example providing a string when the SQL query is expecting an integer, or purposely inserting a syntax error in an SQL statement cause the database server to throw an error.
Errors are very useful to developers during development, but if enabled on a live site, they can reveal a lot of information to an attacker. SQL errors tend to be descriptive to the point where it is possible for an attacker to obtain information about the structure of the database, and in some cases, even to enumerate an entire database just through extracting information from error messages – this technique is referred to as error-based SQL injection. To such an extent, database errors should be disabled on a live site, or logged to a file with restricted access instead.
Another common technique for exfiltrating data is to leverage the UNION SQL operator, allowing an attacker to combine the results of two or more SELECT statements into a single result. This forces the application to return data within the HTTP response – this technique is referred to as union-based SQL injection.
The following is an example of such a technique. This can be seen on testphp.vulnweb.com, an intentionally vulnerable website hosted by Acunetix.
The following HTTP request is a normal request that a legitimate user would send.
GET http://testphp.vulnweb.com/artists.php?artist=1 HTTP/1.1 Host: testphp.vulnweb.com
Although the above request looks normal, the artist parameter in the GET request’s query string is vulnerable to SQL injection.
The SQL injection payload below modifies the query to look for an inexistent record by setting the value in the URL’s query string to -1 (it could be any other value that does not exist in the database, however, an ID in a database is less likely to be a negative number).
In SQL injection, the UNION operator is commonly used to allow an attacker to join a malicious SQL query to the original query intended to be run by the web application. The result of the injected query will be joined to the result of the original query, allowing an attacker to exfiltrate data out of a database by obtaining values of columns from other tables.
GET http://testphp.vulnweb.com/artists.php?artist=-1 UNION SELECT 1, 2, 3 HTTP/1.1 Host: testphp.vulnweb.com
The above example proves that the query to the database can be modified to return data which an attacker may want to extract. The following example shows how an SQL injection payload could be used to exfiltrate data from this intentionally vulnerable site.
GET http://testphp.vulnweb.com/artists.php?artist=-1 UNION SELECT 1,pass,cc FROM users WHERE uname='test' HTTP/1.1 Host: testphp.vulnweb.com
Read Part 1 in the Series: SQLi: How it works
Read Part 2 in the Series: What’s the worst an attacker can do with SQL?