How can I prevent SQL injection in PHP?

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?

Solution 1

The correct way to avoid SQL injection attacks, no matter which database you use, is to separate the data from SQL, so that data stays data and will never be interpreted as commands by the SQL parser. It is possible to create an SQL statement with correctly formatted data parts, but if you don't fully understand the details, you should always 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([ '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 (for example, pg_prepare() and pg_execute() for PostgreSQL). PDO is the universal option.

---

Correctly setting up the connection

PDO

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=utf8mb4', 'user', 'password');

$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 PDO will inform you of all MySQL errors by means of throwing the PDOException.

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 (before 5.3.6) silently ignored the charset parameter in the DSN.

Mysqli

For mysqli we have to follow the same routine:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // error reporting
$dbConnection = new mysqli('127.0.0.1', 'username', 'password', 'test');
$dbConnection->set_charset('utf8mb4'); // charset

---

Explanation

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([ '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';
}

Solution 2

To use the parameterized query, you need to use either Mysqli or PDO. To rewrite your example with mysqli, we would need something like the following.

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

$variable = $_POST["user-input"];
$stmt = $mysqli->prepare("INSERT INTO table (column) VALUES (?)");
// "s" means the database expects a string
$stmt->bind_param("s", $variable);
$stmt->execute();

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.

Solution 3

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

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

And prepared statements cover 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)); // if we have such a name
$orderby = $orders[$key]; // If not, first one will be set automatically. 
$query = "SELECT * FROM `table` ORDER BY $orderby"; // Value is safe

To ease the process I wrote a whitelist helper function that does all the job in one line:

$orderby = white_list($_GET['orderby'], "name", ["name","price","qty"], "Invalid field name");
$query  = "SELECT * FROM `table` ORDER BY `$orderby`"; // sound and safe

There is another way to secure identifiers - escaping but I rather stick to whitelisting as a more robust and explicit approach. Yet as long as you have an identifier quoted, you can escape the quote character to make it safe. For example, by default for mysql you have to double the quote character to escape it. For other other DBMS escaping rules would be different.

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.

So, a general recommendation may be phrased as

• Any variable that represents an SQL data literal, (or, to put it simply - an SQL string, or a number) must be added through a prepared statement. No Exceptions.
• Any other query part, such as an SQL keyword, a table or a field name, or an operator - must be filtered through a white list.

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

Solution 4

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

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

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

Solution 5

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

View more solutions

Comments

[Rob]

Indeed; running with magic_quotes switched on just encourages poor practice. However, sometimes you can't always control the environment to that level - either you don't have access to manage the server, or your application has to coexist with applications that (shudder) depend on such configuration. For these reasons, it's good to write portable applications - though obviously the effort is wasted if you do control the deployment environment, e.g. because it's an in-house application, or only going to be used in your specific environment.

[BryanH]

As of PHP 5.4, the abomination known as 'magic quotes' has been killed dead. And good riddance to bad rubbish.

[Álvaro González]

This is the best you can do with legacy mysql extension. For new code, you're advised to switch to mysqli or PDO.

[Polynomial]

If you don't quote the string, it's still injectable. Take $q = "SELECT col FROM tbl WHERE x = $safe_var"; for example. Setting $safe_var to 1 UNION SELECT password FROM users works in this case because of the lack of quotes. It's also possible to inject strings into the query using CONCAT and CHR.

[Sumit Gupta]

@Zaffy, I like the idea, but what about performance, I mean if you have 1 million records and 1000 users searching does it slow down as compare to prepare solution ?

[Sumit Gupta]

I just test SELECT * FROM tblproducts WHERE product_code LIKE ( '%42%') does find record but SELECT * FROM tblproducts WHERE product_code LIKE ('%' +0x3432 +'%') doesn't, so it simply doesn't work or I did something wrong ?

[Zaffy]

@SumitGupta Yea, you did. MySQL doesnt concatenate with + but with CONCAT. And to the performance: I dont think it affects performance because mysql has to parse data and it doesnt matter if origin is string or hex

[sectus]

I am not agree with this 'a specially-made function to prevent these attacks'. I think that mysql_real_escape_string purpose is in allow to build correct SQL query for every input data-string. Prevention sql-injection is the side-effect of this function.

