SQL Injection

SQL Injection is a code injection technique to attack a web application. Generally malicious SQL statements are inserted into an entry field (or manipulate http request with malicious statement) for execution. The execution of malicious script might cause unauthorized access to content, deleting table form underlying database, becoming a database administrator, shutting down the database server and so on. SQL Injection is one of the most dangerous attack performed on web applications.

Mainly there are three types of SQL injection attacks

1. Error based attack

2. Union based attack

3. Blind attack

Error Based

Aim: The main goal of this kind of attacks is to generate different kind of SQL error messages, by manipulating the http requests that might bubble up to the error page.

This is a very basic type of SQL injection attack because the underlying database will tell us about the data structure and data through the error messages.

Union Based

Aim: The main goal of this kind of attacks is to construct a malicious query and union it with a legitimate query in the database. Database will produce two result set for two different query and union it to a single result set and show the data in the web page.

This type of attack is fairly simple to execute does not need in depth knowledge of underlying database.

Time Based

This type of attack is more advanced and need very good understanding of underlying database. Generally when error based and union based attacks does not work, this type of attacks are deployed.

Aim:  The main aim of this kind of attacks is to ask database several questions through injected queries and database is replying to those questions through the web page. Based on the reply of those question, attacker is assuming the underlying data structure and data.

This type of attacks are called “Blind” since attacker cannot see the data structure or data directly. Attacker has to assume the underlying data structure and data based on the reply to the questions s/he is asking to the database.

There are two types of Blind SQL Injection attacks:

1. Boolean based: In this type of blind attack, the reply given by the database server is of boolean in nature. Basically attacker is asking question like “If yes do this else do that” and the reply (this or that) is visible in web pages.

2. Time based: In this type of blind attack, the reply given by the database have time delay. The attacker is asking question like “If yes delay 20 second to give reply else reply immediately”. And this kind of delay can easily be determined by using a tool like “Fiddler”. So the attacker is forced the database server to delay if the answer of their question is yes.

Error based attack and other attacks in details

Error based attacks can be launched using http requests. The simplest one (as far as visibility is concern. We can see it on url ) is get and so get request is used throughout this article.

It’s a bit difficult so show the attacks without using a website. So we are considering a fictitious ecommerce web site www.myecommsite.com developed using ASP.Net MVC/SQL server and showing some url where attack can be launched.

Suppose you would like to search a specific type of book “SQL” in www.myecommsite.com. So you went to books section and clicked on “SQL” filter (the filters might be located at left/right/bottom region ). It shows all SQL books. The url is like this

www.myecommsite.com/booksByType?type=SQL

This url tells us a lot of things. Like there might be a table name items or books and type might be column in either of the table. The underlying database query that mapped with this url might be like

select * from books where type = ‘SQL’

It’s very important for an attacker to guess the mapped database query. Now if you guess right then you know that ‘SQL’ is taken from the url and placed in the query directly. So we call ‘SQL’ as untrusted data because that can be easily manipulate by user in url. Let’s do that and manipulated url is like below one

www.myecommsite.com/booksByType?type=SQL’

Seems to be same but if you observe carefully you will get to know that we have appended a single quote ( ‘ ) at the end. Single quote is a real hostile character as far as the sql injection is concern.

Now after manipulating submit that request (just hit enter button). Wow, we get an error page! Delighted! Gold mine! Feeling like a Joker!!!!! Hmm. The error page may be showing some error like

Server Error in '/' Application.

Unclosed quotation mark after the character string 'SQL''.

Incorrect syntax near 'SQL''.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Unclosed quotation mark after the character string 'SQL''.

Incorrect syntax near 'SQL''.

To show the error we have taken the error page from another website that is vulnerable to SQL Injection attack and changed the input string to SQL.

Now let’s try to understand why this error is appearing.

The specific part of the url that we are interested is  booksByType?type=SQL

The red part is the untrusted data where user can put anything they want.  The code that runs  the database query by taking data from url looks like

public ActionResult Bookindex ( string booktype )

