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:)
Use prepared statements instead of mixing the statement and the actual payload data.
see
You might also be interested in http://shiflett.org/articles/sql-injection and http://shiflett.org/blog/2007/sep/the-unexpected-sql-injection
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
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.
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.
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.
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.
After some experiments I was create two functions for total clean SQL injections. In pair with prepared statements it works perfect.
// Cleanup outer SQL
protected static function escapeOuter( string $s ): string {
return preg_replace(
['/(\/\*\s*\w*\s*(?!.\/\*))/si', '/(\-\-\s*\w*\s*(?!.\-\-))/si', '/(or\s*\w*\s*=\s*\w(?!.*or)|\|\|\s*\w*\s*=\s*\w(?!.\|\|))/si'],
[';', ';', ''],
str_replace(
[ '+--', '--+', '"', "\x1a", '%', 'qq ', '--', '/*!',],
[ '', ';', '"', '\\Z', "\%", '--', '/*!'],
trim( $s )
)
);
}
// Cleanup inner SQL
protected static function innerEscape( string $v ): string {
// Secure stage means that inner SQL clauses fixed to be secure
$secureStage = str_ireplace(
['ASCII', 'UNION', ' OR ', '||', ' AND ', '&&', ' ON ', "'", '+--', '--+', 'qq', '"', '--', '/*!', ],
['', '', ' or ', ' || ', ' and ', ' && ', ' on ', '\'', '', ';', '', '"', '--', '/*!'],
addslashes(
htmlspecialchars( $v )
)
);
// Not available to use built in escape future when DB connection not established
if( isset( self::$dbx_lnk[ 1 ] ) ) {
if( (bool)self::$dbx_lnk[ 1 ]['CONNECTION'] ) {
return mysqli_real_escape_string( self::$dbx_lnk[ 0 ], $secureStage );
}
else {
return $secureStage;
}
}
else {
return $secureStage;
}
}
This functions is a part of my own DataBase driver.
For example you have to use innerEscape future for clean fields values and escapeOuter future to cleanup full MySQL query. After filtering you have to use prepared statement of PDO or MySQLi.
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);
}
