Mysqli_real_escape_string with Single Quotes - Is it Safe? [duplicate]

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
– Don't Panic, Commented Jul 7, 2016 at 23:19
@Don'tPanic - I would like to agree with your logic. Any proof you could point me to? — William F
– William F, Commented Jul 7, 2016 at 23:22
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
– Don't Panic, Commented Jul 7, 2016 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
– William F, Commented Jul 7, 2016 at 23:39

Your Common Sense · Accepted Answer · 2016-07-08 04:11:01Z

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]);

Collectives™ on Stack Overflow

Mysqli_real_escape_string with Single Quotes - Is it Safe? [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related