Protection against SQL injection

Question

Does the following PHP MySQL statement protect against SQL Injection?

$strSQL = "SELECT * FROM Benutzer WHERE Benutzername = '".$Benutzer."' AND Password = '".md5($PW)."'";

The Variables $Benutzer and $PW are inputs from the User.

We're checking the username and password against common SQL Injection techniques:

' or 0=0 --, " or 0=0 --, or 0=0 --, ' or 0=0 #, " or 0=0 #, or 0=0 #, ' or 'x'='x, " or "x"="x, ') or ('x'='x, ' or 1=1--, " or 1=1--, or 1=1--, ' or a=a--, " or "a"="a, ') or ('a'='a, ") or ("a"="a, hi" or "a"="a, hi" or 1=1 --, hi' or 1=1 --, hi' or 'a'='a, hi') or ('a'='a and hi") or ("a"="a.

Am I missing something? Should I use a different method to protect against SQL injection?

Answer 1

Try using mysql_real_escape_string()

$strSQL = "SELECT * 
  FROM Benutzer 
  WHERE Benutzername = '".mysql_real_escape_string($Benutzer)."' 
  AND Passwort = '".mysql_real_escape_string(md5($PW))."'";

Make sure that when users register, you register using the same technique .. for example,

AND Passwort = '".mysql_real_escape_string(md5($PW))."'"

and

AND Passwort = '".md5(mysql_real_escape_string($PW))."'"

may produce different results depending upon the input text.

And yes, also add some "salt" to this password as well. For some ideas you do like this

sha1(md5($pass)):
sha1($salt.$pass):
sha1($pass.$salt):
sha1($username.$pass):
sha1($username.$pass.$salt):

Answer 2

I'd personally advise using an algorithm such as SHA1 over MD5, as there is a lower possibility of collisions. You should be salting your passwords too, otherwise one can use rainbow tables to crack your passwords.

Also, use mysql_real_escape_string() to escape things, don't simply do a search and replace for common injections.

Answer 3

If security is supposed to be as high as possible, you ought to use some other hashing method than md5 - like e.g. sha256. Also, use a password salt.

Answer 4

You may want to look into parameterized queries for querying the database. This eliminates SQL injection attacks.

I work primarily with postgreSQL, and the format for doing such a query would look something like this:

$query = 'select * from Benutzer where Benutzername = $1 and Passwort = $2';
$params = array($Benutzer, md5($PW));
$results = pg_query_params($query, $params);

Most databases have a function that will be similar to this funationality.

I hope this helps and good luck!

Kyle

Answer 5

Prepared statements are a fantastic invention.

http://www.php.net/manual/en/pdo.prepare.php and http://www.php.net/manual/en/mysqli.prepare.php for examples.

Answer 6

The security standards should be as high as possible.

If md5 got included in your design with nobody raising an eyebrow or spitting coffee on themselves, then you need to revisit the security issue with the client. If security is that critical they need to spring for a security specialist to join your team. Everybody says they want secure code. Few are willing to pay for it, so few actually get it.

Answer 7

Be sure to strip_tags any public content (or use your own html filter) to prevent XSS as well.