How to prevent SQL injection in 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 escaping characters in your variable will require fewer changes.

We'll do the simpler 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, parameterised 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 http://webappsec.org/projects/articles/091007.txt a useful resource on some of the more subtle problems you can encounter.

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 occurence that you have to switch database providers.

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 2 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 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 whitelisting seems the only approach in this case.

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

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

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.

What mysql_real_escape_string does is take 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 detail about SQL Injection Prevention.

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

Parameterized query AND input validation is the way to go. There is 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 case you can't use ' to protect the encapsulation.

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

As you can see, people suggest you to use prepared statements at the most. Its not wrong, but when your query is executed just once per process, there would be a slightly performance penalty. I was facing this issue but I think i solved it very very sophisticated way - the way hackers use to avoid using quotes. I used this in conjuction with emulated prepared statements. I use it to prevent all possible sql injection attacks.

My approach:

• if you expect input to be integer make sure its really integer. In variable-type language like is php 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 use bin2hex(). Dont worry about that the escaped string will have 2x size of its original length because even if you use mysql_real_escape_string or bin2hex, php have to allocate same capacity ((2*input_length)+1). This hex method is often used when you transfer binary data but I see no reason why not use it to all data to prevent sql injection attacks. Note that you have to prepend data with 0x or use mysql function UNHEX.

So for example query:

SELECT password FROM users WHERE name = 'root'

Will become:

SELECT password FROM users WHERE name = 0x726f6f74

Hex is the perfect escape. No way to inject.

Note that this hex method is often used as a sql injection attack where integers are just like strings and escaped just with mysql_real_escape_string, then you can avoid use of quotes.

For example if you just do something like this:

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

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

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

But if would the coder of injectable site hex it, no injection would be possible:

SELECT ... WHERE id = 0x2d312075...3635

The right way to prevent SQL injection is by using parameterized queries. This means defining the SQL code that is to be executed with placeholders for parameter values, programmatically adding the parameter values, then executing the query. Doing this allows the server to create an execution plan for the query, which prevents any "injected" SQL from being executed. An example will help in explaining this. Let’s use the same script, but I’ll define the SQL query with parameter placeholders:

$sql = "SELECT * FROM Tbl WHERE Username = ? and Password = ?";

Now, I’ll define an array that holds the parameter values:

$params = array($_POST['Username'], $_POST['Password']);

When I execute the query, I pass the $params array as an argument:

$stmt = sqlsrv_query($conn, $sql, $params);

I favor stored procedures (mySQL has sp 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 db structure is available to the application. This makes it harder or 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.

Type cast if possible 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 ;

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

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 solution. 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 in the system only upto:

$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 login as admin without having his password. Now, he can do anything what admin/email person can do. See, it's very dangerous if SQL injection is not prevented.

In my opinion, the best way to generally prevent SQL injection in your PHP app (or any web app, 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 app in a sensible way and make you think more about loading and saving objects than about securely constructing single SQL queries.

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

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 you want to take advantage of cache engines like redis or memcache maybe DALMP could be a choice, it uses pure mysqli, check this: http://www.dalmp.com/PExecute

Also you can 'prepare' your arguments before preparing your query so that you can build dynamic queries and at the end have a full prepared statements query. http://www.dalmp.com/Prepare

I think if someone want use PHP and MySQL:

1. Think about lerning PDO
2. Think about mysqli
3. Use native php functions like: strip_tags, mysql_real_escape_string or if variable numeric just (int) $Foo

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. Use of MySQLi.

3. The most advanced way is to use PDOs.

I hope this will help you.

Regarding to many useful answers, I hope to add some values to this thread. SQL injection is type of 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 (by passing 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 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 db user for all privileges, for all SQL operations, you can create your scheme like (deluser, selectuser, updateuser) as usernames for easy usage.

2. Data filtering: before building any query user input should be validated and filtered, for programmers, its 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 vary between (x and n) where x and n (integers, x <=n ). Rule: creating exact filters and validation rules are best practice for me.

3. 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 are not exist in most users, the basic idea here is to distinguish between SQL query and the data that being used inside, both approach 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 these defense as you like, but, for beginners, if they can't quickly implement stored procedure and prepared statement, its better to filter input data as much they can.

Finally, lets 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.

Last point is detecting unexpected behavior which requires more effort and complexity, its 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.

References:

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

The simple alternative to this problem could be solved by granting appropriate permissions in the database itself. For example: if you are using 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.

A few guidelines for escaping special characters in SQL statements.

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

MySQLi

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

Example:

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

Example:

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

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

$stmt->execute();

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 prepared statement, you must specify the types of the variables for mysqli_stmt_bind_param function.

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

Example:

$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 

How protect index.php by sql-injection using .htaccess was not really a duplicate! Some have reported it as a duplicate question to this question. So, I'll post my answer here, an admin can even move my answer from here to a so called duplicate question:

However, if the attackers is trying to hack with the form via PHP $_GET variable or with the URL's query string, you can 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 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 queries from your site. The code above is giving you a tip, to rewrite or redirect (depends on you) that hacking-specific dynamic query string into a page that will store the attacker's I.P. address, or EVEN THEIR COOKIES, history, browser, or any other sensitive informations, and try to hack them back for security purpose.

A simple way would be to use a PHP framework like CodeIgniter or Laravel which have in-built features like filtering and active-record, so that you dont have to worry about these nuances.

Although not a perfect solution, you can get a big deal of protection for free, merely by using the ESAPI for PHP, from OWASP

Caution: they say their current release is not suitable for production use. I'd take that with a grain of salt, though.

The best protection you can get is is you know what you are doing in the first place.

Many small and large and very large websites have been hacked by use of injection due to programmers overconfidence in their skills.

To be able to write good secure code in the first place you need to understand know how injections work.

PHP is naively stupid at handling variable types and thus the main reason why most injections are done on PHP based sites.

The majority of programmers who use MySQL have never written a user function or a trigger, most never know what they are exactly and think they are very good programmer, some have heard about stored procedures but are not really sure how they work. Once you have a good clue on the true power of a database engine and a programming language and have studied all the fortes and weaknesses then you can start programming secure code.

One of my most important moment in my programmers life was after an interview when I realized I am not as good as I thought I was. That's when I really started to learn programming.

Just my two noob cents.