Yes, it’s instant destruction we’re talking about, because I don’t want to open this article with the usual lame terminology of “tightening security” and “preventing malicious access.” SQL Injection is such an old trick in the book that everyone, every developer, knows about it very well and is well aware of how to prevent it. Except for that one odd time when they slip up, and the results can be nothing short of disastrous. If you already know what SQL Injection is, feel free to skip to the latter half of the article. But for those who are just coming up in the field of web development and are dreaming of taking on more senior roles, some introduction is in order.
What is SQL Injection?
The key to understanding SQL Injection is in its name: SQL + Injection. The word “injection” here doesn’t have any medical connotations, but rather is the usage of the verb “inject.” Together, these two words convey the idea of putting SQL into a web application. Putting SQL into a web application . . . hmmm . . . Isn’t that what we’re doing anyway? Yes, but we don’t want an attacker to drive our database. Let’s understand that with the help of an example. Let’s say you’re building a typical PHP website for a local e-commerce store, so you decide to add a contact form like this: And let’s assume the file send_message.php stores everything in a database so that the store owners can read user messages later on. It may have some code like this: So you’re first trying to see if this user already has an unread message. The query SELECT * from messages where name = $name seems simple enough, right? WRONG! In our innocence, we’ve opened the doors to the instant destruction of our database. For this to happen, the attacker has to have the following conditions met:
The application is running on a SQL database (today, almost every application is) The current database connection has “edit” and “delete” permissions on the database The names of the important tables can be guessed
The third point means that now that the attacker knows you’re running an e-commerce store, you’re very likely storing the order data in an orders table. Armed with all this, all the attacker needs to do is provide this as their 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:
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: 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: 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. 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.
