way How can I prevent SQL injection in PHP?

sql injection in php login form (23)

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?

There are so many answers for PHP and MySQL, but here is code for PHP and Oracle for preventing SQL injection as well as regular use of oci8 drivers:

$conn = oci_connect($username, $password, $connection_string);
$stmt = oci_parse($conn, 'UPDATE table SET field = :xx WHERE ID = 123');
oci_bind_by_name($stmt, ':xx', $fieldval);

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.

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.

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.


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 )


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

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

Using this PHP function mysql_escape_string() you can get a good prevention in a fast way.

For example:

SELECT * FROM users WHERE name = '".mysql_escape_string($name_from_html_form)."'

mysql_escape_string — Escapes a string for use in a mysql_query

For more prevention, you can add at the end ...

wHERE 1=1   or  LIMIT 1

Finally you get:

SELECT * FROM users WHERE name = '".mysql_escape_string($name_from_html_form)."' LIMIT 1

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.

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


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


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


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

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.

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");


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

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

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.


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

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


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.

    $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);




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.

Regarding many useful answers, I hope to add some values to this thread. SQL injection is an attack that can be done through user inputs (Inputs that filled by user and then used inside queries), The SQL injection patterns are correct query syntax while we can call it: bad queries for bad reasons, we assume that there might be bad person that try to get secret information (bypassing access control) that affect the three principles of security (Confidentiality, Integrity, Availability).

Now, our point is to prevent security threats such as SQL injection attacks, the question asking (How to prevent SQL injection attack using PHP), be more realistic, data filtering or clearing input data is the case when using user-input data inside such query, using PHP or any other programming language is not the case, or as recommended by more people to use modern technology such as prepared statement or any other tools that currently supporting SQL injection prevention, consider that these tools not available anymore? How you secure your application?

My approach against SQL injection is: clearing user-input data before sending it to the database (before using it inside any query).

Data filtering for (Converting unsafe data to safe data) Consider that PDO and MySQLi not available, how can you secure your application? Do you force me to use them? What about other languages other than PHP? I prefer to provide general ideas as it can be used for wider border not just for specific language.

  1. SQL user (limiting user privilege): most common SQL operations are (SELECT, UPDATE, INSERT), then, why giving UPDATE privilege to a user that not require it? For example login, and search pages are only using SELECT, then, why using DB users in these pages with high privileges? RULE: do not create one database user for all privileges, for all SQL operations, you can create your scheme like (deluser, selectuser, updateuser) as usernames for easy usage.

see Principle of least privilege

  1. Data filtering: before building any query user input should be validated and filtered, for programmers, it's important to define some properties for each user-input variables: data type, data pattern, and data length. a field that is a number between (x and y) must be exactly validated using exact rule, for a field that is a string (text): pattern is the case, for example, username must contain only some characters lets say [a-zA-Z0-9_-.] the length varies between (x and n) where x and n (integers, x <=n ). Rule: creating exact filters and validation rules are best practice for me.

  2. Use other tools: Here, I will also agree with you that prepared statement (parametrized query) and Stored procedures, the disadvantages here is these ways requires advanced skills which do not exist for most users, the basic idea here is to distinguish between SQL query and the data that being used inside, both approaches can be used even with unsafe data, because the user-input data here not add anything to the original query such as (any or x=x). For more information, please read OWASP SQL Injection Prevention Cheat Sheet.

Now, if you are an advanced user, start using this defense as you like, but, for beginners, if they can't quickly implement stored procedure and prepared the statement, it's better to filter input data as much they can.

Finally, let's consider that user sends this text below instead of entering his username:

[1] UNION SELECT IF(SUBSTRING(Password,1,1)='2',BENCHMARK(100000,SHA1(1)),0) User,Password FROM mysql.user WHERE User = 'root'

This input can be checked early without any prepared statement and stored procedures, but to be on safe side, using them starts after user-data filtering and validation.

The last point is detecting unexpected behavior which requires more effort and complexity; it's not recommended for normal web applications. Unexpected behavior in above user input is SELECT, UNION, IF, SUBSTRING, BENCHMARK, SHA, root once these words detected, you can avoid the input.


A user commented that this post is useless, OK! Here is what OWASP.ORG provided:

Primary defenses:

Option #1: Use of Prepared Statements (Parameterized Queries)
Option #2: Use of Stored Procedures
Option #3: Escaping all User Supplied Input

