Defending against SQL injection

The following excerpt, courtesy of Wiley Publishing, is from Chapter 22 of the book "The Database Hacker's Handbook: Defending Database Servers" written by David Litchfield, Chris Anley, John Heasman and Bill Grindlay. Click for the complete book excerpt series or purchase the book.

Defending Against SQL Injection

Despite SQL injection's well-earned reputation as a relatively common and dangerous SQL Server attack vector, there are several ways to protect against this type of attack. The first, and most obvious, is to ensure that Web applications properly validate user-supplied input. Input can be filtered so that only known good input is accepted, known bad input could be stripped out, bad input could be escaped, and finally, bad input could be rejected entirely. Often a combination of these approaches is the best solution.

The idea behind allowing only known good input is defining a set of permitted characters for each data type used by the application. A telephone number input field, for example, would only accept the digits 0 to 9; a surname field should only contain upper- or lowercase letters from A to Z. The application could also be programmed to reject SQL keywords such as select or exec. Care should be taken to ensure that all possible keywords are included. A filter checking for the select keyword could be bypassed by alternative encodings:

exec('sel'+'ect * from sysxlogins')

and by converting the entire query to a hex string using the function fn_varbintohex:

select master.dbo.fn_varbintohexstr(CAST('select * from sysxlogins' as
varbinary))
0x73656c656374202a2066726f6d20737973786c6f67696e73

The following query could then also be attempted, bypassing checks on the select keyword:

declare @query varchar(128); set @query =
0x73656c656374202a2066726f6d20737973786c6f67696e73; exec(@query)

Escaping submitted characters in a Web application means treating them as literal data rather than part of a possible SQL query. For example, if a single quote character (') is submitted within user input, the application will replace it with two single quotes (''), which means that within any SQL query this input will be treated as a literal single-quote character. This approach has the added benefit of correctly processing surnames that may contain single quotes (O'Neill, O'Reilly, and so on). A hazard when using character escaping can be introduced if length limits are applied to any of the input fields; length limits may be applied by the application to reduce the risk of buffer overflow attacks. Using the example application in the previous section, and supposing that single-quote characters are escaped and that the username field is limited to 25 characters, the following username is submitted:

test ' ' ' ' ' ' ' ' ' '

The application then escapes the single-quote characters by replacing them with double single quotes, and truncates the result to its limit of 25 characters. The final single quote is removed from the end, meaning that the single quote before it is no longer escaped. The resultant string: test ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '

allows SQL statements to be injected into the password field. So a password of

; drop table users—

would delete the entire users table. The effective query formed by the application will be

SELECT * FROM users WHERE username = 'test ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' AND

password = '; drop table users--';

The usefulness of injected queries is restricted by the length limit, but it is possible to drop tables or shut down the server with short queries. Care should be taken when writing the input parsing code that escape characters are not deleted by length limits.

The safest method that can be employed is to reject any input not explicitly classified as "good." A possible drawback may be that improperly defined filters could block access to users, so it is important that all rules are thoroughly tested.

As well as the application code, the security of the SQL Server itself should be a concern. Basic security measures consist of the following:

• A well-configured firewall to block everything apart from connections from the Web server and the database administrator.
• The Web app should connect to the database with the minimum of privileges required to access the data — not as the sa user.
• Powerful stored procedures that access the registry and run commands should be restricted to system administrators.
• Permissions granted to the public role should be strictly controlled.
• All relevant security patches should be applied to prevent privilege escalations.

Click for the next excerpt in this series: Covering tracks

Click for the complete book excerpt series.

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL Server Security Password cracking tools for SQL Server Meet compliance requirements with improved database security practices Hardening the network and OS for SQL Server security Securing the server and database in SQL Server SQL Server security made simple and sensible Blog: Protect your databases from the internal threat Setting up SQL Server Service Broker for secure communication The keys to database backup protection for SQL Server Understanding transparent data encryption in SQL Server 2008 The fine line between not encrypting your databases and breach notification

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary data corruption  (SearchSQLServer.com) data hiding  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary