When to use mysqli_real_escape_string?

Question

Possible Duplicate:
Best way to prevent SQL Injection in PHP

I am wondering about sql injection and want to know when to use mysqli_real_escape_string.

Do I need to use it every time I have a WHERE clause in my query?

Solution

You should use mysqli_real_escape_string for any data that comes from the user or can't be trusted.

Comments

[jaypabs]

hi, i am actually using prepared statement on my other script but since i have ported most of my script from mysql to mysqli i have a lot of sql statement that is using a traditional programming. so instead of using prepared statement, i decided to use mysqli_query instead of modifying all my code to use prepared statement. so for the meantime is it safe to use code like: mysqli_real_escape_string($_POST['username'])?

[jaypabs]

you mean i have to use mysqli_real_escape_string even in the value of the update statement like: $query = "UPDATE foo set bar = mysqli_real_escape_string($_GET['var']) ..." and also in WHERE clause like: $query = "SELECT bla from foo WHERE bar = mysqli_real_escape_string($_COOKIE['var'])"?

[David Barker]

@jaypabs yes you can use it like that, and it will provide a level of security, however it doesn't escape strings that contain special chars: % and _ that are used in LIKE clauses. As they are usually contained within double quotes in the query they are still a risk. (see ammended info in my answer)

[jaypabs]

does this mean that we really have to use prepared statement? what happened to mysqli_query? is mysqli_query already useless?

[David Barker]

Prepared statements provide you with a much more robust defense than mysqli or mysql could ever do out of the box. Managing all user inputted data in the fashion described above gets very tedious indeed. And, I wouldn't say mysqli was dead, but it is certainly the least preferred choice.

[David Barker]

Worth mentioning that whether you're using PDO or not you should also always use 'htmlspecialchars()` as well (php.net/manual/en/function.htmlspecialchars.php) when inputting into a db