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

https://blogs.msdn.microsoft.com/brian_swan/2010/03/04/whats-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.