SQL Injection: How To Prevent Security Flaws In PHP / MySQL

Foreword: SQL injection is a very scary phrase. After all, it has single-handedly been responsible for putting down major government websites and thousands of personal home pages- and everything in between. (Something that has been increasingly popular after the “Techie” generation had puberty-riddled children.) Yet believe it or not, guarding against the attack is simple as a couple of lines of code.

SQL Injection: What It Is

There was once a famous doctor that had it completely right: never trust your patients. Now this doctor may have only been a sitcom doctor on the show “House,” but we’ll be taking a page from his book. Of course, in our case the patients will actually be Internet users. Don’t let your guard down! They are conniving, dastardly, plot-making fiends- and you’ll do well to remember it.

First, Let’s define an SQL Injection:

Define SQL Injection

  SQL Injection - \S-Q-L-in-'jek-shen\ - Noun
 The technique of inputting malicious data into an SQL statement, which would therefore make the vulnerability present on the database layer. Surprisingly, it seems everyone who has recently taken up learning a web development language has to try the technique out on their favorite websites. Luckily for said websites, this technique isn't at all hard to protect against.

The technique of inputting malicious data into an SQL statement, which would therefore make the vulnerability present on the database layer. Surprisingly, it seems everyone who has recently taken up learning a web development language has to try the technique out on their favorite websites. Luckily for said websites, this technique isn’t at all hard to protect against.

SQL Injection: What It Looks Like

The vast majority of all SQL injections will take place on an input form. Contrary to popular belief, this isn’t the only place where we will see them- it’s also common to manipulate URLs to inject SQL code. (But we’ll get more into that later.)

The most basic of all SQL injections will look like the following:

The Basic SQL Injection

 Variable' or 1=1--

Let’s say we have a login form. By inputting the above code, we can use our SQL injection to gain login even without proper credentials! So how’s it work? Take a look at the “bigger picture” below:

What It Looks Like On The Back-End

 SELECT * FROM users WHERE username = 'Variable' or 1=1--'

See how our code is nicely injected into the query? The result of this query will grant us access regardless of the username, since the result of “1=1″ will always be true. In this case, we bypass the whole selection process.

You may have been wondering what the double dashes are for ( — ). These dashes at the end tell the SQL server to ignore the rest of the query. If the exploit isn’t being used on an SQL server, then omitting the double dashes and ending single quote will get the desired results.

Note that while this is the most standard way, it certainly isn’t the only way that malicious users will gain entry. SQL queries will differ greatly from one syntax to another, and thus, so too should the SQL injection. It’s also common to see the following:

More SQL Injection Syntax Fun

    ') or ('1'='1
    "or "1"="1
    ' or '1'='1
    Or 1=1--
    " or 1=1--
    ' or 1=1--

SQL Injection: Attacking Via URLs

Did you know it was possible to attack an SQL server through a URL? Well, it’s possible, and usually much more dangerous to webmasters. When using PHP and SQL, there is commonly a URL such as the following:

• http://YourWebsite.com/login.php?id=2

By adding a little SQL to the end of the URL, we can do some very mischievous mischief:

• http://YourWebsite.com/login.php?id=2‘; DROP TABLE login; #

You might be confused by the hash. This little guy is just like the double dash we used earlier; it will tell the SQL query to halt after our input. And if you haven’t noticed, we just told the server to drop the entire table of users! This is an example of how powerful and dangerous SQL injections can be- and also shows that constant backups are a necessity.

Enough already! Let’s finally find out how to make sure that little script kiddies aren’t going to ruin the hard work webmasters and web developers set aside for their projects.

SQL Injection Prevention: Editing Lengths Of Form Components

The first step in the process is simple: simply restrict input fields to the absolute minimum- usually anywhere from 7-12 characters is fine. Doing so will make long queries unable to be input, since the field is only enough characters for smaller queries. This will actually not prevent an SQL injection, but will make work harder for those trying to make use of one.

Savvy SQL injection users can simply make a new form and remove the limits on the character length, since the length is in plain HTML and viewable (and editable) by anyone.

SQL Injection Prevention: Data Type Validation

Another good idea is to validate any data once it is received. If a user had to input an age, make sure the input is an actual number. If it was a date, make sure the date is in proper format. Again, this will not prevent an SQL injection in itself- it just makes work harder for those trying to exploit an SQL server.

