Protect databases from SQL injection attacks

by Arthur Fuller | Jul 21, 2005 7:00:00 AM

Tags: Programming languages, Databases, ASP Page, Arthur Fuller, SQL, SQL injection, injection, database, SQL Server Newsletter

Takeaway: In a SQL injection, attackers attempt to damage your database by a "injecting" a SQL statement into another statement. Learn simple methods for preventing injections from penetrating your system.

SQL injection is a strategy for attacking databases. The attacker "injects" a SQL statement into another statement—often to inflict damage upon your database. Web sites that interface with databases are particularly vulnerable to SQL injection because they often rely on dynamic SQL. Here's a simple example.

An ASP page asks the user for a name and a password, and then sends the following string to the database:

SELECT FROM users WHERE username = 'whatever' AND password = 'mypassword'

It seems safe, but it isn't. A user might enter something like this as her user name:

' OR 1>0 --

When this is plugged into the SQL statement, the result looks like this:

SELECT FROM users WHERE username = '' OR 1>0 -- AND password = ''

This injection comments out the password portion of the statement. It results in a list of all the names in the users table, so any user could get into your system.

The easiest way to prevent this sort of injection is to parse the SQL string and remove any occurrences of "--" before passing the statement.

You also have to beware of injections that contain semicolons because semicolons delimit SQL statements. Think about the implications of a user name like this:

' OR 1>0 ; DELETE Customers ; --

There are numerous ways a malicious user might penetrate your system using SQL injection and various defenses, but the simplest approach is to avoid dynamic SQL. Instead, use stored procedures everywhere. Thanks to the way SQL passes parameters, injections such as those above will produce errors, and the stored procedure will not execute.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!