Additional defenses:

Also Enforce: Least Privilege
Also Perform: White List Input Validation

As you may know, claiming on an article should be supported by valid argument, at least one reference! Otherwise, it's considered as an attack and bad claim!


From the PHP manual, PHP: Prepared Statements - Manual:

Escaping and SQL injection

Bound variables will be escaped automatically by the server. The server inserts their escaped values at the appropriate places into the statement template before execution. A hint must be provided to the server for the type of bound variable, to create an appropriate conversion. See the mysqli_stmt_bind_param() function for more information.

The automatic escaping of values within the server is sometimes considered a security feature to prevent SQL injection. The same degree of security can be achieved with non-prepared statements if input values are escaped correctly.


I created test cases for knowing how PDO and MySQLi send the query to MySQL server when using prepared statement:


$user = "''1''"; //Malicious keyword
$sql = 'SELECT * FROM awa_user WHERE userame =:username';
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute(array(':username' => $user));

Query Log:

    189 Query SELECT * FROM awa_user WHERE userame ='\'\'1\'\''
    189 Quit


$stmt = $mysqli->prepare("SELECT * FROM awa_user WHERE username =?")) {
$stmt->bind_param("s", $user);
$user = "''1''";

Query Log:

    188 Prepare   SELECT * FROM awa_user WHERE username =?
    188 Execute   SELECT * FROM awa_user WHERE username ='\'\'1\'\''
    188 Quit

It's clear that a prepared statement is also escaping the data, nothing else.

As also mentioned in above statement The automatic escaping of values within the server is sometimes considered a security feature to prevent SQL injection. The same degree of security can be achieved with non-prepared statements, if input values are escaped correctly, therefore, this proves that data validation such as intval() is a good idea for integer values before sending any query, in addition, preventing malicious user data before sending the query is correct and valid approach.

Please see this question for more detail: PDO sends raw query to MySQL while Mysqli sends prepared query, both produce the same result


  1. SQL Injection Cheat Sheet
  2. SQL Injection
  3. Information security
  4. Security Principles
  5. Data validation

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

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.

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

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.

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


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'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;
    $query = preg_replace_callback(
        function($match) use ($values)
            if (isset($values[$match[1]]))
                return $values[$match[1]];
                return $match[0];
    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.

A few guidelines for escaping special characters in SQL statements.

Don't use MySQL, this extension is deprecated, use MySQLi or PDO.


For manually escaping special characters in a string you can use the mysqli_real_escape_string function. The function will not work properly unless the correct character set is set with mysqli_set_charset.


$mysqli = new mysqli( 'host', 'user', 'password', 'database' );
$mysqli->set_charset( 'charset');

$string = $mysqli->real_escape_string( $string );
$mysqli->query( "INSERT INTO table (column) VALUES ('$string')" );

For automatic escaping of values with prepared statements, use mysqli_prepare, and mysqli_stmt_bind_param where types for the corresponding bind variables must be provided for an appropriate conversion:


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

$stmt->bind_param( "is", $integer, $string );


No matter if you use prepared statements or mysqli_real_escape_string, you always have to know the type of input data you're working with.

So if you use a prepared statement, you must specify the types of the variables for mysqli_stmt_bind_param function.

And the use of mysqli_real_escape_string is for, as the name says, escaping special characters in a string, so it will not make integers safe. The purpose of this function is to prevent breaking the strings in SQL statements, and the damage to the database that it could cause. mysqli_real_escape_string is a useful function when used properly, especially when combined with sprintf.


$string = "x' OR name LIKE '%John%";
$integer = '5 OR id != 0';

$query = sprintf( "SELECT id, email, pass, name FROM members WHERE email ='%s' AND id = %d", $mysqli->real_escape_string( $string ), $integer );

echo $query;
// SELECT id, email, pass, name FROM members WHERE email ='x\' OR name LIKE \'%John%' AND id = 5

$integer = '99999999999999999999';
$query = sprintf( "SELECT id, email, pass, name FROM members WHERE email ='%s' AND id = %d", $mysqli->real_escape_string( $string ), $integer );

echo $query;
// SELECT id, email, pass, name FROM members WHERE email ='x\' OR name LIKE \'%John%' AND id = 2147483647

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

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

$user->first_name = 'Jamie';

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

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.