Protecting MySQL from SQL Injection Attacks with PHP.

This is intended as a brief guide to protecting your MySQL database from SQL injection attacks. Unfortunately, a large amount of the code that I’ve seen written by people on forums, and in countless crappy PHP tutorials lurking around on the net, and in the many websites that display the magic breeding slashed-quote (''' see below) show that many people just do not understand what’s going on and how to protect themselves against SQL injection attacks.

In fact, the only reason that many websites are ‘protected’ is due to magic quotes, and given that this is due to be disabled in the forthcoming PHP6, then there’s going to be some major problems cropping up.

I’ll talk about the problem of SQL injection, the half-hearted attempt to fix it with these ‘magic quotes’, and what you should really be doing EVERY TIME you send user inputted data to your database.

The Problem What is SQL Injection:

As the name suggests, SQL Injection is quite simply, when the user injects SQL into your application. How does this happen? Say we have a nice simple login form that takes a username and password, and checks if that’s in the database. If it is, the user is logged into an admin section or something. The code for this could look something like this:

 
// user and password come from a simple POST'd form
  
$user = $_POST[ 'user' ];
$password = $_POST[ 'password' ];

$query = "SELECT name, age, credit_card FROM usertable WHERE username = '$user' AND password = '$password' ";

$result = mysql_query( $query );

// check if mysql found anything, and get the record if it did
  
if ( mysql_num_rows( $result ) > 0 ) {
    $data = mysql_fetch_assoc( $result );
    echo 'Hello '.$user.'!';
    echo 'Your credit card number is '.$data[ 'credit_card' ].'';
} else {
    echo 'Incorrect Username or Password! Go Away!';
}

Ok. This works, BUT it’s about as safe as juggling with scalpels. If I enter “simon” as my username, and “secret” as my password, then the query that goes to MySQL looks like this:

SELECT name, age, credit_card FROM usertable WHERE username = 'simon' AND password = 'secret'

and I get logged in quite happily. Fantastic.

The problem comes when I start entering other characters. Let’s say that the next user who tries to login is Peter O’Reilly. Naturally he’ll want a username something like PeterO’Reilly. If we plug that into our query we get this:

SELECT name, age, credit_card FROM usertable WHERE username = 'PeterO'Reilly' AND password = 'secret'

MySQL blasts along quite happily and hits username=‘PeterO’, and then it gets this “Reilly” thing which it doesn’t know what to do with and this happens:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Reilly' and password="secret"; " at line 1

Nice. We have a broken website, Pete can’t login, and he’s left with misgivings about our web programming skills.

Even worse ’ what happens if I enter my password as this?

' or 1=1 ; --

The query that gets sent to MySQL will look like this:

SELECT name, age, credit_card FROM usertable WHERE username = 'simon' AND password = " or 1=1 ; " '

What does this mean? It tells MySQL to find all rows with a username equal to “simon” and a password equal to an empty string OR “1=1”. To represent that a bit more logically:

( username = “simon” and password = "" ) || ( 1 = 1 )

Now, 1=1 is always going to be true, so this is equal to:

( false ) || ( true )

Which means that ALL the records in the table will get returned. Our login processer above is going to log me on with someone else’s credentials – in fact, those of the first record returned.

Keep in mind, however, that we don’t need to escape numbers, and we shouldn’t put quote marks around them (it’s not standard SQL)- if a variable is a number, then it’ll be fine.

The crap attempt to fix it ( magic quotes ):

How to fix this? We need to be escape these quote characters ( both single and double quotes, as well as backslashes). This is done by putting a slash in front of them, e.g. so a ’ becomes a ', and MySQL can work out that that quote mark is “protected” by the slash, and is part of the value and ignores it. So, Peter’s attempt to login becomes:

SELECT name, age, credit_card FROM usertable WHERE username = 'PeterO\'Reilly' AND password = 'secret';

and my attempt to hack my way in becomes:

  
SELECT name, age, credit_card FROM usertable WHERE username = 'simon' AND password = '\' or 1=1 ; -- ';

MySQL now thinks my password is the string

' or 1=1 ;

and I won’t be able to login.

So where do we get these slashes? Since around PHP version 3.06, PHP tries to do this for you, with a setting called “magic_quotes”. What this does is to automatically add slashes to anything coming in via HTTP get or post requests and via cookies. You can also do this manually using the addslashes() function.

But do not rely on this!

  • It could be turned off or on, or not present in your version (and it won’t be in PHP6). Therefore YOU CANNOT RELY ON IT, AND HAVE TO HANDLE THIS YOURSELF.
  • This means that if you rely on magic quotes, then your code is not portable. Unfortunately, escaping things with a slash is one of those irritating non-standard MySQL features. Most other databases which follow the SQL standards (like Postgres), escape things with another single quote ( O’Reilly => O"Reilly ).
  • It’s crappy. It doesn’t work well with extended characters, and these can be used to get around the slashes. See Chris Shiflett’s discussion of this problem
  • It’s irritating ’ it pollutes your data with stuff that the user didn’t enter. This is the major cause of the magic breeding slashed-quote.

(Aside) The Magic Breeding Slashed-Quote:

I’m sure you’ve all seen websites that have this really annoying habit of messing up their user's post's quote marks ( just like that ). I’m calling this the magic breeding slashed quote, because these things propagate like crazy. What’s happening here is that the hard working web developer is adding slashes to the data they send to their database ’ great! BUT, they’re not checking for magic quotes, so PHP is escaping the ’ once to ', and then when the website runs addslashes() again, php sees a backslash AND a single quote which need escaping ( remember that the three characters that get escaped are , ‘, and " ). This therefore becomes \'. MySQL comes along and sees an escaped backslash AND an escaped single quote.

Here’s what’s happening:

  1. user input: O’Reilly
  2. magic quotes: O'Reilly
  3. addslashes: O\'Reilly
  4. MySQL processes this to: O'Reilly

and we end up with O'Reilly stored when we really want O’Reilly.

I’ve actually seen applications which quite happily store O'Reilly, and stripslashes() before they display the data ’ this is just blindingly stupid.

Fixing it.

So, we need a way of escaping data that isn’t crappy, isn’t as prone to character set issues, and isn’t prone to magic breeding slashed quotes.

There are two ways to do this.

  • Use better slashes ( PHP4, old mysql client library using the mysql_* functions )
  • Use a better technique ’ bound parameters ( PHP5 with the new mysqli_* client library)

Using better slashes ’ mysql_real_escape_string( ) (PHP4, mysql_* )

If you’re using the old mysql client library ( i.e. the mysql_* functions ), then you have to use the hideously named mysql_real_escape_string() function. This takes into account the character set of the database connection and should handle things appropriately.

Note: mysql_real_escape_string needs an active database connection, or anything sent to it will disappear ( WTF? ), or it will generate an error.

BUT we still need to check for the evil magic_quotes setting, which and remove it. We can do this with the get_magic_quotes_gpc() function ( “gpc” refers to Get, Post, and Cookies which magic quotes operates on ).

So ’ something like this:

// remove the pesky slashes from magic quotes if it's turned on
  
function clean_string( $value, $DB ) {
    if ( get_magic_quotes_gpc() ) {
        $value = stripslashes( $value );
    }
    // escape things properly
    return mysql_real_escape_string( $value, $DB );
}

$string = "O'Reilly";
// where $db is your active database connection resource id.
$safe_string = clean_string( $string, $db );

There’s a function described in the PHP manual called quote_smart, that handles this and handles both strings and integers:

// Quote variable to make safe
function quote_smart($value) {
    if ( get_magic_quotes_gpc() ) {
        $value = stripslashes( $value );
    }
    // Quote if not a number or a numeric string
    if ( !is_numeric( $value ) ) {
        $value = "'" . mysql_real_escape_string($value) . "'";
    }
    return $value;
}

Note that you’ll need to implement this yourself, and you’ll have to rewrite your queries to not have quotes in them e.g.:


$variable = "O'Reilly";
  
$variable = quote_smart( $variable );
  
// note that we haven't surrounded $variable with quote marks in
  
// the query below since quote_smart does that for us.
  
$query = "SELECT x, y, z FROM tablename WHERE user = $variable";

However, this leaving quote marks out of the query irritates me enough, that I generally just type-cast anything which should be a number to a number:

function clean_int($i) {
    if ( is_numeric( $i ) ) {
        return ( int ) $i;
    }
    // return False if we don't get a number
    else {
        return False;
    }
}

Warning:

This is NOT foolproof. In fact, if the attacker can change the character set on the fly, then this whole system can be avoided. Ilia Alshanetsky has an excellent write up on this.

Fixing it with better technique ’ bound parameters ( PHP5, MySQLi ):

So ’ the best solution? Use bound parameters. To use these you’ll need to be using the improved mysqli library that comes with PHP5. This technique differs slightly in that you define a query “template” first with placeholders, and then “bind” the parameters to it, and the mysqli library takes care of the appropriate escaping for us:

  
$variable = "O'Reilly";
  
// prepare the query
  
$query = $mysqli->prepare( "SELECT x, y, z FROM tablename WHERE user = ?" );

// bind a parameter ' here the first parameter is a short string that specifies the type that the
  
// subsequent arguments should be:
  
// 's' means a string
  
// 'd' means a double
  
// 'i' means an integer
  
// 'b' is a blob
  
$query->bind_param( 's', $variable );

// execute query:
  
$query->execute( );

// so if we had a more complex query, which updated the user info // with "favorite_color" (a string), "age" ( an integer ) and
  
// "description", a blob:

$query = $mysqli->prepare( "UPDATE tablename SET favorite_color = ?, age = ?, description = ? WHERE user = ?" );
  
// we would have a bind looking like this:
  
$query->bind_param( 'sibs', 'red', 27, $some_blob, $variable );
  
$query->execute();

Another benefit of this method is that it’s faster to transfer data to the db server. Harrison Fisk has a good discussion of these here.

Another thing to keep in mind:

Now, properly using mysql_real_escape_string or prepared statements should keep you pretty safe, but there are a few characters you might also want to watch out for:

The Percentage Sign (%)

The percentage symbol is commonly used by MySQL to perform LIKE queries ’ this WON’T get escaped. If your application is doing LIKE comparisons, and your database is large, then it’s worth checking for this specifically to avoid a friendly user entering “%” and making your database grind to a halt ’ e.g.

  
$user_input = '%';
  
$query = "SELECT x,y,z FROM tablename WHERE user LIKE '%$user_input%';
  
// becomes LIKE %%% -> and returns all rows in tablename.

Edit: 15th August, 2006 '

James Laver has written a nice lightweight database access class for MySQLi which takes care of the binding of parameters for you.

Edit: 9th November, 2006 '

Fixing a link, thanks Peter 🙂

-Simon