[glglgl]

@Polynomial Completely right, but I'd see this merely as wrong usage. As long as you use it correctly, it will definitely work.

[DjOnce]

so, if I write these codes, db is still unprotected? mysql_query("INSERT INTO table (column) VALUES ('$safe_variable')");

[Your Common Sense]

Also, there is absolutely no point in checking $_POST array members with is_string()

[Nazca]

you dont use functions to write correct input data-strings. You just write correct ones that don't need escaping or have already been escaped. mysql_real_escape_string() may have been designed with the purpose you mention in mind, but its only value is preventing injection.

[Arvind Bhardwaj]

Does not prevent '1 OR 1=1'

[eggyal]

WARNING! mysql_real_escape_string() is not infallible.

[eggyal]

WARNING! mysql_real_escape_string() is not infallible.

[eggyal]

WARNING! mysql_real_escape_string() is not infallible.

[Wayne Whitty]

@eggyal Especially if you're messing around with different charsets.

[jww]

mysql_real_escape_string is now deprecated, so its no longer a viable option. It will be removed in the future from PHP. Its best to move onto what the PHP or MySQL folks recommend.

[jww]

mysql_real_escape_string is now deprecated, so its no longer a viable option. It will be removed in the future from PHP. Its best to move onto what the PHP or MySQL folks recommend.

[jww]

mysql_real_escape_string is now deprecated, so its no longer a viable option. It will be removed from PHP in the future. Its best to move onto what the PHP or MySQL folks recommend.

[Scott Arciszewski]

@rahularyansharma I'd like to request this be modified with a disclaimer: Prepared Statements are easier to use safely and are more secure from an engineering standpoint: the parameters and the query string are sent in separate packets, thus preventing the parameters from altering the query string. Also: stackoverflow.com/questions/5741187/…

[Josip Ivic]

You can prevent SQL injection if you adopt an input validation technique in which user input is authenticated against a set of defined rules for length, type and syntax and also against business rules.

[Pratik]

Please give me example before mysqli_real_escape_string and after the string which gets generated. As it casuse confusion and no example is given.

[Randall Valenciano]

Also, the official documentation of mysql_query only allows to execute one query, so any other query besides ; is ignored. Even if this is already deprecated there are a lot of systems under PHP 5.5.0 and that may use this function. php.net/manual/en/function.mysql-query.php

[Alix]

This is a bad habit but is a post-problem solution : Not only for SQL injection but for any type of injections (for example there was a view template injection hole in F3 framework v2) if you have a ready old website or app is suffering from injection defects , one solution is to reassign the values of your supperglobal predefined vars like $_POST with escaped values at bootstrap. By PDO, still it is possible to escape (also for today frameworks) : substr($pdo->quote($str, \PDO::PARAM_STR), 1, -1)

[cjohansson]

Ok the bypass says nothing about "To learn more about why prepared statements are better at stopping SQL injection, refer to this mysql_real_escape_string() bypass". Rather the opposite, that prepared statements suffer the same issues and PDO is not an exception. "It gets worse. PDO defaults to emulating prepared statements with MySQL. That means that on the client side, it basically does a sprintf through mysql_real_escape_string() (in the C library), which means the following will result in a successful injection:"

[HoldOffHunger]

This is one of the few cases where I would use an "escaped value" instead of a prepared statement. And integer type conversion is extremely efficient.

[Dustin Graham]

The question is very generic. Some great answers above, but most suggest prepared statements. MySQLi async does not support prepared statements, so the sprintf looks like a great option for this situation.

[Your Common Sense]

This answer is essentially wrong, as it doesn't help to prevent an injection prevention but just trying to soften the consequences. In vain.

[Apurv Nerlekar]

Right, it doesn't provide a solution, but is what you can do before hand to avoid things.

[Alexander Holsgrove]

@Apurv If my goal is to read private information from your database, then not having the DELETE permission means nothing.

[Apurv Nerlekar]

@AlexHolsgrove: Take it easy, I was just suggesting good practices for softening the consequences.

[Alexander Holsgrove]

@Apurv You don't want to "soften consequences", you want to do everything possible to protect against it. To be fair though, setting the correct user access is important, but not really what the OP is asking for.