Data type validation can be thwarted by modifying the query over a trial-and-error test period. This is still only slowing attackers down- but isn’t it much more satisfying to have them waste their time before finding out one’s own query is impervious to harm? Of course! An eye for an eye!

SQL Injection Prevention: User Privileges

It’s nice to be able to create a “super user” in one’s own database that can create, drop, and edit tables at will. The security-obsessive webmaster will want to make individual users that can only do one or two tasks at a time. In effect, this means that SQL injections will only be able to do one or two things at a time.

This is just a little prevention fun, it can certainly still cause a certain amount of danger. If a user is made for deleting tables, than an SQL injection can do the same thing- it just won’t be able to do much else. Regardless, deleting a table is a very big privilege to handle. This method is still useful for throwing attackers off track, as well as minimizing risk from areas of a website that aren’t critical to the security of the database.

SQL Injection Prevention: Magic Quotes (Which Aren’t So Magical)

We all love magic. Heck, magic is downright cool. It serves as the basis for great children books (Harry Potter, anyone?) and even has been used for themes in nerdy card games everyone seems to enjoy (Ah, we’re looking at you, Magic The Gathering!)

One thing that just doesn’t live up to the magic name is magic quotes. PHP developers thought it would be a wonderful idea to make a process that escapes all incoming data in a PHP script. Sounds like it would fix our problem with SQL injections, but alas, there are better ways.

Anyone who has recommended a fix with magic quotes doesn’t know what they are talking about. After all, magic quotes are considered deprecated and removed as of PHP version 6. So why such hostility over a process that is seemingly beneficial to our predicament?

The short answer: magic quotes are horrible for portability issues, performance issues, and they mess with other data that doesn’t need to be escaped.

1. Many scripts made with magic quotes won’t work on servers that have (intelligently) turned the feature off.


2. Performance loss is observed because not all of the data is being input into a database- we’re wasting process time.


3. Lastly, magic quotes are just inconvenient. They add an extra slash (”\”) to all of our form data, even when it might not be needed. To fix this, we have to use another process to fix it (If you are unfortunate enough to have used magic quotes, look up the stripslashes() function, and consider switching if possible)

We came close to finding a real solution there- almost! But we did learn something: don’t use magic quotes, and instead find an alternative that can escape the input data based on what we need: not what we don’t.

SQL Injection Prevention: The Solution In Preventing SQL Attacks

We could’ve given you the answer right away, but what fun would that have been? Too often, PHP developers are becoming lazy and not following proper security tactics the way they should. By reaching this point in the lecture, you’ve increased your knowledge on how SQL injections are used, how not to prevent the attacks, and finally: you’ll learn the right way to keep injection attacks at bay.

We’ll accomplish this last feat with a simple function that the developers of PHP made especially for SQL injections. We call this function mysql_real_escape_string() – take a look at it below:

mysql_real_escape_string() In Action!

    $name = "John";
    $name = mysql_real_escape_string($name);
    $SQL = "SELECT * FROM users WHERE username = '$name'";

Although for a more practical use, we would have the $name variable pointed to a POST result, as seen below:

$_POST Can Dig In On The Action Too!

    $name = mysql_real_escape_string($_POST['user']);

And we can even make things easier by putting it into one line:

All In One Line Now; Here We Go!

$SQL = "SELECT * FROM users where username = "mysql_real_escape_string($POST['user']);

So what’s the output like if malicious users try to get access to our SQL server? Well glad you asked! Their attempts may look something like this:

Cause And Effect With mysql_real_escape_string()

    $malcious_input = "' OR 1'";
    // The Above Is The Malicious Input. Don't Be Scared!
    // With The mysql_real_escape_string() usage, the following is obtained:

 \' OR 1\'
    // Notice how the slashes escape the quotes! Now users can't enter malicious data

And the best part is, they just wasted their time and effort for nothing. Now how’s that for vindication!

SQL Injection: Closing Comments

We’ve learned quite a bit today. SQL injections are bad. All Internet users are equally as bad. Protecting against both ensures a happy and stable web application. And above all else, never use magic quotes! Despite their cleverly disguised name, we’ve found no evidence of magic.

Lastly, note that there are libraries and classes that can help aid in the fight against SQL injection. Prepared statements are plausible as well, but as for us, we enjoy sticking to the mysql_real_escape_string() function for less headaches.

Bottom Line: mysql_real_escape_string() – It doesn’t have a magically awesome name, but it’s 24 characters worth of SQL injection-protection goodness.