Preventing SQL Injection with MySQL and PHP

Most new web developers have heard of SQL injection attacks, but not very many know that it is fairly easy to prevent an attacker from gaining access to your data by filtering out the vulnerabilities using MySQL extensions found in PHP.

An SQL injection attack occurs when a hacker or cracker (a malicious hacker) attempts to dump the data in a database table in a database-driven web site. In an unprotected and vulnerable site, this is pretty easy to do.

In order for an SQL injection attack to work, the site must use an unprotected SQL query that utilizes data submitted by a user to lookup something in a database table. The data could be from a search box, a login form or any type of query used to look up data using data input by user. It also means that querystring data used to query a database can create vulnerabilities.

For example:

An very simple unprotected query might look like this:

SELECT * FROM items WHERE itemID = '$itemID' 

Normally, you would expect a user to submit a username and password, which would be used to query the database table to see if the username and password exists. But what if someone used the following instead of a password?

‘ OR ’1′ = ’1

That would make the query used to look for the password look like this:

SELECT * FROM items WHERE itemID = '' OR  '1' = '1'

This would always return a True response and could literally display the entire table as the result for the query. This is a pretty scary thought if you are trying to keep your data secure. The problem with SQL injection is that a hacker does not have to know anything about your database or table structure.

What if an error or some other issue caused your table structure to be exposed? Hackers are very good at forcing errors to occur that expose information that allows them to penetrate a site deeper. What if the following was entered in the password field?

‘; drop table users;

There is a method for filtering the data that is used on the right side of the WHERE clause to look up a row in a database. The trick is to escape any characters that may be in the user input portion of the query that could lead to a successful attack.

Use the following function to add backslashes to suspect characters and filter any data that is input by a user.

function cleanQuery($string)
{
  if(get_magic_quotes_gpc())  // prevents duplicate backslashes
  {
    $string = stripslashes($string);
  }
  if (phpversion() >= '4.3.0') 
  {
    $string = mysql_real_escape_string($string);
  }
  else
  {
    $string = mysql_escape_string($string);
  }
  return $string;  
}

// if you are using form data, use the function like this:
if (isset($_POST['itemID'])) $itemID = cleanQuery($_POST['itemID']);

// you can also filter the data as part of your query:
SELECT * FROM items WHERE itemID = '".  cleanQuery($itemID)."' " 

The first part looks to see if magic quotes is turned on. if so, it may have already added backslash escapes though a POST or GET method used to pass the data. If backslashes were added, they need to be removed prior to running it through the rest of the function.

The next part checks the PHP version. The built-in function that we want to use is called mysql_real_escape_string. This MySQL function only exists in PHP version 4.3.0 or newer. If you are using an older version of PHP, another MySQL function is used called mysql_escape_string.

mysql_escape_string is not as effective as the newer mysql_real_escape_string. The newer version escapes the string according to the current character set. The character set is ignored by mysql_escape_string, which can leave some vulnerabilities ope for sophisticated hackers. If you find that you are using an older version of PHP and you are trying to protect sensitive data, you really should upgrade to a current version of either PHP 4 or PHP 5.

So what does mysql_real_escape_string do?

This PHP library function prepends backslashes to the following characters: \n, \r, \, \x00, \x1a, ‘ and “. The important part is that the single and double quotes are escaped, because these are the characters most likely to open up vulnerabilities.

For those who do not know what an escape is, it is a character that is pre-pended to another character. When a character is escaped, it is ignored by the database. In other words, it makes that character ineffective in a query. In the case of PHP, an escaped character is treated differently by the PHP parser. The standard escape character used by PHP and MySQL is the backslash.

In the case of the SQL query example used above, after running it through the routine, it now looks like this, which breaks the query :

SELECT * FROM items WHERE itemID = '\' OR \'1\' = \'1' 

This method should stop the bulk of the SQL injection attacks, but crackers and hackers are very creative and are always finding new methods to break into systems. There are additional steps that can be taken to filter out certain words, such as drop, grant, union, etc., but using this method will strip these words from searches performed by you users. However, if you want to add another level of security and do not have an issue with certain words being deleted from queries, you can add the following just before if (phpversion() >= ’4.3.0′).

