Tuesday, January 6, 2009

SQL Injection Prevention

f you have ever taken raw user input and inserted it into a MySQL database
there's a chance that you have left yourself wide open for a security issue
known as SQL Injection. This lesson will teach you how to help prevent
this from happening and help you secure your scripts and MySQL statements.

What is SQL Injection

SQL injection refers to the
act of someone inserting a MySQL statement to be run on your database without
your knowledge. Injection usually occurs when you ask a user for input, like
their name, and instead of a name they give you a MySQL statement that you will
unknowingly run on your database.

SQL Injection Example

Below is a sample string that has been gathered from a normal user and a bad
user trying to use SQL Injection. We asked the users for their login, which will
be used to run a SELECT statement to get their information.

MySQL & PHP Code:



The normal query is no problem, as our MySQL statement will just select
everything from customers that has a username equal to timmy.

However, the injection attack has actually made our query behave
differently than we intended. By using a single quote (') they have ended the
string part of our MySQL query

  • username = ' '

and then added on to our WHERE statement with an OR clause of 1 (always

  • username = ' ' OR 1

This OR clause of 1 will always be true and so every single entry
in the "customers" table would be selected by this statement!

More Serious SQL Injection Attacks

Although the above example displayed a situation where an attacker could
possibly get access to a lot of information they shouldn't have, the attacks can
be a lot worse. For example an attacker could empty out a table by executing a
DELETE statement.

MySQL & PHP Code:


If you were run this query, then the injected DELETE statement would
completely empty your "customers" table. Now that you know this is a problem,
how can you prevent it?

Injection Prevention - mysql_real_escape_string()

Lucky for you, this problem has been known for a while and PHP has a
specially-made function to prevent these attacks. All you need to do is use the
mouthful of a function mysql_real_escape_string.

What mysql_real_escape_string does is take a string that is going to
be used in a MySQL query and return the same string with all SQL Injection
attempts safely escaped. Basically, it will replace those troublesome quotes(')
a user might enter with a MySQL-safe substitute, an escaped quote \'.

Lets try out this function on our two previous injection attacks and see how
it works.

MySQL & PHP Code:



Notice that those evil quotes have been escaped with a backslash \,
preventing the injection attack. Now all these queries will do is try to find a
username that is just completely ridiculous:

  • Bad: \' OR 1\'

  • Evil: \'; DELETE FROM customers WHERE 1 or username = \'

And I don't think we have to worry about those silly usernames getting access
to our MySQL database. So please do use the handy mysql_real_escape_string()
function to help prevent SQL Injection attacks on your websites. You have no
excuse not to use it after reading this lesson!


Post a Comment


Subscribe in Bloglines Msn bot last visit powered by MyPagerank.Net Yahoo bot last visit powered by MyPagerank.Net
I heart FeedBurner downtime checker The Ubuntu Counter Project - user number # 31290

Twitter Delicious Facebook Digg Stumbleupon Favorites More

Powered by TadPole