MySQL injection protection and vulnerability signs using PHP

Question

What are the best ways to protect from MySQL injection? What are weaknesses I should look out for?

I know what it is, but I really have no idea how vulnerable I might be. Though I have taken (what I think to be) steps toward protecting myself and my database.

Is there any sure-fire way of stopping someone?

BTW...I write in PHP:)

Answer 1

You have to sanitize all input. How you can do this depends on the programming languaguage and/or framework you are working with.

edit:

If you are using php the function you are looking for is mysql_real_escape_string($string). You should use that on everything you receive from the client that should go in the database.

Answer 2

Use prepared statements instead of mixing the statement and the actual payload data.

see

• http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html
• PDO::prepare
• mysqli::prepare

You might also be interested in http://shiflett.org/articles/sql-injection and http://shiflett.org/blog/2007/sep/the-unexpected-sql-injection

Answer 3

If you're not using a framework that provides you with sanitizing tools PHP has a built in string escaper, you should start there. You can find the documentation on that within the PHP docs for mysql real escape string. If you look at example three you'll get a good idea of the basics you can follow.

Another method I follow is to make sure I cast variables where appropriate. For example if I'm expecting input from a user to be an integer I'll do the following:

$age = (int)$age;

Also if a column is supposed to be limited to one or two values (for example a gender column) make sure you enforce that in your PHP before putting it into the database.

Answer 4

A sign that you could have a problem would be taking user input directly and put it into your SQL command.

For example you ask for their username. If you take it and then simply say

"Select * From Users Where Username = '$USERNAME';"

The user could then add "JOE'; Drop Table..." and so on.

In perl you can say something like

my $sth2 = $dbh->prepare("Insert Into HostList (HostName,PollTime,Status) values (?,?,?);");
$sth2->execute($Hostname,$myDate,$Status);

The execute method would then look for exploits such as the one above and escape it properly.

Answer 5

I use this PHP function on all input before I try to use it in any code (MySQL query, data display, etc.). It probably isn't complete, but it should stop all basic attempts at hacking the system:

//$linkID is the link ID of the connection to the MySQL database
function clean_input($input)
{
    GLOBAL $linkID;
    if(get_magic_quotes_gpc())
    {
        //Remove slashes that were used to escape characters in post.
        $input = stripslashes($input);
    }
    //Remove ALL HTML tags to prevent XSS and abuse of the system.
    $input = strip_tags($input);
    //Escape the string for insertion into a MySQL query, and return it.
    return mysql_real_escape_string($input,$linkID);
}

Answer 6

Trust no one!

Sanitize all input -- filter_var() or regexes or in_array() of valid values or a mixed strategy depending on datatype.

"Input" means any source of input that you don't directly control -- not just forms!

Sanitize anything you get back from $_GET, $_POST, $_SESSION, $_COOKIE -- anything that could have any possibility of being tainted.

AND

Use prepared statements

Answer 7

This may seem like commonsense, but I was tripped up on it for a while.

There is a difference between encoding htmlentities() and escaping mysql_real_escape_string(). I was thinking of them as fairly interchangeable. However there not... as commonsense will tell you. :) Usually it's best to apply them both, such as first encode, then escape.

Then when pulling the data out reverse the process, unescape(if needed) then unencode. Note being specific in the way the steps are performed (and reversed) will save a lot of headaches and double-escaping woes.

Answer 8

All the other answers in this thread generally suggest two main ways of tackling SQL injection; blacklisting and whitelisting.

As you most likely know, blacklisting is having a list of characters that should be banned, and whitelisting is having a list of characters that should be allowed. Whitelisting is generally more secure because one may have to keep updating a blacklist, while a whitelist would change much less frequently, if at all.

One simple example of whitelisting, already given in this thread, is casting variables to integers, assuming the variable should be an integer. Dangerous, and not dangerous, strings would be caught.

What I suggest is writing your own code, perhaps including regular expressions, that checks the data with a whitelist. Though, input that could be filled with a very diverse array of characters might make a blacklist somewhat easier.

In summary, whitelist when you can, and blacklist when whitelisting would be too complicated. Functions, like mysql_real_escape_string (), and other things that were mentioned in other answers, can help you with the specifics.