$badWords = array("/delete/i", "/update/i","/union/i","/insert/i","/drop/i","/http/i","/--/i");
$string = preg_replace($badWords, "", $string);

This additional step should prevent a malicious attacker from damaging a database if they found a way to slip through. Just remember that is you take this additional step and you have a site where someone might search for a “plumbing union” or a “drop cloth”, those queries would not work as intended. If you are wondering what the trailing ‘i’ is following each word in the array, it is required to make the preg_replace replacements case insensitive. This wasn’t needed with eregi_replace, but that function has been deprecated in PHP 5.3.

Another important step that needs to be taken with any database is controlling user privileges. When setting up a MySQL user, you should never assign any more privileges than they actually need to accomplish the tasks that you allow on your site. Privaleges are easily assigned and managed thought phpMyAdmin, which is found in the the control panel (cPanel, Plesk, etc.) for most hosting companies.

Comments

  1. TE says

    I can’t find a definitive answer on that one. If you have a link to a resource, please post it.

    SQL injection is more of a database issue than a PHP issue. Current versions of MySQL do prevent some types of multiple queries from being executed, which can stop a few types of SQL injection attacks.

    I suspect that PHP 5 does not resolve the issue because SQLite, the new database bundled with PHP 5, is already gaining notoriety as a database that may be particularly vulnerable to injection attacks.

    I’m running a server with PHP 5, but I will still use the cleanQuery function until someone proves to me that I don’t need it.

  2. Kuro says

    How about trying $string = addcslashes($string,’;%_:$&?-+=*[]()¡””´`’); ?

  3. TE says

    Hi Kuro

    You can escape any characters that you want, but most of those characters have nothing to do with SQL injection. MySQL recommends using mysql_real_escape_string to protect against SQL injection. You can do anything you want beyond that to protect against cross-site scripting or other vulnerabilities. mysql_real_escape_string just focuses on SQL injection.

  4. TE says

    I think you may misunderstand the SQL injection issues.

    strip_tags doesn’t do anything to prevent SQL injection. SQL injection requires SQL commands, which strip_tags does not remove. strip_tags is a PHP function that just removes HTML tags.

    In most cases, mysql_real_escape is all that is needed. That is what is recommended by MySQL.

  5. TW says

    TE: I’m not sure I agree with “SQL injection is more of a database issue than a PHP issue.”
    SQL injection is all about lack of/poor validation of user provided data. There is no way an sql database can know that the sql statement it receives is from a good piece of code or from a malicious user. All it can check is syntax. It is entirely up to the code (in this case PHP) to make sure it only passes the SQL statements it wants run to the database.

  6. TE says

    Hi TW

    I agree with you, but you have to put my statement into context. The user made a comment about the SQL injection issue being fixed in PHP5. I don’t think that PHP itself can automatically fix the problem. You still have to filter any user input using PHP, but mysql_real_escape_string is a MySQL function executed through PHP. mysql_real_escape_string is a MySQL library function that doesn’t exist unless MySQL is installed.

    That’s why I referred to it as a database issue. The MySQL team provides the recommended solution.

  7. says

    Injection is an SQL problem, the closure for commands to a DB should have never been made to be determined by a String which could be mixed with data intended to be entered into a DB. The language is flawed.

  8. Mat says

    Thank you,

    This has helped allot. Althou i already used the msql injection to avoid that as i am using latest php 5 version i now got words filtered aswell.

    Cheers!

    Great info and great site.

  9. demon says

    Nice info. How do you think about using ctype_alnum() to prevent anything but alphanumeric from being submitted, and ctype_digit() to allow numerical input only?

  10. Doogie says

    Hi demon

    They can be useful for validating user input using PHP, but they do not have anything to do with SQL injection.

    There is also ctype_alpha(), which validates a string for alphabetic characters only.

    I think we need to do an article explaining how to use these functions. Thanks for the info. The code monkeys reading this will find it to be useful. :)

  11. Paul says

    hey thanks for the great source code, I’ve been developing web for about a 3/2 years now and until recently I never took any heave to the security issue. It’s only recent that I started learning and your article really lightened me up on some parts. Thanks again :)

  12. boaz says

    why not use stored procedures in addition to escaping characters? that way your scripts are safe, and you have full control over what the script does.

  13. Doogie says

    Hi boaz

    Stored procedures can be a good idea and add another layer of security, but this article only covers SQL injection. Stored procedures work with MySQL 5.0 and newer versions of MySQL, so they are fairly new to MySQL users, but are commonly used with other, older database systems. Quite frankly, I have not seen any MySQL applications using them thus far. I know that some developers think that it adds more complexity to the code, and thus intentionally avoid using them unless it is absolutely required for security.

    I’ve added MySQL Stored Procedures to a series of upcoming security articles. Thanks for the tip. :D

  14. boaz says

    Hi Doogie,
    Stored procedures are indeed an advanced feature of database development. I look forward to reading your articles on them. Is there a way I could add myself to an emailing list of yours?

    Thanks! :)

  15. Doogie says

    Hi boaz

    We do not use an e-mail list, but we do use Twitter. A tweet goes out to followers with each new article.

  16. Arnor Baldvinsson says

    Thanks for the article! I just want to say that SQL Injections have nothing to do with the programming langue that constructs the queries. It has to do with the developer writing the programming code to do his or her job and make sure that he or she does not allow malicious SQL statements through to the back end. It’s a question of string parsing and the language can certainly help with that, but the responsibility is with the developer who writes the code to try to prevent injections going through to the backend:)

    Stored procedures do not really help since you will strill need to pass information to the sp so it can do it’s job. If you are passing a filter to it which includes “;DROP TABLE USERS” or whatever the syntax is, and the sp takes the filter straight into a WHERE statement it will not help at all with the injection. Of course it always depends on the context and if all you are sending into the sp is a numerical system ID number, then checking that it is indeed all numberical characters will prevent it from doing any bad stuff. But ultimately it is up to us to make sure that the injection doesn’t reach the back end:)

    Best regards,

  17. Elizabeth says

    Hi, Although I have been programming for several years, I am new to php. I am looking into the security of websites that use forms and databases for user interaction. I have been searching online and have found many different suggestions to help secure my site against mysql injection and xss. I have used your above code to help in cleaning out any unwanted characters within my sql statements, what I am wondering is there any way to test a webpage to see if it passes an injection. Since I am no way as good as a those who do these bad things I would find it helpful if there was a way to test my pages to make sure that they are at least fairly secure. Do you have any suggestions? And thank you for the above function, it saved me the time from writing my own.

  18. Doogie says

    Hi Elizabeth

    New and creative ways to hack sites are always emerging. The basic test is the one mentioned in the article. Many sites will fail that simple test.

    You can do a search for “SQL injection test” and you will find several methods to test for vulnerability.

    The definitive source for information appears to be OWASP.org. You will find links to several excellent articles on that page that cover a range of server-side languages.

    SQL injection can be prevented simply by blocking certain characters that are required for an attack. That is what mysql_real_escape_string does. That function was developed by the MySQL team.

  19. says

    Great little guide here.

    I always use mysql_real_escape_string, ctype_alpha, or intval to stop the bad boys in their tracks.

    Remember to validate any user submited data on output aswell, just incase they get past the first hurdle!

  20. says

    A nice and simple solution, I also recommend checking yoiur log files from time to time to see if there have been any possible hack attempts, you can often learn a thing or two by seeing how they go about compromising a website.

  21. accoh says

    What if somebody will use an injection string containing something like the following:

    “; ddroprdropodropp table users”

    Once used the preg_replace function suggested, it will indeed remain “; drop table users”…

    Anyway, thank you so much for the explanation but I think that the suggested method, could also be weak once known…

    For people that just use copy and paste function, please consider also this.

    Best regards.

  22. Conrad says

    Hi accob

    That might be a valid workaround except for the fact that the entire string would be surrounded by single quotes in the query, which means that the query would search for the resulting string and not execute it. I think the single quote needs to slip through in order for one query statement to end and another to begin.

    But it does demonstrate the need to keep your table data secure. It is also a good idea to use a minimalist approach to applying permissions to a MySQL user. If the database user does not need to be able to drop a table, then that permission should not be granted.

  23. says

    Hi, great post, helped me to figure out my own code for this problem (code posted below). I’ve also added the ‘htmlentities’ into my/your code. I know it does nothing for strictly SQL injections, but it should be included to prevent anyone from running malicious code on your website/s – so I guess my code would come under the umbrella term of ‘user form submission security’ rather than ‘sql injection security’. Hope you guys like it!

    function secure_string($string){
    if(get_magic_quotes_gpc()) // prevents duplicate backslashes
    {
    $string = stripslashes($string);
    }
    if (phpversion() >= ’4.3.0′) //if using new version of PHP and mysql_real_escape_string
    {
    $string = mysql_real_escape_string(htmlentities($string, ENT_QUOTES));
    }
    else //for the old version of PHP and mysql_escape_string
    {
    $string = mysql_escape_string(htmlentities($string, ENT_QUOTES));
    }
    return $string; //return the secure string
    }

  24. James Stoddern says

    Hi

    Thanks for a very useful article. There are clearly a few discussions going on here, and even though some of them do not relate specifically to sql-injection, they are good points to make about general security for web based applications. One of my companie’s websites got hit a few years ago and had javscript injected into many columns. Since then I have ensured that treatments much like the one you suggest above, and checks to see whether numeric values are actually numeric, are in place on all my sites.

    As Arnor correctly points out, it is OUR job as developers to ensure that me mitigate these risks. People such as the MySQL Team can certainly help us to make this easier for us by providing great functions like ‘mysql_real_escape_string’. Irrespective of what tools are around, article such as yours will help educate developers and show them the potential risks. I thankyou for your contribution.

  25. says

    Hi,
    in addition , you should use parametric query and not exposing the query to the variables directly,
    you should also strip tags.

    To prevent database hacking , http.conf of apache should be configured properly , otherwise there are ways to exploit your server …

    best luck .

  26. says

    As I am reviewing SQL injection, this article helps very much. Thanks for the insight. We also need to remember that it is also important to remember to safeguard the data coming out of SQL as well.

  27. says

    Thanks for this useful info!

    I always set very restrictive permissions for MySQL users and by doing so I can eliminate most of the worst consequences of injection attacks.

    Of course one should filter all user submitted input to the database as a matter of course, but at least by also setting restrictive user permissions you have already limited the effect of an attack if you have any unforeseen vulnerabilities.

  28. Aadil says

    Hi there…

    Thanx for this.

    Please advise how I could go about testing if this is actually working.

    Thanx again.

  29. Steve says

    Wouldn’t it also be good to have “select” in your list of bad words? Kill the string stone dead.

  30. Doogie says

    Hi Steve

    You can always add it if you want. We focused on the list of words that can lead to database damage.

  31. matt says

    @Tim – storing userdate with htmlentities parsed is STUPID. You should parse for html-entities on data output, and just store the raw data.

    The issue with html entities is to prevent XSS attacks, and is a presentation layer issue.

  32. says

    Hey guys (and gals) what about PHP using MS SQL. Is there a “mysql_real_escape_string” type string for use with MS SQL server backend? I’m getting hacked all the time! :(

  33. Tibob says

    Are the “bad words” case-sensitive? What if someone tries to inject, for example, UPDATE or Update instead of update? Do I have to add an uppercase version of each of the bad words to the list?
    Thanks for the article, by the way?

  34. Doogie says

    Hi Tibob

    You missed the details in the explanation. “If you are wondering what the trailing ‘i’ is following each word in the array, it is required to make the preg_replace replacements case insensitive.” So if you do it that way, the bad words are not case sensitive.

Trackbacks