Causes of and Cures for SQL Injection

The SQL Injection attack is possible when the programmers who write the code behind the page neglect to properly escape strings that are used in SQL queries.

Programs common build SQL queries such to check values. For example the SQL statement: will return one row if the given user/password combination exists in the table users.

SELECT name FROM users WHERE name='scott' AND password='tiger'

Of course the values scott and tiger are taken from values entered by the user. To build the SQL query the Perl programmer might use a line such as:

$sql = "SELECT name FROM users WHERE name='$Q::name' AND password='$Q::password'"

The VB programmer might use something like:

sql = "SELECT name FROM users WHERE name='" & name & "' AND '" & password & "'"

In both cases the sql string generated will be invalid SQL if the variable name contains a single quote.

Worse, a sneaky user might enter a string that results in valid SQL, but SQL that generates unexpected reults.

Cures

The cure is simply to escape single quotes properly. In most cases that mean substitute a single quote with two single quotes. In Perl you could use:

$sql = sprintf 'SELECT name FROM users WHERE name=%s AND password=%s,
   $dbh->quote($Q::name),$dbh->quote($Q::password);

In VB you could use:

sql = "SELECT name FROM users WHERE name='" & replace(name,"'","''") & _
          "' AND password='" & replace(password,"'","''")