Causes of and Cures for SQL Injection

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,"'","''")

next page

By-Pass authentication using SQL Injection