Manage Learn to apply best practices and optimize your operations.

Defending against SQL injection

Learn how to defend against SQL injection in this excerpt from "The Database Hacker's Handbook: Defending Database Servers" by David Litchfield, Chris Anley, John Heasman and Bill Grindlay.

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

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.

Dig Deeper on SQL Server Security

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.