{

var books = ‘select ID,Name,Price from Books where type =  ‘ “ + booktype + “ ‘ “;

         . . . . .

}

And the mapped database query will be Select ID, Name, Price from Books where type = ‘SQL’

In the url part everything is string but at the database-end it can be string or integer or other data type. When data is taken from the url, it is type casted to actual data type that is used in database. So our modified url is  booksByType?type=SQL’

And the mapped database query becomes

Select ID,Name,Price from Books where type = ‘SQL’’

The extra single quote in ‘SQL’’ is causing the error that bubble up to the web page. You can easily test the error message if you execute same type of query using SQL Server Management Studio. For demonstration purpose let us create a table called Books in our sql server with fields like ID, Name, Price, and Type and execute the same query. We will get the following error

/*------------------------

Select ID, Name, Price from Books where [Type] = 'SQL''

------------------------*/

Msg 105, Level 15, State 1, Line 1

Unclosed quotation mark after the character string 'SQL''.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'SQL''.

Now we can see the exactly the same error message is bubbled up in the error page. This error page is meant for developers so it is showing the correct error. But if this error page is kept on production server, then this kind of information is very “Yummy” to the attacker. Let’s continue this cracking journey with other meaningful attacks.

Attack URL:  

Underlying Database Query:

Result: All the book in the database irrespective of type will be listed in web page.

Reason: The condition 1=1 is always true, it will negate [Type] = 'SQL' in where clause and return all the books.  We also have used -- to comment the extra single quote (--‘). Otherwise the query would have returned same error page.

1=1 is a very special. It is called SQL tautology. In logic “tautology” is a formula that is true in every circumstance. So we have different SQL tautologies like ‘a’=’a’  , ‘a’ < ‘b’ , ‘a’ != ‘b’ . So the attacks can be

All of the above attacks will show all the book in the web page. Even you can see the result if you use this tautology against Books table using SSMS.

SQL tautology can be used to log into to any website that is vulnerable to SQL Injection and yes without knowing any username and password!

Suppose a sign in form looks like a below one.

Let’s try to understand the DB query associated with this sign in form. It might be like

The logic behind may be if count() is more than zero, authentication success. Now let see the attack.

Let’s see the query related to above sign in data.

SQL tautology 1=1 will negate username = ‘Joker’ and return number of users present in the table. Thus according to the authentication logic discussed above, Joker will be logged into the site successfully.

Authentication logic might be different. It might be if the count is exactly one then authentication success. Now we have to modify the attacked a little bit. Hmm ! Let’s see the new attack.

Did you notice the change ? ‘or’ is replaced by ‘and’. The only requirement in this SQL injection attack is Joker has to be a valid user of this web site. Let’s see the DB Query

Now the used SQL tautology 1=1  does not negate username = ‘Joker’ instead it require username = ‘Joker’ and that why Joker has to be a valid username. The query return exact one count and authentication is successful. Now you must be thinking why we need SQL  tautology to lunch this kind of attack, above attack should work without SQL tautology. Let’s see

Yes, it should work but only if username comes before password in the query. So it’s extremely important to guess the underlying query and launch the attack. Sometimes you might need to put that attack using SQL tautology in password field if in DB query password comes before username.

Let’s try to extend the attack on this login form which is vulnerable to SQL injection.

Result : Password for every user will be set to “joker”. Even for administrator. You can guess the username of administrator might be ‘admin’. So now you know the credentials for administrator and it is admin/joker. Following are couple of other situations that a mischievous attacker can do.

Result : Every book is cheaper by 10$. Purchase the book and then again set it back to original price ( price + 10 ).

Result: Users table is dropped. Very serious damage. Only possible only if the web application has privileges to change the underlying database schema.

Summary: SQL injection is one of the top most web application vulnerability and it is finding the top position in web application vulnerability list for last decade. To mitigate the risk of having SQL injection vulnerability, web application developers and testers must be very well versed and well equipped with SQL injection knowledge and new attack vectors.