What's the Right Way to Prevent SQL Injection in PHP Scripts?

How to prevent SQL injection in PHP scripts is probably a topic that doesn’t need anything more written about it. It is pretty easy to find blog posts, documentation, videos, etc.  that explain the importance of preventing SQL injection and suggestions for preventing it. In fact, I’ve already written a post on this topic as a guest writer on the SQL Server Driver for PHP team blog. However, it is important to have fresh information for new Web developers and I don’t necessarily agree with some of the most common suggestions for preventing SQL injection. (Besides, I hear that this is the Month of PHP Security.) So, this will be yet another post about preventing SQL injection, but I will offer my 2 cents about what I think is the right way to prevent it.

 

What is SQL Injection?

SQL injection (or a SQL injection attack) occurs when a user provides SQL code as user input for a Web page, and the SQL code is then executed in the database. For example, consider the following login script:

<form method="post" action="injection.php" enctype="multipart/form-data" >
Username:<input type="text" name="Username" id="Username"/></br>
Password:<input type="text" name="Password" id="Password"/></br>
<input type="submit" name="submit" value="Submit" />
</form>
<?php
$username = $_POST['Username'];
$password = $_POST['Password'];

$server = "MyServer\sqlexpress";
$options = array("Database"=>"ExampleDB", "UID"=>"MyUID", "PWD"=>"MyPWD");
$conn = sqlsrv_connect($server, $options);
$sql = "SELECT * FROM UserTbl WHERE Username = '$username' and Password = '$password'";
$stmt = sqlsrv_query($conn, $sql);
if(sqlsrv_has_rows($stmt))
{
echo "Welcome.";
}
else
{
echo "Invalid password.";
}
?>

Now consider the form with the following inputs:

   

Now the statement that is executed in the database is the following:

SELECT * FROM UserTbl WHERE Username= 'Brian' and Password= ''or 1 = 1--'

Because 1=1 is always true, this query will return all users. (Note that the last quotation is commented out.) So, in the script above, sqlsrv_has_rows is true, and access is granted.

SQL injection is possible here because user input is concatenated with the executed SQL code. Scripts should not be written in this way…ever. The example above only scratches the surface of what can be done with SQL injection – much more malicious attacks are possible.

 

A Common Attempt at Prevention

The most common suggestion I’ve seen for preventing SQL injection involves trying to remove or escape any possible SQL code from user input before concatenating it with the SQL code to be executed. There are several PHP functions (and functions in PHP extensions) that can be used to do this, but all of them are potentially vulnerable. If you concatenate user input with SQL code that will be executed in the database, you run the risk of a SQL injection attack no matter how much parsing and escaping of the input you do. How can you be 100% sure that you’ve thought of all possibilities that a creative hacker might think of? How can you be sure that you’ve taken the appropriate measures to mitigate an attack? How can you be sure that the functions you are using to remove or escape dangerous user input aren’t buggy?

Now, having posed those questions, can I actually come up with an attack that gets by the best of the "remove and escape" strategies? No. When done carefully, this strategy is pretty good at preventing SQL injection. However, it still allows for the possibility of some clever hacker finding a way to inject SQL, even if it is a remote possibility. Why take that chance when an easier, safer alternative exists?  

 

The Right Way to Prevent SQL Injection

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

