My blog has moved and can now be found at http://blog.aniljohn.com

No action is needed on your part if you are already subscribed to this blog via e-mail or its syndication feed.

Friday, May 21, 2004
« Patterns & Practices Site has a new look... | Main | System.Security.Cryptography functionali... »

All relational databases are susceptible to SQL injection attacks. The following SQL Server Magazine article teaches you four important steps to protecting your Web applications from SQL injection attacks.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag04/html/InjectionProtection.asp

The above came across on the MSDN feed. I thought I would add a bit to it.

SQL Injection attacks occur when applications use input to construct dynamic SQL statements to access the database. One of the often quoted defenses against this type of attack is to use stored procedures.  That is a good start. Just remember that SQL injection attacks can also occur if your code uses sprocs that accept strings which contain unfiltered user input.  This attack gets exponentially worse if the application is using an over privileged account to connect to the database.

You prevent SQL Injection using the following tactics:
 
  • Constrain the input by validating it for type, length, format and range. Remember, ALL INPUT IS EVIL, until proven otherwise!
  • Use type safe SQL parameters. The parameter collection in SQL provides type checking and length validation. So if you use the Parameters collection, input is treated as a literal value and SQL does not treat it as executable code.  Another point is that the Parameters collection can be used to enforce type and length checks so that values outside of the range trigger exceptions. You can use the Parameters collection with both sprocs as well as dynamic SQL.
  • Use filter routines that sanitize the code by adding escape characters to characters that have special meaning to SQL. An example would be adding an escape character to the single apostrophe character. Keep in mind that these type of filter routines can be bypassed by an attacker that uses ASCII hex characters. So they should be used as just another part of your defense in depth strategy.
A more complete treatment can be found in "Chapter 14: Secure Data Access" of the PAG volume "Improving Web Application Security"
 
Tags:: Security
5/21/2004 5:50 PM Eastern Daylight Time  |  Comments [3]  |  Disclaimer  |  Permalink   
Sunday, May 8, 2005 12:06:45 AM (Eastern Daylight Time, UTC-04:00)
Security wiseman Anil John summarizes, links to and then adds to a great article on protecting against SQL Injection attacks. You prevent SQL Injection using the following tactics: * Constrain the input by validating it for type, length, format and range. Remember, ALL INPUT IS EVIL, until proved otherwise! * Use type safe SQL parameters. The parameter collection in SQL provides type checking and length validation. So if you use the Parameters collection, input is treated as a literal value and SQL does not treat it as executable code. Another point is that the Parameters collection can be used to enforce type and length checks so that values outside of the range trigger exceptions. You can use the Parameters collection with both sprocs as well as dynamic SQL. * Use filter routines that sanitize the code by adding escape characters to characters that have special meaning to SQL. An example would be adding an escape character to the single apostrophe character. Keep in mind that these type of filter routines can be bypassed by an attacker that uses ASCII hex characters. So they should be used as just another part of your defense in depth strategy....
Ensight - Jeremy C. Wright
Sunday, May 8, 2005 12:06:45 AM (Eastern Daylight Time, UTC-04:00)
Take Outs for 21 May 2004
Enjoy Every Sandwich
Sunday, May 8, 2005 12:06:45 AM (Eastern Daylight Time, UTC-04:00)
Just don't ever construct queries out of string fragments. Don't use dynamic exec on the server side either! Use parameters, no constants, and you'll never have to worry about escaped characters and whatnot.
Matt
Comments are closed.