How can I prevent SQL injection in PHP?


Answers

Warning: This answer's sample code (like the question's sample code) uses PHP's mysql extension, which was deprecated in PHP 5.5.0 and removed entirely in PHP 7.0.0.

If you're using a recent version of PHP, the mysql_real_escape_string option outlined below will no longer be available (though mysqli::escape_string is a modern equivalent). These days the mysql_real_escape_string option would only make sense for legacy code on an old version of PHP.


You've got two options - escaping the special characters in your unsafe_variable, or using a parameterized query. Both would protect you from SQL injection. The parameterized query is considered the better practice but will require changing to a newer mysql extension in PHP before you can use it.

We'll cover the lower impact string escaping one first.

//Connect

$unsafe_variable = $_POST["user-input"];
$safe_variable = mysql_real_escape_string($unsafe_variable);

mysql_query("INSERT INTO table (column) VALUES ('" . $safe_variable . "')");

//Disconnect

See also, the details of the mysql_real_escape_string function.

To use the parameterized query, you need to use MySQLi rather than the MySQL functions. To rewrite your example, we would need something like the following.

<?php
    $mysqli = new mysqli("server", "username", "password", "database_name");

    // TODO - Check that connection was successful.

    $unsafe_variable = $_POST["user-input"];

    $stmt = $mysqli->prepare("INSERT INTO table (column) VALUES (?)");

    // TODO check that $stmt creation succeeded

    // "s" means the database expects a string
    $stmt->bind_param("s", $unsafe_variable);

    $stmt->execute();

    $stmt->close();

    $mysqli->close();
?>

The key function you'll want to read up on there would be mysqli::prepare.

Also, as others have suggested, you may find it useful/easier to step up a layer of abstraction with something like PDO.

Please note that the case you asked about is a fairly simple one and that more complex cases may require more complex approaches. In particular:

  • If you want to alter the structure of the SQL based on user input, parameterized queries are not going to help, and the escaping required is not covered by mysql_real_escape_string. In this kind of case, you would be better off passing the user's input through a whitelist to ensure only 'safe' values are allowed through.
  • If you use integers from user input in a condition and take the mysql_real_escape_string approach, you will suffer from the problem described by Polynomial in the comments below. This case is trickier because integers would not be surrounded by quotes, so you could deal with by validating that the user input contains only digits.
  • There are likely other cases I'm not aware of. You might find this is a useful resource on some of the more subtle problems you can encounter.
Question

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example:

$unsafe_variable = $_POST['user_input']; 

mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')");

That's because the user can input something like value'); DROP TABLE table;--, and the query becomes:

INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')

What can be done to prevent this from happening?




I'd recommend using PDO (PHP Data Objects) to run parameterized SQL queries.

Not only does this protect against SQL injection, it also speeds up queries.

And by using PDO rather than mysql_, mysqli_, and pgsql_ functions, you make your app a little more abstracted from the database, in the rare occurrence that you have to switch database providers.




For those unsure of how to use PDO (coming from the mysql_ functions), I made a very, very simple PDO wrapper that is a single file. It exists to show how easy it is to do all the common things applications need to be done. Works with PostgreSQL, MySQL, and SQLite.

Basically, read it while you read the manual to see how to put the PDO functions to use in real life to make it simple to store and retrieve values in the format you want.

I want a single column

