way - How can I prevent SQL injection in PHP?




sql injection in php login form (19)

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?


IMPORTANT

The best way to prevent SQL Injection is to use Prepared Statements instead of escaping, as the accepted answer demonstrates.

There are libraries such as Aura.Sql and EasyDB that allow developers to use prepared statements easier. To learn more about why prepared statements are better at stopping SQL injection, refer to this mysql_real_escape_string() bypass and recently fixed Unicode SQL Injection vulnerabilities in WordPress.

Injection prevention - mysql_real_escape_string()

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.

mysql_real_escape_string takes 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 \'.

NOTE: you must be connected to the database to use this function!

// Connect to MySQL

$name_bad = "' OR 1'"; 

$name_bad = mysql_real_escape_string($name_bad);

$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";
echo "Escaped Bad Injection: <br />" . $query_bad . "<br />";


$name_evil = "'; DELETE FROM customers WHERE 1 or username = '"; 

$name_evil = mysql_real_escape_string($name_evil);

$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";
echo "Escaped Evil Injection: <br />" . $query_evil;

You can find more details in MySQL - SQL Injection Prevention.


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.).


** 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.


Use prepared statements and parameterized queries. These are SQL statements that are sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL.

You basically have two options to achieve this:

  1. Using PDO (for any supported database driver):

    $stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
    
    $stmt->execute(array('name' => $name));
    
    foreach ($stmt as $row) {
        // do something with $row
    }
    
  2. Using MySQLi (for MySQL):

    $stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
    $stmt->bind_param('s', $name); // 's' specifies the variable type => 'string'
    
    $stmt->execute();
    
    $result = $stmt->get_result();
    while ($row = $result->fetch_assoc()) {
        // do something with $row
    }
    

If you're connecting to a database other than MySQL, there is a driver-specific second option that you can refer to (e.g. pg_prepare() and pg_execute() for PostgreSQL). PDO is the universal option.

Correctly setting up the connection

Note that when using PDO to access a MySQL database real prepared statements are not used by default. To fix this you have to disable the emulation of prepared statements. An example of creating a connection using PDO is:

$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

In the above example the error mode isn't strictly necessary, but it is advised to add it. This way the script will not stop with a Fatal Error when something goes wrong. And it gives the developer the chance to catch any error(s) which are thrown as PDOExceptions.

What is mandatory, however, is the first setAttribute() line, which tells PDO to disable emulated prepared statements and use real prepared statements. This makes sure the statement and the values aren't parsed by PHP before sending it to the MySQL server (giving a possible attacker no chance to inject malicious SQL).

Although you can set the charset in the options of the constructor, it's important to note that 'older' versions of PHP (< 5.3.6) silently ignored the charset parameter in the DSN.

Explanation

What happens is that the SQL statement you pass to prepare is parsed and compiled by the database server. By specifying parameters (either a ? or a named parameter like :name in the example above) you tell the database engine where you want to filter on. Then when you call execute, the prepared statement is combined with the parameter values you specify.

The important thing here is that the parameter values are combined with the compiled statement, not an SQL string. SQL injection works by tricking the script into including malicious strings when it creates SQL to send to the database. So by sending the actual SQL separately from the parameters, you limit the risk of ending up with something you didn't intend. Any parameters you send when using a prepared statement will just be treated as strings (although the database engine may do some optimization so parameters may end up as numbers too, of course). In the example above, if the $name variable contains 'Sarah'; DELETE FROM employees the result would simply be a search for the string "'Sarah'; DELETE FROM employees", and you will not end up with an empty table.

Another benefit of using prepared statements is that if you execute the same statement many times in the same session it will only be parsed and compiled once, giving you some speed gains.

Oh, and since you asked about how to do it for an insert, here's an example (using PDO):

$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)');

$preparedStatement->execute(array('column' => $unsafeValue));

Can prepared statements be used for dynamic queries?

While you can still use prepared statements for the query parameters, the structure of the dynamic query itself cannot be parametrized and certain query features cannot be parametrized.

