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:



    image


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

  1. Craig Marvelley says:

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

    PHP bug report:

    http://bugs.php.net/36561

    In 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

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

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

  4. Pierre says:

    Hi Brian,

    Input filtering using the Filter extensions:

    http://devzone.zend.com/node/view/id/1113

    and its manual: http://www.php.net/filter

    That’s a good start as well to prevent bad data in your DBs or views.

  5. Sean Coates says:

    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

  6. Jukka says:

    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?

  7. Da Scritch says:

    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.

  8. qammar says:

    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

  9. Craig Marvelley says:

    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

  10. @Sean Coates: Agreed…and it took me an entire post to say what you said in one sentence. 🙂

    @Jukka: I think filtering should be used to validate user input (e.g. make sure an e-mail address has a valid format). See Pierre’s comments and links (http://devzone.zend.com/node/view/id/1113) about filtering for more info.

    @Da Scritch: I’m not sure you gain any security by doing both filtering AND parameterized queries. Of course, this depends on how your access stack or server treats parameter values. If the stack/server doesn’t allow parameter values to ever be code, then you don’t gain any thing by doing filtering first (unless your filtering is for validation, not security purposes).

    @qammar: I’m not sure the .htaccess file is intended to be used for SQL injection prevention. I’m no expert with Apache, but I’ve not heard of the that file being used in this way. More detail here: http://en.wikipedia.org/wiki/Htaccess

    @Craig Marvelley: Please let me know if you make any progress. My understanding is that ODBC will release with the next release of SQL Server (I could be wrong about that, however).

  11. Joe says:

    The right way to prevent SQL injection in PHP Scripts is…  don’t use PHP.   It’s a horrible language, for a raft of reasons.    It’s the open source world’s answer to Visual Basic, making it easy for untalented programmers to create insecure, unmaintainable, badly architected systems.

    Use something that has built-in taint checking (IE Perl or Ruby).   This forces you to validate your inputs before you do anything with them.   The language itself  automatically makes any attempt to use untrusted, unvalidated data in an unsafe manner a fatal error.  

    Likewise, don’t use a relational database unless you actually need the features it offers (Strong consistency, atomic transactions, ad-hoc queries, etc).   The VAST majority of web development doesn’t need the features of an RDBMS (or the overhead that goes with it).   Most websites are better off using a simple key-value store like BerkeleyDB for data persistence, and the 1% or less that aren’t are probably better off using a distributed kvs like Cassandra or HBase.

    If you must use a RDBMS, parameterized queries are a good starting point but the RIGHT way to do it is with stored procedures.   With SPs you can grant permission to the procedure, rather than a table.   This prevents an untrusted user from being able to run any query except the ones you have explicitly defined, even if they find a hole in your application.    This is called defense in depth, and is the basis of all real security.

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

  13. Craig Marvelley says:

    Hi Brian,

    Just wanted to let you know that I looked into your suggestions; something like that would be perfect. PDOStatement::bindParam() does have a ‘data_type’ parameter, but I don’t think it’s comparable  since its not driver-specific. It didn’t make a difference to my queries, anyway!

    There’s a generic ‘driver specific options’ parameter that could be suitable for something like this, but I can’t find a reference to its use in the documentation or in the source of the PDO ODBC extension. Patching the extension to support a type hint through that parameter is a possibility that I’m looking into.

    Cheers,

    Craig

  14. @Craig: Glad to hear you are making progress. I’d be interested in the details of your final solution. You can e-mail me at brian.swan ‘at’ microsoft ‘dot’ com. Let me know if you need help with anything else.

    -Brian

  15. Warjat says:

    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!

  16. abdulsalam almekhlafi says:

    Very good article and nice explain

    I want to post this article on my web site http://www.almekhlafi.com with write this site source if you no have any problem

    Thankyou

  17. @abdulsalam almekhlafi-

    Feel free to re-post this article with a link back to the source.

    Glad you found it helpful.

    -Brian

  18. thejoester says:

    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!

  19. Donkey Kong says:

    Sorry to tell you but the above code is vulnerable to blind SQL injection. You may wish to try input sanitizeation.

  20. I'd be interested to see exactly how this vulnerable to "blind SQL injection". Can you elaborate?

  21. Mathankumar G says:

    hi,its very useful information but its support mysql or only sql etc.,??

    Thanks

  22. Fahad Rafiq says:

    SQL injections may cause the exploitation of data .Through such techniques hackers may put some malicious code in the database. Hence it is highly recommended to use prevention techniques to avoid such vulnerabilities. There are many methods to prevent SQL injections,2 of them are discussed in the post. Prepare statements are also used to make the execution process much quicker. To get more tips about prevention from SQL injects, read this article: http://www.cloudways.com/…/protect-php-website-sql-injection