MySQL Injection Prevention

SuisydeKing

I'm trying to prevent my login script from being vulnerable to MySQL injection attacks. I'm wondering the following:

A) What other validation should I be performing besides removing "bad" characters and checking the string lengths?

I know a preg will be quicker for replacing strings, I just wanted to illustrate all that I have done here for quicker analysis.

if (($_POST['username']) && ($_POST['password'])) {

$username = str_replace(';','', $_POST['username']);
$username = str_replace('\'','', $username);
$username = str_replace('"','', $username);
$username = str_replace('>','', $username);
$username = str_replace('<','', $username);

$password = str_replace(';','', $_POST['password']);
$password = str_replace('\'','', $password);
$password = str_replace('"','', $password);
$password = str_replace('>','', $password);
$password = str_replace('<','', $password);

if (strlen($username)>10 || strlen($password)>10) {
echo "invalid login";

} else {

$query = mysql_query("select id from sample_table where username = '$username' and password = '$password'");
if (mysql_num_rows($query)) {
// start a session
// header location to members.php page
exit();
} else {
echo "invalid login";
} } }

I have found many threads on this but most vary in answers and opinons. I guess I'm really looking for a "checklist" of things that need validated before the MySQL query is run. Any help would be greatly appreciated.

pete_bisby

Using preg would be quicker - all you need to allow for username and password would be a-z, A-Z, 0-9. Any other characters should be regarded as a fail.

MarkR

No, none of this is necessary. You just need to use mysql_real_escape_string on your final string.

People should be allowed to put non-alphanumeric characters in their password.

As a side note, you should probably select the password from the database, then compare it with ==, rather than letting the database compare it, as that way you can make it case sensitive despite the database's collation.

Mark

pete_bisby

So you would allow non-alphanumeric characters like = to be accepted ...

.... as in OR 1=1 .... ????

After the hacking and SQL Injection courses I've been on, I would strongly advise accepting alpha-numeric characters only.

Additionally, hash/encrypt the password and store this in the database when the user account is created. Then you can compare the hashed/encrypted version.

MarkR

It's perfectly safe to allow OR 1=1 in the username, provided they cannot "break out" of the string by using unescaped quotes.

The mysql_real_escape_string function will ensure that.

However, in my applications I never use mysql_real_escape_string or any other SQL escaping functions, I just use parameterised queries (e.g. in PDO), which fixes it automatically.

Mark

pete_bisby

The function may ensure that breaking out will not occur, but there is a vulnerability associated with it (http://secunia.com/advisories/20365).

I do agree with parameterised queries, though. But if you are using the mysql_real_escape_string function, however, make sure your version of MySQL is up to date.