[donis]

This answer lacks the explanation of what is a prepared statement - one thing - it's a performance hit if you use a lot of prepared statements during your request and sometimes it accounts for 10x performance hit. Better case would be use PDO with parameter binding off, but statement preparation off.

[Anthony Rutledge]

I think your first paragraph is important. Understanding is key. Also, everyone is not working for a company. For a large swath of people, frameworks actually go against the idea of understanding. Getting intimate with the fundamentals may not be valued while working under a deadline, but the do-it-yourselfers out there enjoy getting their hands dirty. Framework developers are not so privileged that everyone else must bow and assume they never make mistakes. The power to make decisions is still important. Who is to say that my framework won't displace some other scheme in the future?

[Johannes Fahrenkrug]

@AnthonyRutledge You are absolutely correct. It is very important to understand what is going on and why. However, the chance that a true-and-tried and actively used and developed framework has run into and solved a lot of issues and patched a lot of security holes already is pretty high. It's a good idea to look at the source to get a feel for the code quality. If it's an untested mess it's probably not secure.

[Anthony Rutledge]

Here. Here. Good points. However, would you agree that many people can study and learn to adopt an MVC system, but not everyone can reproduce it by hand (controllers and server). One can go too far with this point. Do I need to understand my microwave before I heat up my peanut butter pecan cookies my girl friend made me? ;-)

[Johannes Fahrenkrug]

@AnthonyRutledge I agree! I think the use-case makes a difference too: Am I building a photo gallery for my personal homepage or am I building an online banking web application? In the latter case it's very important to understand the details of security and how a framework that I am using is addressing those.

[Anthony Rutledge]

Ah, the security exception to the do it yourself corollary. See, I tend to be willing to risk it all and go for broke. :-) Kidding. With enough time, people can learn to make a pretty darn secure application. Too many people are in a rush. They throw their hands up and assume that the frameworks are safer. After all, they do not have enough time to test and figure things out. Moreover, security is a field that requires dedicated study. It is not something mere programmers know in depth by virtue of understanding algorithms and design patterns.

[CodingInTheUK]

User1: insert, update, User2: Selects, User3: Delete. Complicated to maintain and may have performance impact having three users connecting but would definitely lock down what can be done in a given situation. User 1 cant delete or read, but adding a column "remove" you can update it where you need something deleting, Do deletes from a cron job using User3 checking for your flag. Point im making, if an insert/update is made sensitive info cant be pulled, if a select is made a delete or write/update can't be made etc. Doesnt eliminate all issues but should help?

[degr]

@Chris, you are wrong, it do nothing except additional butt pain. For an example, we have query select id, name from dropdown_table where status = '$YOUR_STATUS_VARIABLE$'; Now, pass 1' union all select id, email from users -- as $YOUR_STATUS_VARIABLE$ and you will see how it work.

[CodingInTheUK]

I didnt claim it solved all issues, thats a matter of coding correctly, but a user with only select cant update or delete, a user with only update cant select delete etc. And as i said deleting could be done via a cron job mark the rows for deletion and delete on the next cron run. Will this eliminate all attacks, absolutely not. but it will make life difficult for attackers.

[Kassem Itani]

Using PDO is better, in case you are using direct query make sure you use mysqli::escape_string

[Bimal Poudel]

Theme: Do not trust user's submitted data. Anything you expect is a garbage data with special characters or boolean logic, which should itself become a part of SQL query you may be executing. Keep $_POST values as data only, not SQL part.

[Sanke]

I think the whole point of the question is to get this done without using such framework.

[peiman F.]

using mysql_real_escape_string is enough or i must use parameterized too?

[Goufalite]

@peimanF. keep a good practice of using parametrized queries, even on a local project. With parametrized queries you are guaranteed that there will not be SQL injection. But keep in mind you should sanitize the data to avoid bogus retrieval (i.e. XSS injection, such as putting HTML code in a text) with htmlentities for example

[Richard]

@peimanF. Good practise to parametrized queries and bind values, but real escape string is good for now

[p0358]

@Alix this sounds like a good idea in theory, but sometimes the values need a different kind of escapes, for example for SQL and HTML