When sqlsrv_query is called, an execution plan is created on the server before the query is executed. The plan only allows our original query to be executed. Parameter values (even if they are injected SQL) won’t be executed because they are not part of the plan. So, if I submit a password like I did in the example above ('or 1=1--), it will be treated as user input, not SQL code. In other words, the query will look for a user with this password instead of executing unexpected SQL code.

The script above, modified to prevent SQL injection, looks like this:

<form method="post" action="injection.php" enctype="multipart/form-data" >
Username:<input type="text" name="Username" id="Username"/></br>
Password:<input type="text" name="Password" id="Password"/></br>
<input type="submit" name="submit" value="Submit" />
</form>
<?php
$params = array($_POST['Username'], $_POST['Password']);

$server = "MyServer\sqlexpress";
$options = array("Database"=>"ExampleDB", "UID"=>"MyUID", "PWD"=>"MyPWD");
$conn = sqlsrv_connect($server, $options);
$sql = "SELECT * FROM UserTbl WHERE Username = ? and Password = ?";
$stmt = sqlsrv_query($conn, $sql, $params);
if(sqlsrv_has_rows($stmt))
{
echo "Welcome.";
}
else
{
echo "Invalid password.";
}
?>

Note: If you expect to execute a query multiple times with different parameter values, use the sqlsrv_prepare and sqlsrv_execute functions. The sqlsrv_prepare function creates an execution plan on the server once and the sqlsrv_execute function executes the query with different parameter values each time it is called.

I’m using SQL Server Express and the sqlsrv API to demonstrate parameterized queries here, but this technique can (and should) be applied regardless of the database and extension being used.

OK…that’s my 2 cents about preventing SQL injection. I’d certainly be interested in other opinions about the best way to prevent SQL injection…let me know what you think.

Thanks.

-Brian

Share this on Twitter

Comments

• Anonymous
  March 05, 2010
  Hi Brian,Thanks for these articles, they're very helpful.On this subject in particular, is there any chance of Microsoft resolving a bug in ODBC which has been around since 2006, and prevents bound parameters being used in sub-selects within queries.Microsoft bug report:https://connect.microsoft.com/SQLServer/feedback/details/521409/odbc-client-mssql-does-not-work-with-bound-parameters-in-subquery?wa=wsignin1.0PHP bug report:http://bugs.php.net/36561In addition to the SQL Server Driver for PHP it causes errors in PDO_ODBC and is a major headache for me at the moment.Thanks,Craig
• Anonymous
  March 05, 2010
  Hey Craig-Thanks for calling my attention to that bug...I wasn't aware of it. I'm following up with the development team and will hopfully have something to report soon.Thanks.-Brian
• Anonymous
  March 05, 2010
  I've talked with some folks on the development team. They are aware of this bug. They are aiming to fix it in the next release of ODBC. In the meantime, can you try specifying both the PHP type and SQL type of the parameter in question? The syntax for doing so is described here: http://msdn.microsoft.com/en-us/library/cc296184(SQL.90).aspx and here: http://msdn.microsoft.com/en-us/library/cc626305(SQL.90).aspx. If that doesn't work, you should be able to write a stored procedure that accepts the required parameter value (but they may not work if you are using an ORM).Hope that helps.-Brian
• Anonymous
  March 05, 2010
  Hi Brian,Input filtering using the Filter extensions:http://devzone.zend.com/node/view/id/1113and its manual: http://www.php.net/filterThat's a good start as well to prevent bad data in your DBs or views.
• Anonymous
  March 05, 2010
  You're right. The proper way to prevent SQL Injection is to use parameterized queries.The reason this is the correct way is that context matters. By using parameters, and injecting data into those placeholders as a secondary action, you're separating the control and data contexts.S
• Anonymous
  March 05, 2010
  Hey Brian,I was wondering, do you still consider input filtering to be of importance when passing variables into a query? I understand that as a principle, building queries with parameters is effective practice, but do you have any tips on what steps one should take in filtering user input?
• Anonymous
  March 06, 2010
  Best way to avoid SQL injection and other bugs is to apply numerous filters : I decode from the origins (var filters is a excellent way), then clean the unicode UTF-8//IGNORE , then remove any unwanted sideeffects (html tags by example, may change by the context) and eventually , i'm using  parametized queries.Multiple securities is the better policy.
• Anonymous
  March 07, 2010
  Hi Brian!my own website was attacked by some one 2 time in last month. I tried everything but no work.then I came to know a .htaccess rewrite. that is as I know the best thing to prevent sql Injection in php scripts.Let me know if anybody is interested to get the details.Thanks,qammar
• Anonymous
  March 07, 2010
  Hi Brian,Thanks for enquiring about that bug, it's very much appreciated. I'll try your suggestions and let you know how they work out :)Has a date been set for the next ODBC release (even a ballpark estimate would be helpful to me)?Cheers,Craig
• Anonymous
  March 08, 2010
  The comment has been removed
• Anonymous
  March 08, 2010
  The comment has been removed
• Anonymous
  March 09, 2010
  @Joe: Thanks for the comments. Some good food for thought. If I could generalize your comment about only using a RDBMS when you really need it, I'd say you should use the right tool for the right job. I think that rule applies to programming languages too. Personally, I've found PHP easier to pick up than Visual Basic or C#. Sometimes, being able to develop quickly fits the "right tool for the right job" requirement. And, as I learn more PHP, I'm finding that the writing secure, maintainable, well-architected code is becoming easier and easier.-Brian
• Anonymous
  March 15, 2010
  The comment has been removed
• Anonymous
  March 15, 2010
  The comment has been removed
• Anonymous
  August 22, 2010
  Great post, thanks.I've somewhat new to PHP/SQL logins, and although I can build a login system, I always knew that I was lacking in the department of security. This brings me one step closer to make my logins more secure. Thanks!
• Anonymous
  February 13, 2011
  Very good article and nice explainI want to post this article on my web site http://www.almekhlafi.com with write this site source if you no have any problemThankyou
• Anonymous
  February 14, 2011
  @abdulsalam almekhlafi-Feel free to re-post this article with a link back to the source.Glad you found it helpful.-Brian
• Anonymous
  February 18, 2011
  I had a database that got hacked in this way before discovering the above solution to it I wrote the login script a tad differently, I first do a count statement that does "SELECT COUNT(username) as count FROM user_table WHERE username='$username' AND password='$password'"then only if the count = 1 do I actually query the database with the SELECT statement to get user information. I found this way works, though there is more code and may not be as good of a solution.Thank you!
• Anonymous
  April 03, 2011
  Sorry to tell you but the above code is vulnerable to blind SQL injection. You may wish to try input sanitizeation.
• Anonymous
  April 03, 2011
  I'd be interested to see exactly how this vulnerable to "blind SQL injection". Can you elaborate?
• Anonymous
  February 07, 2013
  hi,its very useful information but its support mysql or only sql etc.,??Thanks
• Anonymous
  September 22, 2015
  The comment has been removed
• Anonymous
  July 26, 2016
  Thanks for these articles, they’re very helpful.
• Anonymous
  September 12, 2016
  Thnk you very much for the useful and concise information. Database security is not something you want to overlook.
• Anonymous
  October 11, 2016
  I think This is the Best way to Prevent SQL injection
• Anonymous
  November 30, 2016
  Hi, i was wondering if this solution was still up to date, since its been 6 years or are there anybetter methods>thanksSjoerd