What are the methods to check user input and prevent mysql injection?

Question

"We should never trust user's input", this is what I read from somewhere on the web.

Currently I have a web form, they enter their username and emails. For username field, how to control, check and prevent mysql injection?? Currently, in my php script I only do this:

$username = mysql_real_escape_string($_POST['username']); // prevent mysql injection

I saw some tutorials, before the mysql_real_escape_string function, they include other functions like, htmlentities, etc (could not remember what it is, and I cant found it now, sigh)

Is this a must to include the so called "htmlentities" function before mysql_real_escape_string??

What is your method you usually use for checking user's input data?

Oh ya, some other functions:

stripslashes();
serialize();
urlencode();

Must i include those?

Answer 1

You're doing it right, as far as putting your data into the database is concerned. You're protected against SQL injection attacks.

htmlentities() and htmlspecialchars() aren't relevant to SQL injection attacks; they're relevant to XSS attacks, which is a whole other topic you should look into, and is a relevant issue if you're displaying user input back out to the web.

Answer 2

like @chaos said is right

you can also use database abstraction layers, like pdo that will escape the paramaters for you

Answer 3

You could also look at using prepared statements (I think equivalent to parameterized queries for SQL Server), which further reduces the attack surface.