For these specific scenarios, the best thing to do is use a whitelist filter that restricts the possible values.

// Value whitelist
// $dir can only be 'DESC' otherwise it will be 'ASC'
if (empty($dir) || $dir !== 'DESC') {
   $dir = 'ASC';
}

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.


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.


Every answer here covers only part of the problem. In fact, there are four different query parts which we can add to it dynamically:

  • a string
  • a number
  • an identifier
  • a syntax keyword.

And prepared statements covers only two of them.

But sometimes we have to make our query even more dynamic, adding operators or identifiers as well. So, we will need different protection techniques.

In general, such a protection approach is based on whitelisting.

In this case, every dynamic parameter should be hardcoded in your script and chosen from that set. For example, to do dynamic ordering:

$orders  = array("name", "price", "qty"); // Field names
$key     = array_search($_GET['sort'], $orders)); // See if we have such a name
$orderby = $orders[$key]; // If not, first one will be set automatically. smart enuf :)
$query   = "SELECT * FROM `table` ORDER BY $orderby"; // Value is safe

However, there is another way to secure identifiers - escaping. As long as you have an identifier quoted, you can escape backticks inside by doubling them.

As a further step, we can borrow a truly brilliant idea of using some placeholder (a proxy to represent the actual value in the query) from the prepared statements and invent a placeholder of another type - an identifier placeholder.

So, to make the long story short: it's a placeholder, not prepared statement can be considered as a silver bullet.

So, a general recommendation may be phrased as As long as you are adding dynamic parts to the query using placeholders (and these placeholders properly processed of course), you can be sure that your query is safe.

Still, there is an issue with SQL syntax keywords (such as AND, DESC and such), but white-listing seems the only approach in this case.

Update

Although there is a general agreement on the best practices regarding SQL injection protection, there are still many bad practices as well. And some of them too deeply rooted in the minds of PHP users. For instance, on this very page there are (although invisible to most visitors) more than 80 deleted answers - all removed by the community due to bad quality or promoting bad and outdated practices. Worse yet, some of the bad answers aren't deleted, but rather prospering.

For example, there(1) are(2) still(3) many(4) answers(5), including the second most upvoted answer suggesting you manual string escaping - an outdated approach that is proven to be insecure.

Or there is a slightly better answer that suggests just another method of string formatting and even boasts it as the ultimate panacea. While of course, it is not. This method is no better than regular string formatting, yet it keeps all its drawbacks: it is applicable to strings only and, as any other manual formatting, it's essentially optional, non-obligatory measure, prone to human error of any sort.

I think that all this because of one very old superstition, supported by such authorities like OWASP or the PHP manual, which proclaims equality between whatever "escaping" and protection from SQL injections.

Regardless of what PHP manual said for ages, *_escape_string by no means makes data safe and never has been intended to. Besides being useless for any SQL part other than string, manual escaping is wrong, because it is manual as opposite to automated.

And OWASP makes it even worse, stressing on escaping user input which is an utter nonsense: there should be no such words in the context of injection protection. Every variable is potentially dangerous - no matter the source! Or, in other words - every variable has to be properly formatted to be put into a query - no matter the source again. It's the destination that matters. The moment a developer starts to separate the sheep from the goats (thinking whether some particular variable is "safe" or not) he/she takes his/her first step towards disaster. Not to mention that even the wording suggests bulk escaping at the entry point, resembling the very magic quotes feature - already despised, deprecated and removed.

So, unlike whatever "escaping", prepared statements is the measure that indeed protects from SQL injection (when applicable).

If you're still not convinced, here is a step-by-step explanation I wrote, The Hitchhiker's Guide to SQL Injection prevention, where I explained all these matters in detail and even compiled a section entirely dedicated to bad practices and their disclosure.


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 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.


I use three different ways to prevent my web application from being vulnerable to SQL injection.

  1. Use of mysql_real_escape_string(), which is a pre-defined function in PHP, and this code add backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a. Pass the input values as parameters to minimize the chance of SQL injection.
  2. The most advanced way is to use PDOs.

