CODEWITHSUNDEEP
phpmysqlmysqlisql-injection
kanohn
kanohn

Reputation: 81

Mysqli Real Escape String Inside or Outside Query

I have a doubt about real escape string and security from sql injection..

Does have any difference if i put real escape string inside a query? like:

$ano = $_POST['ano'];
$SQL->query("DELETE from viewers WHERE ano = '".$SQL->real_escape_string($ano)."'");

or the correct method is doing like?

$ano = $SQL->real_escape_string($_POST['ano']);
$SQL->query("DELETE from viewers WHERE ano = '$ano'");

Ignore $SQL, its just a example.

Upvotes: 0

Views: 118

Answers (1)

tadman
tadman

Reputation: 211700

Both approaches are incorrect. The solution is, instead, to use prepared statements with placeholder values.

There is no other safe way to deal with data parameters.

In other words this query should look like:

$stmt = $SQL->prepare('DELETE from viewers WHERE ano=?'); // Single quotes prevent accidental interpolation/injection
$stmt->bind_param('s', $_POST['ano']); // Just use $_POST directly
$stmt->execute();

This is the safe way to do it. There is zero chance of a SQL injection vulnerability.

There are only rare occasions where placeholder values will not work, but you need to address those on a case-by-case basis to see the best approach.

Your code with manual escaping should be ugly, and it should be blindingly obvious that all values are escaped, that there's absolutely no chance of the wrong variable being used due to a dumb typo. It should also include a comment in the form of an explanation and/or apology as to why a prepared statement could not work.

If use placeholder values consistently and diligently you won't have any SQL injection bugs. These are some of the worst to deal with because they often manifest in unusual circumstances that you might not normally test for, and can be exploited by attackers. Even a single SQL injection bug is enough to bust an application wide open, so the risk here is extremely high.

Upvotes: 2

Related Questions