[Steen Schütt]

I understand the inclusion of mysql_real_escape_string() for completeness, but am not a fan of listing the most error-prone approach first. The reader might just quickly grab the first example. Good thing it's deprecated now :)

[Jahanzeb Awan]

Please explain oci_bind_by_name parameters.

[Abhijeet Kambli]

Above code doesn't work. mysqli_real_escape_string expects two params. check

[5ervant - techintel.github.io]

@RápliAndrás Some sort of ([0-9\-]+)=([0-9]+).

[Rick James]

@peimanF. - Don't do both; it will double-escape things! Pick either parameterization (preferred) or mysqli_real_escape_string

[Rick James]

@SteenSchütt - All the mysql_* functions are deprecated. They were replaced by similar mysqli_* functions, such as mysqli_real_escape_string.

[Steen Schütt]

@RickJames My point is, that you are more likely to make mistakes with the old approach of manually quoting your parameters. I am aware that the old MySQL functions are deprecated/removed and that the mysqli extension does something similar, but we should be teaching prepared statements first, and then "the old ways" for completeness.

[mickmackusa]

that mysqli is incorrect. The first param expresses the data types.

[Your Common Sense]

this answer is misleading. PDO is not a magic wand that protects your queries by a mere presence. You ought to substitute every variable in your query with a placeholder to get a protection from PDO.

[Your Common Sense]

This overcomplicated approach is absolutely for naught. One could use simple quoting function "'".$mysqli->escape_string($_GET["id"])."'" instead of this hexing/unhexing paraphernalia. But it will be equally limited, leaving your application exposed to SQL injection for the cases where it's inapplicable.

[pocketrocket]

I honestly disagree on your suggestion. This could lead to a false postive feeling of security throwing in any ORM. Of course, most of those take care of prepared statements and parameterized queries. A newbie coming to this post might still feel secure by picking any ORM - trusting them all. In general ORM are easing up things by hiding / abstracting implementation details. You really WANT to check (or blindly trust) how it's done. Rule of thumb: The bigger the open source community (support) behind it, the less it's totally screwed ;)

[Shayne]

Honestly its not the worst idea , pocketrocket. Depending on the ORM, theres a very very high chance that the authors of the ORM know their way around SQL better than the coder. Its kind of like that old rule of encryption that unless you've got your name on research papers in the field, dont roll your own, because chances are the attacker DOES have his name on papers in the field. That said if its an ORM requiring you to supply all or part of the query (ie Model.filter('where foo = ?',bar), you may well be better off rolling hand SQL

[andrew pate]

As an aside php has command injection vunerabilities to worth considering. One way round this is to block requests containing special characters, or that looks unduely long at the loadbalancer/webserver before they get to PHP... gracefulsecurity.com/… owasp.org/www-community/vulnerabilities/PHP_Object_Injection

[niCk cAMel]

I thought you couldn't run multiple queries in the same mysql_query. Consider this answer.

[Edgaras]

@Zaffy, thanks, it helps a Lot. I tested by myself, and your "formula" hex/unhex prevents the most common SQL injection attacks. It's possible broke this, a leak on the process or something? At least in a way that you know ..

[user138720]

I'd just like to point out something blindingly missing from the first five minutes of this thread, but worth mentioning here. It validly relates to the OP question. The vast majority of users do not require an account with anything other than INSERT SELECT and UPDATE Privileges. Some tables have multiple columns beginning with deleted. This immediately solves the Bobby Tables problem.

[Jack]

Any reason why you test for empty($dir) ?

[Admin]

There are two points perhaps can lessen the risk I think - 1. framework like doctorine 2. some static code analysis tool - there many paid ones but there are some great free ones too.

[TomoMiha]

Excellent and absolutely brilliant architectural answer !!! I also do this all the time :-), because it limits the amount of damage that could be done by the infamous attackers

[Daniel L. VanDenBosch]

Do you have any resources or further explain what you mean? when you say substitute every variable in your query with a placeholder do you mean the bindvalue thing?

[Gregory R.]

use mysqli_real_escape_string instead

[Lloyd Bunting]

Also: Pros and cons of Safe PDO discussed here: stackoverflow.com/questions/1314521/…