$count = DB::column('SELECT COUNT(*) FROM `user`);

I want an array(key => value) results (i.e. for making a selectbox)

$pairs = DB::pairs('SELECT `id`, `username` FROM `user`);

I want a single row result

$user = DB::row('SELECT * FROM `user` WHERE `id` = ?', array($user_id));

I want an array of results

$banned_users = DB::fetch('SELECT * FROM `user` WHERE `banned` = ?', array(TRUE));



I favor stored procedures (MySQL has had stored procedures support since 5.0) from a security point of view - the advantages are -

  1. Most databases (including MySQL) enable user access to be restricted to executing stored procedures. The fine-grained security access control is useful to prevent escalation of privileges attacks. This prevents compromised applications from being able to run SQL directly against the database.
  2. They abstract the raw SQL query from the application so less information of the database structure is available to the application. This makes it harder for people to understand the underlying structure of the database and design suitable attacks.
  3. They accept only parameters, so the advantages of parameterized queries are there. Of course - IMO you still need to sanitize your input - especially if you are using dynamic SQL inside the stored procedure.

The disadvantages are -

  1. They (stored procedures) are tough to maintain and tend to multiply very quickly. This makes managing them an issue.
  2. They are not very suitable for dynamic queries - if they are built to accept dynamic code as parameters then a lot of the advantages are negated.



A good idea is to use an 'object-relational mapper' like Idiorm:

$user = ORM::for_table('user')
->where_equal('username', 'j4mie')
->find_one();

$user->first_name = 'Jamie';
$user->save();

$tweets = ORM::for_table('tweet')
    ->select('tweet.*')
    ->join('user', array(
        'user.id', '=', 'tweet.user_id'
    ))
    ->where_equal('user.username', 'j4mie')
    ->find_many();

foreach ($tweets as $tweet) {
    echo $tweet->text;
}

It not only saves you from SQL injections but from syntax errors too! Also Supports collections of models with method chaining to filter or apply actions to multiple results at once and multiple connections.




I've written this little function several years ago:

function sqlvprintf($query, $args)
{
    global $DB_LINK;
    $ctr = 0;
    ensureConnection(); // Connect to database if not connected already.
    $values = array();
    foreach ($args as $value)
    {
        if (is_string($value))
        {
            $value = "'" . mysqli_real_escape_string($DB_LINK, $value) . "'";
        }
        else if (is_null($value))
        {
            $value = 'NULL';
        }
        else if (!is_int($value) && !is_float($value))
        {
            die('Only numeric, string, array and NULL arguments allowed in a query. Argument '.($ctr+1).' is not a basic type, it\'s type is '. gettype($value). '.');
        }
        $values[] = $value;
        $ctr++;
    }
    $query = preg_replace_callback(
        '/{(\\d+)}/', 
        function($match) use ($values)
        {
            if (isset($values[$match[1]]))
            {
                return $values[$match[1]];
            }
            else
            {
                return $match[0];
            }
        },
        $query
    );
    return $query;
}

function runEscapedQuery($preparedQuery /*, ...*/)
{
    $params = array_slice(func_get_args(), 1);
    $results = runQuery(sqlvprintf($preparedQuery, $params)); // Run query and fetch results.   
    return $results;
}

This allows running statements in an one-liner C#-ish String.Format like:

runEscapedQuery("INSERT INTO Whatever (id, foo, bar) VALUES ({0}, {1}, {2})", $numericVar, $stringVar1, $stringVar2);

It escapes considering the variable type. If you try to parameterize table, column names, it would fail as it puts every string in quotes which is an invalid syntax.

SECURITY UPDATE: The previous str_replace version allowed injections by adding {#} tokens into user data. This preg_replace_callback version doesn't cause problems if the replacement contains these tokens.




** Warning: the approach described in this answer only applies to very specific scenarios and isn't secure since SQL injection attacks do not only rely on being able to inject X=Y.**

If the attackers are trying to hack into the form via PHP's $_GET variable or with the URL's query string, you would be able to catch them if they're not secure.

RewriteCond %{QUERY_STRING} ([0-9]+)=([0-9]+)
RewriteRule ^(.*) ^/track.php

Because 1=1, 2=2, 1=2, 2=1, 1+1=2, etc... are the common questions to an SQL database of an attacker. Maybe also it's used by many hacking applications.

But you must be careful, that you must not rewrite a safe query from your site. The code above is giving you a tip, to rewrite or redirect (it depends on you) that hacking-specific dynamic query string into a page that will store the attacker's IP address, or EVEN THEIR COOKIES, history, browser, or any other sensitive information, so you can deal with them later by banning their account or contacting authorities.




Security Warning: This answer is not in line with security best practices. Escaping is inadequate to prevent SQL injection, use prepared statements instead. Use the strategy outlined below at your own risk. (Also, mysql_real_escape_string() was removed in PHP 7.)

You could do something basic like this:

$safe_variable = mysql_real_escape_string($_POST["user-input"]);
mysql_query("INSERT INTO table (column) VALUES ('" . $safe_variable . "')");

This won't solve every problem, but it's a very good stepping stone. I left out obvious items such as checking the variable's existence, format (numbers, letters, etc.).




I think if someone wants to use PHP and MySQL or some other dataBase server:

  1. Think about learning PDO (PHP Data Objects) – it is a database access layer providing a uniform method of access to multiple databases.
  2. Think about learning MySQLi
  3. Use native PHP functions like: strip_tags, mysql_real_escape_string or if variable numeric, just (int)$foo. Read more about type of variables in PHP here. If you're using libraries such as PDO or MySQLi, always use PDO::quote() and mysqli_real_escape_string().

Libraries examples:

---- PDO

----- No placeholders - ripe for SQL injection! It's bad

$request = $pdoConnection->("INSERT INTO parents (name, addr, city) values ($name, $addr, $city)");

----- Unnamed placeholders

$request = $pdoConnection->("INSERT INTO parents (name, addr, city) values (?, ?, ?);

----- Named placeholders

$request = $pdoConnection->("INSERT INTO parents (name, addr, city) value (:name, :addr, :city)");

--- MySQLi

$request = $mysqliConnection->prepare('
       SELECT * FROM trainers
       WHERE name = ?
       AND email = ?
       AND last_login > ?');

    $query->bind_param('first_param', 'second_param', $mail, time() - 3600);
    $query->execute();

P.S:

PDO wins this battle with ease. With support for twelve different database drivers and named parameters, we can ignore the small performance loss, and get used to its API. From a security standpoint, both of them are safe as long as the developer uses them the way they are supposed to be used

But while both PDO and MySQLi are quite fast, MySQLi performs insignificantly faster in benchmarks – ~2.5% for non-prepared statements, and ~6.5% for prepared ones.

And please test every query to your database - it's a better way to prevent injection.







The simple alternative to this problem could be solved by granting appropriate permissions in the database itself. For example: if you are using a mysql database then enter into the database through terminal or the UI provided and just follow this command:

 GRANT SELECT, INSERT, DELETE ON database TO username@'localhost' IDENTIFIED BY 'password';

This will restrict the user to only get confined with the specified query's only. Remove the delete permission and so the data would never get deleted from the query fired from the php page. The second thing to do is to flush the privileges so that the mysql refreshes the permissions and updates.

FLUSH PRIVILEGES; 

more information about flush.

To see the current privileges for the user fire the following query.

select * from mysql.user where User='username';

Learn more about GRANT.




As you can see, people suggest you use prepared statements at the most. It's not wrong, but when your query is executed just once per process, there would be a slight performance penalty.

I was facing this issue, but I think I solved it in very sophisticated way - the way hackers use to avoid using quotes. I used this in conjunction with emulated prepared statements. I use it to prevent all kinds of possible SQL injection attacks.

My approach:

  • If you expect input to be integer make sure it's really integer. In a variable-type language like PHP it is this very important. You can use for example this very simple but powerful solution: sprintf("SELECT 1,2,3 FROM table WHERE 4 = %u", $input);

  • If you expect anything else from integer hex it. If you hex it, you will perfectly escape all input. In C/C++ there's a function called mysql_hex_string(), in PHP you can use bin2hex().

    Don't worry about that the escaped string will have a 2x size of its original length because even if you use mysql_real_escape_string, PHP has to allocate same capacity ((2*input_length)+1), which is the same.

  • This hex method is often used when you transfer binary data, but I see no reason why not use it on all data to prevent SQL injection attacks. Note that you have to prepend data with 0x or use the MySQL function UNHEX instead.

So, for example, the query:

SELECT password FROM users WHERE name = 'root'

Will become:

SELECT password FROM users WHERE name = 0x726f6f74

or

SELECT password FROM users WHERE name = UNHEX('726f6f74')

Hex is the perfect escape. No way to inject.

Difference between UNHEX function and 0x prefix

There was some discussion in comments, so I finally want to make it clear. These two approaches are very similar, but they are a little different in some ways:

The ** 0x** prefix can only be used for data columns such as char, varchar, text, block, binary, etc.
Also, its use is a little complicated if you are about to insert an empty string. You'll have to entirely replace it with '', or you'll get an error.

UNHEX() works on any column; you do not have to worry about the empty string.


Hex methods are often used as attacks

Note that this hex method is often used as an SQL injection attack where integers are just like strings and escaped just with mysql_real_escape_string. Then you can avoid the use of quotes.

For example, if you just do something like this:

"SELECT title FROM article WHERE id = " . mysql_real_escape_string($_GET["id"])

an attack can inject you very easily. Consider the following injected code returned from your script:

SELECT ... WHERE id = -1 union all select table_name from information_schema.tables

and now just extract table structure:

SELECT ... WHERE id = -1 union all select column_name from information_schema.column where table_name = 0x61727469636c65

And then just select whatever data ones want. Isn't it cool?

But if the coder of an injectable site would hex it, no injection would be possible because the query would look like this: SELECT ... WHERE id = UNHEX('2d312075...3635')




Parameterized query AND input validation is the way to go. There are many scenarios under which SQL injection may occur, even though mysql_real_escape_string() has been used.

Those examples are vulnerable to SQL injection:

$offset = isset($_GET['o']) ? $_GET['o'] : 0;
$offset = mysql_real_escape_string($offset);
RunQuery("SELECT userid, username FROM sql_injection_test LIMIT $offset, 10");

or

$order = isset($_GET['o']) ? $_GET['o'] : 'userid';
$order = mysql_real_escape_string($order);
RunQuery("SELECT userid, username FROM sql_injection_test ORDER BY `$order`");

In both cases, you can't use ' to protect the encapsulation.

Source: The Unexpected SQL Injection (When Escaping Is Not Enough)




A simple way would be to use a PHP framework like CodeIgniter or Laravel which have inbuilt features like filtering and active-record so that you don't have to worry about these nuances.