If you’ve been around web development for a while, you’ve almost certainly heard the term “SQL injection” and some terrifying stories about it.
PHP, like many other languages, is not immune to this type of threat, which can be very dangerous indeed. But, luckily, protecting your websites from SQL injection and other similar threats is something you can take tangible steps towards.
In this post, you’ll learn what SQL injection is, what the consequences of a successful attack are, how an attacker can take advantage of vulnerable PHP code, what you can do to prevent it, and what tools you can use to detect the parts of your code that might be subject to this kind of threat. Additionally, you’ll learn about a few other common vulnerabilities you should be aware of in order to create more secure applications.
How does a SQL injection work?
The first thing you need to know in order to protect your code from SQL injection is to understand how it could be exploited by an attacker.
The idea behind the exploit is rather simple: An attacker runs malicious SQL code on your database through your app.
How could anyone achieve that? By abusing your application’s inputs.
Let’s look at a simple example. Suppose you have an application showing a list of user names, where each user has a link to their details like this:
And then, on the user_details.php file, you have this:
<?php $sql = "SELECT * FROM user WHERE id = ".$_GET['id'];
If the user acts as expected and clicks on some user’s name, the request URL will look like this: http://domain.com/user_details.php?id=8.
So, what’s wrong with it?
The value of $sql will be the string "SELECT * FROM user WHERE id = 8" and the query will run just fine.
The problem is in the italicized portion of this sentence: If the user acts as is expected and clicks on some user’s name, the request URL will look like this:
http://domain.com/user_details.php?id=8.
What if the user doesn’t act as expected?
What if someone were to manufacture a URL sending something other than a number as the ID?
Manufactured URLs
If the URL were something like http://domain.com/user_details.php?id=Peanuts, the resulting SQL string would be: "SELECT * FROM user WHERE id = Peanuts".
In this case, the problem would be for the user. The query would simply fail, and, in a worst-case scenario, the user would see some kind of weird message.
But what if the URL looked more like this:
http://domain.com/user_details.php?id=1+OR+1%3B? (This is the result of urlencode('1 OR 1;'), by the way.)
In this case, the resulting SQL would be "SELECT * FROM user WHERE id = 1 OR 1;". That means that the result of the query would be every user in the database instead of just the user whose ID equals 1. Ouch.
But that’s still not too bad, right?
Now let’s try a more extreme example.
Let’s suppose the URL was something like
http://domain.com/user_details.php?id=1%27%3B+DROP+TABLE+users%3B--+, which translates into id=1; DROP TABLE users;--, effectively producing this SQL command:
"SELECT * FROM users WHERE id = 1; DROP TABLE users;-- ". And if you run this query…big ouch.
In case you’re not 100% familiar with SQL syntax, DROP TABLE would completely delete the table. It would be like it never existed.
Of course, a recent backup would help reduce the damage, but still. This is bad.
If you want to learn more about this kind of attack, there’s a great post here.
But I think that’s enough bad news. Let’s move on to a more cheerful place: how you can prevent these types of SQL injections in your PHP apps.
How to prevent SQL injections in PHP
As you saw, the problem is when an attacker has the ability to run SQL code without you being aware of it.
This situation presents itself as a consequence of having queries created on the fly via string concatenation and including data gathered from external inputs (usually user input, but also other external sources such as files, responses to API calls, and so on).
So, in order to prevent this vulnerability in your code, you need to fix the sources of potential problems.
Validate your inputs
A simple way to fix the vulnerability in the above example would be to check whether the ID parameter is what is actually expected from it (i.e., a positive integer):
Another way to do this kind of validation is to leverage PHP’s built-in filters:
<?php $id = filter_input( INPUT_GET, 'id', FILTER_VALIDATE_INT);
So, by validating your inputs, you prevent attackers from executing malicious code alongside your own.
There’s no easy way to prevent them from trying, but as long as their attempts aren’t successful, you’re good to go.
Use prepared statements
Another way you can protect your code against SQL injections is by using prepared statements. Prepared statements are precompiled SQL commands.
They can be used with a specific database access library (such as mysqli) or with the more generic library PDO.
Let’s have a look at an example using mysqli:
If you try this code (replacing the database credentials, of course), you’ll see that, if accessed through a legal URL like http://domain.com/user_details.php?id=1, you’ll get the same result as any of the malicious URLs above (which is the information about user associated with ID 1 and nothing else).
If you prefer using PDO, the code will look more like this:
Not really much difference, right?
In summary, prepared statements offer a great way to prevent SQL injections. Also, they usually perform better than on-the-fly SQL.
Now that you know the tool, you just need to use it.
How to detect PHP code that’s vulnerable to SQL injections
Once the vulnerability is found, fixing it is not really complicated. But how do you find these types of vulnerabilities in your code in the first place?
Well, if you’re lucky and your codebase is small enough, a simple review of the code will do.
But if your application is medium-sized to big, you’ll need extra help.
One very popular (and open-source) tool you can use for this purpose is sqlmap, a simple Python script that will try to attack any URL you feed to it and report back on its findings.
Here’s a sample output from a run against a PHP-based website of mine:
Another open-source tool you can use is Arachni, a more sophisticated tool that includes a web GUI and was written in Ruby.
Here’s a sample output from running it through the CLI:
Beyond code review and testing, you should consider using a RASP solution, such as Sqreen, to monitor your production environments to stop the execution of any exploits that make it through the review and testing stages.
What other vulnerabilities should you be aware of?
Code injections
SQL injection is just one member of a bigger family of vulnerabilities: code injection.
The idea behind different code injection techniques is always the same. It’s all about tricking an innocent application into running malicious code.
This can be done in several ways.
In PHP, there are functions designed to simply issue commands right into the operating system, such as the exec function.
Here’s a simple example of how this could be exploited:
<?php
exec( 'cp uploads/'.$_GET['file'].' /secure/');
If someone were to request http://domain.com/exec.php?file=a.txt%3B+rm+-Rf+%2A+%23, the result would be the execution of this command:
cp uploads/a.txt; rm -Rf * # /secure/
And this could wipe out the entire disk on the server—unvalidated input strikes again!
Cross-site scripting
Another very common vulnerability is cross-site scripting (XSS). In this case, the victim is the user visiting your site.
XSS is when an attacker injects malicious JavaScript code inside a regular HTML form, which will later be rendered by another user’s browser.
Look at this PHP script:
<?php
echo "<html><p>Hello {$_GET['name']}!</p></html>";
Clearly, the intention here is to create a simple page that greets the user.
But when baddies get to it, what prevents them from requesting
http://domain.com/greet.php?name=Mauro%3Cscript+language%3D%22javascript%22%3Ealert%28%22You+are+hacked%21%22%29%3B%3C%2Fscript%3E? This request will produce the following HTML as output:
<html><p>Hello Mauro<script language="javascript">alert("You are hacked!");</script></p></html>
This is clearly a very naive exploit—after all, what’s the harm in showing someone a simple “You are hacked!” message? But things can get pretty serious with a little imagination. Just bear in mind that every request made to a server includes the session cookie, so this vulnerability can be the culprit behind your users’ sessions getting hijacked.
Then again, while the problems look ugly, the fixes are really simple: It’s all about validation and sanitization.
In the case of XSS, a simple call to htmlentities before producing the actual output will do.
Fixing vs. detecting security vulnerabilities
As you saw in this post, fixing security vulnerabilities—both SQL injections and other types—is not the main challenge. Realizing that your code is vulnerable, and where it can be exploited, is. You have a couple options here:
- Have very strong coding discipline
- Test your applications thoroughly for security issues
- Leverage monitoring and protection tools to prevent exploits and get alerts in a timely manner
Which one you choose is up to you. The important thing is to pay attention to these problems and act on that knowledge. Stay safe.
—-
This post was written by Mauro Chojrin. Mauro helps PHP developers hone their craft through his trainings, books, workshops, and other tools. He’s been in the IT industry since 1997 and has held roles such as developer, architect, and leader of technical teams. Mauro also likes to write and vlog.
