Protect your PHP script from SQL injections in 5 min

Prevent your site from SQL InjectionWith SQL injection, people with bad intentions can delete or change content in your MySQL database.

The good news is that there is a simple solution to prevent SQL injections, so there is no excuses not implementing this technique to your PHP scripts asap.

In this post you’ll learn about SQL injections, and how they can be prevented in your PHP scripts.

What is SQL injection

When people is trying to run unintended SQL against your database it’s called SQL injection. There can be different purposes with SQL injection, some people want to delete or modify the content of your database, while other might want to see if they can get sensible information like passwords.

Let’s say you have a login page at your site. If the users can’t remember their password, you also have a password retrieval page (send_password.php) where the user can enter his email, and have the password sent by email. After filling in a email, the user is taken to the following page:

send_password.php?action=send&email=test@test.com

In this example there is a SQL statement like this at the send_password.php page:

$query = “SELECT * FROM users WHERE email = ‘$email'”;

The intended behavior of this SQL is to select the user with the email “test@test.com”. However people with bad intentions can radically change this behavior by sending some additional information to the page…

Changing the URL to:

send_password.php?action=send&email=‘; DELETE FROM users WHERE 1 or email = ‘

Suddenly gives the SQL a totally different meaning:

$query = “SELECT * FROM users WHERE email = ‘$email’ ; DELETE FROM users WHERE 1 or email = ‘”;

So now people with bad intentions can actually delete the content of the table. By adding other things to SQL, it’s possible to get access to restricted pages etc. etc, so SQL injection should be taken very seriously…

How to prevent SQL injections

As mentioned in the start of this article, there is a easy solution to this known problem. In PHP the function:

mysql_real_escape_string()

Can more or less completely eliminate the problem with SQL injection.

The basic principle the function is, that any unescaped special characters is automatically escaped, which means that backslashes is automatically added in following way to special characters: \x00, \n, \r, \, ‘, ” and \x1a.

The result is, that it’s no longer possible to inject extra SQL into the MySQL queries.

Example code

There are multiple ways of incorporating the mysql_real_escape_string() to your MySQL quiries. In this example we’ll generate a global PHP function, that you can included in your common script library, and include on all pages:

<?php
function safesql($value){
return mysql_real_escape_string($value);
}
?>

To ensure that no SQL injection is possible you just need to add the following code each place where a POST value is used as input for at MySQL query, before the values are inserted into a SQL query:


<?php
$name = safesql($_POST["name"]);
$password = safesql($_POST["password"]);
// and here comes your SQL query
$query = "SELECT * FROM users WHERE name = '$name' and password='$password'";
?>

With this little change your SQL queries are now safer, so there really isn’t any excuse, not adding
mysql_real_escape_string to your scripts immediately after you’ve read this post :-)

Share and Enjoy

  • Facebook
  • Twitter
  • Delicious
  • LinkedIn
  • StumbleUpon
  • Add to favorites
  • Email
  • RSS
fold-left fold-right
About the author
Jørgen Nicolaisen has been passionately interested in everything online since 1995. His experience is based on working with small hobby projects as well as high volume websites. Jørgen is currently focused on the PHP based programming framework - Codeigniter, and WordPress naturally

2 Replies to Protect your PHP script from SQL injections in 5 min

  1. Timon says:

    There is a better way..

    I’ve put this in my websites:

    foreach ($_POST as $key => $value){
    if (!is_array($value)){
    $_POST[$key] = mysql_real_escape_string($value);
    }
    }

    Repeat for GET and even SESSION if you’re really paranoid!

  2. Helen Neely says:

    Thanks for this post. But I just tried it and got some error message – it needs database connection first before the mysql_real_escape_string() method could be executed.

    Is there a way of solving this problem? I want to define the safesql() in a separate class.

Comments are now closed for this article.