Mysqli_real_escape_string with Single Quotes - Is it Safe?

Question

So I know that using prepared statements with placeholders is pretty much the only way to protect yourself from SQL injection due to poor formatting of your queries. However, I also see many people suggesting that, although mysqli_real_escape_string is NOT safe, using it with single quotes around the variable is. For example (note the single quotes in the query):

$value1 = mysqli_real_escape_string($value1);
$value2 = mysqli_real_escape_string($value2);
$value3 = mysqli_real_escape_string($value3);

mysqli_query("INSERT INTO table (column1, column2, column3)
  VALUES ('" . $value1 . "', '" . $value2 . "', '" . $value3 . "')";

So: when only dealing with integers and strings, would the above example be just as safe as if you were to use mysqli prepared statements and placeholders?

No, regardless of which data types you're dealing with, _not_ using prepared statements will _never_ be as safe as using them. — Don't Panic, Jul 07 '16 at 23:19
@Don'tPanic - I would like to agree with your logic. Any proof you could point me to? — William F, Jul 07 '16 at 23:22
http://stackoverflow.com/questions/8263371/how-can-prepared-statements-protect-from-sql-injection-attacks — Don't Panic, Jul 07 '16 at 23:26
Basically, parameters bound to a prepared statement will inherently be interpreted as data. There isn't really a way to force them to be interpreted as commands instead. — Don't Panic, Jul 07 '16 at 23:28
And indeed that explains why prepared statements are safe. But my question is whether or not this specific use of `mysql_real_escape_string` is also safe because it properly formats any possible string or integer. — William F, Jul 07 '16 at 23:39
Sorry, I must have misunderstood. I thought you were specifically asking if using `mysqli_real_escape_string` in that way would be as safe as using a prepared statement. — Don't Panic, Jul 07 '16 at 23:46

Your Common Sense · Accepted Answer · 2016-07-08T04:11:01.393

It is not mysqli_real_escape_string that is not safe, but the way PHP users tend to use it.

As long as you are setting a character set with set_charset() and always wrapping your escaped values in single quotes, despite the type, then technically the statement above would be safe.

However, as you can see, there are too much rules to follow - a condition that is too complex for an average PHP user. Therefore, this kind of manual formatting is an endless source of injections, simply because any rule (escaping, quoting, setting a charset) could be just forgotten to apply.

Besides, manual escaping just makes your code bloated. Why not to let a program to process your data properly for you?

This is why you have to have a mechanism to apply all the rules automatically. So prepared statements is such a mechanism.

What's wrong with a simple function like this, any reason you want to prefer your code to it:

$sql = "INSERT INTO table (column1, column2, column3) VALUES (?,?,?)";
some_query($sql, [$value1,$value2,$value3]);

Mysqli_real_escape_string with Single Quotes - Is it Safe?

1 Answers1