I hope this will help you.

Consider the following query:

$iId = mysql_real_escape_string("1 OR 1=1"); $sSql = "SELECT * FROM table WHERE id = $iId";

mysql_real_escape_string() will not protect here. If you use single quotes (' ') around your variables inside your query is what protects you against this. Here is an solution below for this:

$iId = (int) mysql_real_escape_string("1 OR 1=1"); $sSql = "SELECT * FROM table WHERE id = $iId";

This question has some good answers about this.

I suggest, using PDO is the best option.

Edit:

mysql_real_escape_string() is deprecated as of PHP 5.5.0. Use either mysqli or PDO.

An alternative to mysql_real_escape_string() is

string mysqli_real_escape_string ( mysqli $link , string $escapestr )

Example:

$iId = $mysqli->real_escape_string("1 OR 1=1");
$mysqli->query("SELECT * FROM table WHERE id = $iId");

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.


If possible, cast the types of your parameters. But it's only working on simple types like int, bool, and float.

$unsafe_variable = $_POST['user_id'];

$safe_variable = (int)$unsafe_variable ;

mysqli_query($conn, "INSERT INTO table (column) VALUES ('" . $safe_variable . "')");

In my opinion, the best way to generally prevent SQL injection in your PHP application (or any web application, for that matter) is to think about your application's architecture. If the only way to protect against SQL injection is to remember to use a special method or function that does The Right Thing every time you talk to the database, you are doing it wrong. That way, it's just a matter of time until you forget to correctly format your query at some point in your code.

Adopting the MVC pattern and a framework like CakePHP or CodeIgniter is probably the right way to go: Common tasks like creating secure database queries have been solved and centrally implemented in such frameworks. They help you to organize your web application in a sensible way and make you think more about loading and saving objects than about securely constructing single SQL queries.


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)


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 [email protected]'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.


There are many ways of preventing SQL injections and other SQL hacks. You can easily find it on the Internet (Google Search). Of course PDO is one of the good solutions. But I would like to suggest you some good links prevention from SQL Injection.

What is SQL injection and how to prevent

PHP manual for SQL injection

Microsoft explanation of SQL injection and prevention in PHP

and some other like Preventing SQL injection with MySQL and PHP

Now, why you do you need to prevent your query from SQL injection?

I would like to let you know: Why do we try for preventing SQL injection with a short example below:

Query for login authentication match:

$query="select * from users where email='".$_POST['email']."' and password='".$_POST['password']."' ";

Now, if someone (a hacker) puts

$_POST['email']= [email protected]' OR '1=1

and password anything....

The query will be parsed into the system only up to:

$query="select * from users where email='[email protected]' OR '1=1';

The other part will be discarded. So, what will happen? A non-authorized user (hacker) will be able to log in as admin without having his password. Now, he can do anything that admin/email person can do. See, it's very dangerous if SQL injection is not prevented.


Use PDO and prepared queries.

($conn is a PDO object)

$stmt = $conn->prepare("INSERT INTO tbl VALUES(:id, :name)");
$stmt->bindValue(':id', $id);
$stmt->bindValue(':name', $name);
$stmt->execute();

Using PDO and MYSQLi is a good practice to prevent SQL injections, but if you really want to work with MySQL functions and queries, it would be better to use

mysql_real_escape_string

$unsafe_variable = mysql_real_escape_string($_POST['user_input']);

There are more abilities to prevent this: like identify - if the input is a string, number, char or array, there are so many inbuilt functions to detect this. Also, it would be better to use these functions to check input data.

is_string

$unsafe_variable = (is_string($_POST['user_input']) ? $_POST['user_input'] : '');

is_numeric

$unsafe_variable = (is_numeric($_POST['user_input']) ? $_POST['user_input'] : '');

And it is so much better to use those functions to check input data with mysql_real_escape_string.


Whatever you do end up using, make sure that you check your input hasn't already been mangled by magic_quotes or some other well-meaning rubbish, and if necessary, run it through stripslashes or whatever to sanitize it.





sql-injection