Hack 48. Prevent an SQL Injection Attack

You can take steps to prevent an SQL injection attack. You can also minimize the consequences of an SQL injection attack.

Preventing an SQL injection attack is simply a matter of escaping values that come from web pages.

When you escape a string you replace special characters with escape sequences. For string input the only special character you need to worry about is the single quote. You must turn a string such as O'Reilly's into O''Reilly''s. Each single quote becomes two single quotes:

SELECT name FROM users WHERE name='O''Reilly' AND passwd='tiger'

If someone attempts an SQL injection attack [Hack #47] he could enter the magic string ' OR ''=' as both the username and the password. If you handle the input properly the result will be the perfectly legal and perfectly harmless SQL query:

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

 

6.8.1. Escaping in Perl

In Perl you can ensure that such variables are dealt with properly by using placeholders in your SQL statements, as outlined in "Filter Rows and Columns" [Hack #8].

You can also use the $dbh->quote method. The sprintf function takes a format string followed by a number of parameters. The %s directives are replaced by these parameters in order:

my $dbh=DBI->connect('DBI:mysql:scott','scott','tiger');
$sql = sprintf "SELECT name FROM users WHERE name=%s AND passwd=%s",
 $dbh->quote($Q::name), $dbh->quote($Q::passwd);

The quote method will use the appropriate quoting mechanism for the database that you are using. This might be '' or '.

6.8.2. Escaping in C#

In the .NET environment you can pass parameters to an SQL Server stored procedure, or you can escape the string yourself in C#.

String.Format is ideal for the job, when coupled with the Replace method. You might prepare an SQL statement as follows:

String sql = String.Format(
 "SELECT name FROM users WHERE name='{0}' AND passwd='{1}'",
 name.Replace("'","''"),
 passwd.Replace("'","''")
 );

The {0} and {1} placeholders get replaced with the second and third parameters of the Format method.

6.8.3. Escaping in PHP

In PHP, you can use mysql_real_escape_string(name) if you are using the mysql library. A better solution is to use Pear's DB module, and you can find out how to do this in Hack #35, Create Bulletproof Database Access, in PHP Hacks by Jack Herrington (O'Reilly). The same book also has useful information on storing encrypted passwords (Hack #57, Create a Login System) and fixing a weak password system (Hack #59, Migrate to MD5 Passwords).

6.8.4. Escaping in Java

In Java you can use the replaceAll string method:

name.replaceAll("'","''")

 

6.8.5. Nonstring Data

You must take care to ensure that all values coming in over the Web are properly checked. If you are expecting a number, you should check that it is composed of digits only. If you don't check that, the numeric input can easily be used to launch an SQL injection attack.

Even if you are not concerned about SQL injection, you should check the data format before putting it into an SQL statement because a runtime error generated by a type mismatch can have a disproportionate cost in terms of server load.

6.8.6. JavaScript Validation, Cookies, and Hidden Variables

You can have JavaScript validation and size limits on fields in the HTML, but do not expect these to protect your system. A user could save your form page on her desktop. She could then edit her version of your page and alter or disable the JavaScript and field size limits.

Also beware of hidden variables and cookies. Under normal circumstances, your scripts control these values, but it is not at all difficult for someone to make up her own values. An external user can view all of the hidden variables and cookies associated with a web page from Firefox, for instance. Look in ToolsPage Infoimages/U2192.jpg border=0>Forms for the variables, including hidden variables, as shown in Figure 6-18. Look in Toolsimages/U2192.jpg border=0>Optionsimages/U2192.jpg border=0>Privacyimages/U2192.jpg border=0>Cookiesimages/U2192.jpg border=0>View Cookies for the cookies, as shown in Figure 6-19.

Figure 6-18. Listing variables, including hidden variables

Figure 6-19. Listing cookies associated with a page

 

6.8.7. Exploits Using Hidden Variables and Cookies

An SQL injection attack based on hidden variables is not any more difficult than one using visible components, such as textboxes or a password. You can't do this directly from the web browser, but you can set variables as you choose if you use a command-line prompt utility such as lwp-request or wget. See Spidering Hacks by Kevin Hemenway and Tara Calishain (O'Reilly) for information on how to do this.

6.8.8. Restrict the Rights of the SQL Account

As well as preventing SQL injection attacks by escaping, it is sensible to limit the privileges of the account that you use to access your SQL system. It is good practice to ensure that the account being used has exactly the right permissions required and no more.

6.8.9. Don't Overreact

The basic SQL injection attack will not reveal your SQL user password or your operating system passwords. Only data in the database is exposed. It may be possible for someone to obtain an encrypted version of your SQL account password, but if you have chosen a sound password, that does not constitute a threat.

You should not underestimate the power of an SQL injection attack, but neither should you overestimate it. My site, http://sqlzoo.net, is vulnerable to SQL injection attacks by its very nature, and it has been running fairly smoothly for several years. I can't guard against SQL injection because I allow users to execute any SQL command they want against several different SQL engines. I use SQL GRANT and REVOKE commands to hide the tables I don't want users to see, and I deny write permission to the tables that I want users to see but not to change.

6.8.10. See Also

• "Exploit an SQL Injection Vulnerability" [Hack #47]
• "Implement Application-Level Accounts" [Hack #90]
• "Find and Stop Long-Running Queries" [Hack #98]