Southern Bread Blog

I want to do a couple of posts on SQL injection attack prevention. I
am going to show some of the techniques I use to ward them off. For example,
we use a three pronged approach at
the authentication point: variable binding, row counting, and syntax
detection. When a username and password are entered on the login form
we check to make sure that there is nothing obviously wrong with the
input, like password being of acceptable length and such. The next
thing we do is check to make sure there is no known SQL syntax within
the username or password. For example, if someone inputs a password
like this:

’ OR 1=1

it will get rejected at this step. All of the SQL keywords are
stored in big lookup table and checked against. The next step then is
to bind the variables instead of passing them in as plain strings. This
is a crucial step to avoid SQL injection. So instead of:

$sql="SELECT * FROM users WHERE username="$username" AND 
password="$password" LIMIT 1

we use:

$sql="SELECT * FROM users WHERE username=? AND password=? LIMIT 1
$sth=$dbh->prepare($sql);
$sth->execute($username,$password);

The final thing we do is check the row count of the result set. Even
though we used “LIMIT 1″, if there is an injection going on then we must
assume that it has been changed. Be sure and check that you have a row
count that is sane for the operation you are performing. If you are
logging someone in then you should return an error if the result count
is 0 or greater than 1, like this:

die unless($sth->rows() eq 1);

Next time I’ll focus on SQL injection that happens beyond the front
gate. Sometimes you can’t be so strict on row counts and syntax checks
once a user is inside.