If we cast a value is it still necessary to escape it with mysqli_real_escape_string()?

Question

real_escape() is a function which needs memory for its functionality, so I was wondering that is there any necessity to do that if we've already cast it?

$num = (int) $num;

can we use real_escape() just for string parameters?

Answer 1

It's true that casting can mitigate SQL injections, but unless performance is a huge concern for you, I'd use mysqli_real_escape_string() anyway, for two reasons:

1. Your application might change in the future. Even if your application is only passing integers into your queries right now (via this particular function call or whatever), your code may be modified in the future to accept strings, etc. I'd propose that you're better off planning ahead a bit, and that you should seal off this potential future security vulnerability right now, while it's on your mind.

2. A less experienced developer might "refactor" away that cast. I can see a junior dev coming through your code at some point and saying, "Why is this cast here? We're only passing IDs into the SQL anyway," and then refactoring away that cast. I'd recommend using mysqli_real_escape_string() even if only to give future developers a heads-up that there's a security issue at stake here.

At the very least, if you're going to rely on a cast to implement security, I'd leave some very conspicious ALL CAPS comments in your code to make it clear that that's what you're doing.

Answer 2

You are right. If you know it is an int (because you cast it to int), you do not need to call mysql_real_escape_string().

P.S. I'll go out on a limb and express an opinion. For most purposes, prepared statements are a better defense against SQL injection than manually applying escaping functions. (I'm sure there are PHP folks somewhere who will give me a hard time about this, or point out that some database systems on PHP don't support prepared statements.)

Answer 3

In this example - if you cast variable - there is no need to do any other sanitization. But remember that casting must be prepared on the PHP-level. Don't even think about casting in your SQL query!

Correct:

    mysql_query("SELECT * FROM users WHERE id=".(int)$_GET['id']) 

This one leads to Blind SQL Injection:

    mysql_query("SELECT * FROM users WHERE id=CAST(".$_GET['id']." AS UNSIGNED)")

?id=1 AS UNSIGNED) AND 1337=BENCHMARK(13371337,MD5('ouch!'))--