Safely escaping a variable in a raw SQL query

Question

Im just wondering, would the following be completely safe or would someone be able to get around it using hexadecimal characters, etc:

$name = mysql_real_escape_string(htmlentities(stripslashes($_REQUEST['name'])));
$query ="SELECT * FROM Games WHERE name LIKE '%{$name}%'";

Thanks.

I know I can use PEAR and other libraries to make prepeared statements. However this question is specifically regaring raw queries.

Answer 1

mysql_real_escape_string does the trick,.,

[EDIT]

for string use:

$str = mysql_real_escape_string($input);

for numeric values type casting is sufficient like:

$val = (int)$input;

Answer 2

For the SQL just

$name = mysql_real_escape_string($_REQUEST['name']);
$query ="SELECT * FROM Games WHERE name LIKE '%$name%'";

is enough.

More of that, using of htmlentities and stripslashes here is unecessary and buggy.
I doubt you want to search Dankeschön instead of Dankeschön for example.

Note that mysql_real_escape_string obviously works for quoted strings only.

$num = mysql_real_escape_string($_REQUEST['num']);
$query ="SELECT * FROM Games WHERE salary > $num;

would not help

For the other uses there can be other validations.

Answer 3

Use parametrized prepared-statements, along with PDO, this will give You the most secure way of sending queries, that PHP can offer, and You'll not have to deal with escaping at all, using that way

• PDO
• Parametrized prepared-statements

Answer 4

mysql_real_escape_string is enough here and since you tagged it with xss, when reading it from the database and showing it in html use htmlentities to prevent xss.

Answer 5

Either use parameterized SQL or enforced input filtering:

$query = "SELECT * FROM Games WHERE name LIKE '%{$_REQUEST->sql['name']}%'";

Manual escaping is pretty bad style, because it's too easily forgotten.