Friday, May 16, 2008

Avoiding SQL Injection Attacks

SQL Injection exploits a security vulnerability which can exist in the database access layer. This can occur when user input is not checked for an escape character or is not strongly typed.

The best way to prevent SQL Injection is through the use of parameterized queries. Parameters are a place holder in the SQL statement which are populated with a value. This value is then used in the comparison. Consider the following:

sStmt = "SELECT * FROM Members WHERE userid = '" + sUserID + "'";

The value of the sUserID variable will become part of the SQL statement. A user could potentially use a string such as "Mike' OR  'a' = 'a". This would generate the following statement:

SELECT * FROM Members WHERE userid = 'Mike' OR 'a' = 'a'

This query would return all the user information since 'a' = 'a' will always evaluate to true. Additional SQL statements could also be inserted in a similar fashion. For example the user could enter "Mike';DROP TABLE Members;" resulting in the following statement:

SELECT * FROM Members WHERE userid = 'Mike';DROP TABLE Members;

Using a parameter ensures that the value provided will be used for comparison. As opposed to being sent to the server to be processed as an SQL command. Replacing the above examples with the following statement removes the injection threat.

SELECT * FROM Members WHERE userid = :username

Any input with additional SQL commands would be processed as a string to compare to the userids in the table most likely resulting in no match. However, parameters can not be used with every SQL statement. They can only be used in the where clause so other measures must be taken if the user must input a table name for example.

An easy way to check for an injection attack in this case is to search the string for a ";" in the body of the statement. With Visual Studio this can be done using the Contains function of your string variable (see below).

   1: // Check for SQL injection
   2: if (sStatement.Contains(";"))
   3: {
   4:     IDbCommand oErrCommand = oStateInfo.DataConn.CreateCommand();
   5:     oErrCommand.CommandText = "INSERT INTO __error VALUES( 1001, 'SQL scripts are invlid for this function' )";
   6:     oErrCommand.ExecuteNonQuery();
   7: }

Other options include providing appropriate values from a pick list to prevent injection. You can also limit the length of the value or strongly type the value. If you need a dollar amount for example use a double instead of a string variable. In the following example dSalesGoal is a double and will cause an error if a string value is used.

sStmt = "SELECT Name, TotalSales FROM Employee WHERE TotalSales > " + dSalesGoal;

Although SQL Injection attacks are rare it is a good idea to put in measures to prevent them. Especially in web based applications.

No comments: