How can I prevent SQL injection in PHP?

 What is SQL Injection and How to Prevent in PHP Applications?

Do you think that your SQL database works fine and is safe from immediate destruction? Well, SQL injection disagrees!

Yes, we are talking about immediate death because I do not want to start this article with the usual lame terminology of "strict" and "malicious prevention". SQL injection is so old in the book that every developer knows it very well and knows how to stop it. Except in a rare case where they are wrong and the results can be disastrous.

If you already know what SQL injection is, feel free to skip to the second part of the article. But those who are just getting started in web development and dreaming to occupy the higher ranks have some things in them.

What is SQL Injection?

Do you think your SQL database works fine and is protected against instant destruction? Well, SQL Injection disagrees!

Yes, we are talking about instant death because I don't want to start this article with the usual lame terminology of "hardening" and "malicious access prevention". SQL injection is a trick so old in the book that every developer knows it very well and knows how to prevent it. Except for a rare case where they are wrong and the results can be disastrous.

If you already know what SQL injection is, feel free to skip to the second half of the article. But for those who are just getting started in web development and dreaming of occupying higher ranks, there are a few things worth getting into.

<form action="record_message.php" method="POST">
  <label>Your name</label>
  <input type="text" name="name">
  
  <label>Your message</label>
  <textarea name="message" rows="5"></textarea>
  
  <input type="submit" value="Send">
</form>

Suppose the send_message.php file stores everything in a database so that store owners can later read user messages. You can have code like this:

<?php

$name = $_POST['name'];
$message = $_POST['message'];

// check if this user already has a message
mysqli_query($conn, "SELECT * from messages where name = $name");

// Other code here

So first you are trying to find out if this user has any unread message. A selection from those SELECT * from messages where name = $name sounds very simple, right?

wrong!

In our innocence, we have opened doors to immediate destruction of our database. To do this, the attacker must meet the following conditions:

Application runs on SQL database (almost all current applications) The current connection to the database has "edit" and "delete" permissions on the database. You can guess the important names of tables .The third point means that now that the attacker knows that he has an e-commerce store, he is most likely to store the data in the order table. Armed with all this, all the attackers must do in his name:

Joe; truncate orders;? Yes, sir! Let’s see what the query will become when it gets executed by the PHP script:

SELECT * FROM messages WHERE name = Joe; truncate orders;

Okay, the first part of the query has a syntax error (no quotes around “Joe”), but the semi-colon forces the MySQL engine to start interpreting a new one: truncate orders. Just like that, in one single swoop, the entire order history is gone!

Now that you know how SQL Injection works, it’s time to look at how to stop it. The two conditions that need to be met for successful SQL injection are:

1. The PHP script should have modify/delete privileges on the database. I think this is true of all applications and you’re not going to be able to make your applications read-only. 🙂 And guess what, even if we remove all modify privileges, SQL injection can still allow someone to run SELECT queries and view all the database, sensitive data included. In other words, reducing database access level doesn’t work, and your application needs it anyway.
2. User input is being processed. The only way SQL injection can work is when you are accepting data from users. Once again, it’s not practical to stop all inputs for your application just because you’re worried about SQL injection.

Preventing SQL injection in PHP

Now, given that database connections, queries, and user inputs are part of life, how do we prevent SQL injection? Thankfully, it’s pretty simple, and there are two ways to do it: 1) sanitize user input, and 2) use prepared statements.

Sanitize user input

If you’re using an older PHP version (5.5 or lower, and this happens a lot on shared hosting), it’s wise to run all your user input through a function called mysql_real_escape_string(). Basically, what it does it remove all special characters in a string so that they lose their meaning when used by the database.

For instance, if you have a string like I'm a string, the single quote character (‘) can be used by an attacker to manipulate the database query being created and cause a SQL injection. Running it through mysql_real_escape_string()produces I\'m a string, which adds a backslash to the single quote, escaping it. As a result, the whole string now gets passed as a harmless string to the database, instead of being able to participate in query manipulation.

There is one drawback with this approach: it’s a really, really old technique that goes along with the older forms of database access in PHP. As of PHP 7, this function doesn’t even exist anymore, which brings us to our next solution.

Use prepared statements

Prepared statements are a way to make database queries more safely and reliably. The idea is that instead of sending the raw query to the database, we first tell the database the structure of the query we’ll be sending. This is what we mean by “preparing” a statement. Once a statement is prepared, we pass the information as parametrized inputs so that the database can “fill the gaps” by plugging in the inputs to the query structure we sent before. This takes away any special power the inputs might have, causing them to be treated as mere variables (or payloads, if you will) in the entire process. Here’s what prepared statements look like:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// prepare and bind
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);

// set parameters and execute
$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";
$stmt->execute();

$firstname = "Mary";
$lastname = "Moe";
$email = "mary@example.com";
$stmt->execute();

$firstname = "Julie";
$lastname = "Dooley";
$email = "julie@example.com";
$stmt->execute();

echo "New records created successfully";

$stmt->close();
$conn->close();
?>

I know the process sounds unnecessarily complex if you’re new to prepared statements, but the concept is well worth the effort. Here’s a nice introduction to it.

For those who are already familiar with PHP’s PDO extension and using it to create prepared statements, I have a small piece of advice.

Warning: Be careful when setting up PDO

When using PDO for database access, we can get sucked into a false sense of security. “Ah, well, I’m using PDO. Now I don’t need to think about anything else” — this is how our thinking generally goes. It’s true that PDO (or MySQLi prepared statements) is enough to prevent all sorts of SQL injection attacks, but you must be careful when setting it up. It’s common to just copy-paste code from tutorials or from your earlier projects and move on, but this setting can undo everything:

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

What this setting does is to tell PDO to emulate prepared statements rather than actually use the prepared statements feature of the database. Consequently, PHP sends simple query strings to the database even if your code looks like it’s creating prepared statements and setting parameters and all of that. In other words, you’re as vulnerable to SQL injection as before. 🙂

The solution is simple: make sure this emulation is set to false.

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Now the PHP script is forced to use prepared statements on a database level, preventing all sorts of SQL injection.

Preventing using WAF

Do you know you can also protect web applications from SQL injection by using WAF (web application firewall)?

Well, not just SQL injection but many others layer 7 vulnerabilities like cross-site scripting, broken authentication, cross-site forgery, data exposure, etc. Either you can use self-hosted like Mod Security or cloud-based as the following.

• SUCURI
• Cloudflare
• Indusface

SQL injection and modern PHP frameworks

The SQL injection is so common, so easy, so frustrating and so dangerous that all modern PHP web frameworks come built-in with countermeasures. In WordPress, for example, we have the $wpdb->prepare() function, whereas if you’re using an MVC framework, it does all the dirty work for you and you don’t even have to think about preventing SQL injection. It’s a little annoying that in WordPress you have to prepare statements explicitly, but hey, it’s WordPress we’re talking about. 🙂

Anyway, my point is, the modern breed of web developers don’t have to think about SQL injection, and as a result, they aren’t even aware of the possibility. As such, even if they leave one backdoor open in their application (maybe it’s a $_GET query parameter and old habits of firing off a dirty query kick in), the results can be catastrophic. So it’s always better to take the time to dive deeper into the foundations.

Conclusion

SQL Injection is a very nasty attack on a web application but is easily avoided. As we saw in this article, being careful when processing user input (by the way, SQL Injection is not the only threat that handling user input brings) and querying the database is all there is to it. That said, we aren’t always working in the security of a web framework, so it’s better to be aware of this type of attack and not fall for it.