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:
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:
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.
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:
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:
http://sql.example.org/news?id=100
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:
The above example, obviously, will only work on integer inputs, if the id parameter was instead a string input like this:
http://sql.example.org/news?page=example
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:
- Boolean
- Error
- Union
- Stacked
- Time
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.
Exploitation
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
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.
Union Based
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:
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:
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 --
Blind Boolean
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:
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!)
Time-Based Blind
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:
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:
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:
SELECT sElEcT
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
Web Application Defense: Filtering User Input
Effectively filtering user input is one of the best ways to prevent an awful lot of web application vulnerabilities. There are several ways to approach this, each with their own pros and cons so I’ll run through them here an then you can think of the best way to combine them for your context. It’s important to remember though, that filters are context specific, there is not one filter that will work for a whole application and that’s what can make writing an effective filter tricky.
Blacklisting
The problem with rejecting, or stripping, known bad is two fold – the first is that the attacker likely has a lot of flexibility in the payload of choosing, for example if you look to block the word <script>to prevent a simple cross-site scripting attack and you choose to strip that dangerous input an attacker has a few options, for example they could change the input to <sCrIpT> which has the same effect but might sneak past a naive filter. Alternatively the attacker could use the following payload: <scr<script>ipt> so that when the filter is applied and the <script> string is removed from the input the two pieces either side “<scr” and “ipt>” respectively are placed together, effectively bypassing the filter.
The second issue is that an attacker may utilize encodings to bypass character filters, for example by using URI encoding it may be possible to cause the string %3Cscript%3E to be interpreted in the same way as <script> would be. However there are often a number of encoding types available and it’s not always possible to know ahead of time all possible payloads, so blacklisting is rarely completely effective when deployed alone.
Whitelisting
It follows sensibly then that if rejecting known bad is ineffectively then the opposite approach might be effective. The short answer is that it is, however the problem is implementing an effective filter of this nature in the real world. Take for example an input requesting a user’s mobile phone number, the character set could be restricted to just numbers and a maximum or say 15 characters however consider an advertisement placed on an online auction as an example, that’s a much harder input to whitelist. If a blacklist is difficult to write because it’s not possible to know all potential payloads a whitelist is difficult to write because it may not be possible to know all potential good inputs.
Encoding
A third option then, is to accept input with potentially dangerous characters and simply encode them so that they become benign. In most cases HTML entity encoding the best option for web applications, consider the original example of the payload <script> this can be changed so that the potentially dangerous characters < and > are entity encoded into the form < and > respectively. That way if they are rendered to the screen they will show correctly to the user however could not be utilized for a cross-site scripting attack. Another example would be the SQL injection payload of ‘ OR 1=1 — , this could be represented as: ' OR 1=1 -- so that it could not be interpreted within a SQL query successfully therefore defending against the attack but benign payloads such as Paul O’brien-Shea would be processed as Paul O&pos;brien-Shea but would still be rendered “correctly” to the user within a web browser.
However bear in mind that the above options do not have to be implemented independently and a hybrid approach could be adopted, an possibly effective approach could be something like:
- Decode input recursively until no further decoding is possible
- Recursively strip known malicious sequences, in a strip occurs then repeat step 1.
- Encode potentially dangerous characters
If case is considered with the above (i.e. script and ScRiPt are both handled) then this approach will prevent an attacker using encoding to bypass a filter, it will prevent an attacker utilizing the stripping function of the filter to build up malicious payloads (as the filter runs recursively) and finally if a genuine input includes potentially dangerous characters and an encoding type such as HTML entity encoding is chosen then the output will be rendered correctly to the user. Dangerous characters should include the below list however you could consider encoding all none alpha-numberic characters.
Minimum Characters to be considered dangerous:
" ' ; - < > =
No comments :
Post a Comment