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.

PHP MySQL

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.

sql injection prevent

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.

Comments
  1. Learn PHP Online » Blog Archive » Email Activation For Registration Forms
    April 3, 2009

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

    Leave a reply
  2. Mishant
    April 22, 2009

    thnx for such good information.

    Leave a reply
  3. rashmi
    April 28, 2009

    How SQL INJECTION is used for security testing. Please briefly describe me with examples.

    Leave a reply
  4. Zachary Schuessler
    April 28, 2009

    Rashmi-

    If you want to use SQL injections for security testing you can use two different tools I found for FireFox:
    1. addons.mozilla.org/en-US/firefox/addon/6727
    2. addons.mozilla.org/en-US/firefox/addon/3899

    Good luck!

    Leave a reply
  5. Minesh Ghimire
    May 3, 2009

    This simple idea really works !!

    Leave a reply
  6. avrelian
    July 1, 2009

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

    IMHO should be

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

    Leave a reply
  7. Junaid Atari
    July 9, 2009

    Great Information. Thanks!

    Leave a reply
  8. Kidmenot
    August 5, 2009

    Thanks , but could you tell me exactly where I would put the string to prevent an attack
    Thanks

    Leave a reply
  9. ryan
    August 5, 2009

    I found your article very good but you left me wondering about a few things. Why are you pulling all of the information from your SQL databases with select *? I also wanted to know if it is neccesary to put the mysql_real_escape_string everywhere the post command is even if a different function is running such as trim. Also what is the difference between mysql_realescape_string and mysql_escape_string?

    Leave a reply
  10. arvind
    September 2, 2009

    ‘ or ’1′=’1
    how to prevent sql injection when i am putting ‘ or ’1′=’1
    into url then data is coming so plz help me.

    Leave a reply
  11. read and learn
    October 10, 2009

    I use this line of code to do stuff with the current user’s ID number.

    $sql1 = “SELECT stuff FROM data WHERE id =’”.mysql_real_escape_string($idnumber).”‘”;

    take notice of all the ‘”. usages.
    also how to update stuff to 1 for that id number.

    mysql_query(“UPDATE data SET stuff = ‘”.mysql_real_escape_string(1).”‘ WHERE id = ‘”.mysql_real_escape_string($idnumber).”‘”);

    take a good look in the use of ‘”. signs.

    Leave a reply
  12. read and learn
    October 10, 2009

    the query has a few to many spaces (but didn’t type them in the message..)

    also if you want to learn php sql and how to learn the security, don’t buy the basic books, buy the advanced in depth books. I made that error also and have a book written by matt rutledge for creating my own php games and there’s no real information on the subject of “mysql_real_escape_string()” usages. I’m sorry matt, the book is rather helpful on other fields of game devellopment…

    Leave a reply
  13. Prakash Bhandari
    January 14, 2010

    Thanks for your article. It really works

    Leave a reply
  14. valentinstag karten
    January 31, 2010

    Very nice information.

    Leave a reply
  15. eika
    February 2, 2010

    hi all..

    I am a student and i am a new leaner. I would like to know how am i going to develop a coding to prevent sql injection attacks from scratch?

    Leave a reply
  16. Jon
    February 17, 2010

    Eika, If your page looks like this:

    $desc = $_POST['desc'];

    You would just do this to prevent the SQL injection:

    $desc = $_POST['desc'];
    $desc = mysql_real_escape_string($desc);

    and you’re set.

    Leave a reply
  17. Sushilkumar Shinde
    March 5, 2010

    Thanks for such a nice and understandable information about sql injection. Now i can protect my website from this attack.

    Leave a reply
  18. Randen
    March 9, 2010

    Here’s a good method to just clean out all POST variables right off the bat.

    Instead of using $_POST['var'], just use $clean_post['var'].

    if($_POST){
    $post_orig = $_POST;
    foreach($post_orig as $post_var){
    $var = key($post_orig);
    $clean_post[$var] = stripslashes(stripslashes(mysql_real_escape_string($post_var)));
    next($post_orig);
    }
    }
    print_r($clean_post);

    Leave a reply
  19. Rhett Phillips
    March 22, 2010

    Going off the last comment, here’s a quick method to cover all your bases in a pinch.

    function me($v){return mysql_real_escape_string($v);}
    if($_POST){foreach($_POST as $posts){$posts=me($posts);}}
    if($_GET){foreach($_GET as $gets){$gets=me($gets);}}

    Put these 3 lines on the same include page as your database class or wherever you connect to a mysql db, you’re all set. Remember three things:
    (1) This is not the most efficient method cause it has to work those strings every single time all the time.
    (2) The escaping only works if you are connected to mysql.
    (3) I only created a separate me() function for brevity and reuse on whatever.

    Leave a reply
  20. hacker
    March 22, 2010

    you can do it that way too
    $username=mysql_real_escape_string($_POST['user']);
    $SQL = “SELECT * FROM users WHERE username=’$username’ LIMIT 1″);
    it is safer that way

    Leave a reply
  21. Rich
    March 29, 2010

    Great post! I was just wondering, though: how does mysql_real_escape_string protect you against ;DROP TABLE user; #? You can limit to what users can put into text fields, but doesn’t do any good if your script uses ‘get’ and a hacker can just put it into the url. I think what I can do with my script is to get rid of spaces all together when validating user input, since users should not use spaces in my case, anyways ;p

    Leave a reply
  22. Robin
    April 9, 2010

    For everyone who’s interested in preventing SQL injection from user input, you might want to check our newly released opensource library ValidForm Builder.

    Some of the key features are:
    - Fully CSS and webstandards based forms (no tables)
    - Prevent SQL Injection using both clientside and serverside validation
    - Check http://www.validformbuilder.org/ for more information, tutorials, complete API reference guide and ofcourse the source download!

    This is no commercial; it’s a free to use opensource library for creating webforms.

    Leave a reply
  23. Chantal
    June 7, 2010

    Thanks for the great tutorial. I really appreciated the plain language and simplistic, easy-to-follow examples.

    Leave a reply
  24. Dyl
    June 15, 2010

    Yeah, I would REALLY want to know how to prevent the SQL Injection from the URL.

    Leave a reply
  25. Pheesh
    June 21, 2010

    to prevent sql injection from url you would do the same thing as for POST,just do it for GET when you’re getting the data from the url.
    //This stops SQL Injection in POST vars
    foreach ($_POST as $key => $value) {
    $_POST[$key] = mysql_real_escape_string($value);
    }

    //This stops SQL Injection in GET vars
    foreach ($_GET as $key => $value) {
    $_GET[$key] = mysql_real_escape_string($value);
    }

    Leave a reply
  26. Hydronly
    July 3, 2010

    Thank you very much for your explanation. It helped me out a lot.

    Leave a reply
  27. FYI
    August 1, 2010

    Hey people i found something thats helpful for get values
    you should also put a post if possible so hackers cant hack those

    Leave a reply
  28. Haq
    August 17, 2010

    Very good info to be shared with, I was wondering how to protect my website from sql injection. Now I have learned the real use of mysql_real_escape_string usage. Thank you very much.

    :)

    Leave a reply
Leave a Comment Below »
Your Name
Your Email Address
Your Comment