CODEWITHSUNDEEP
phpsql-injection
F.P
F.P

Reputation: 17831

How could this SQL injection protection be breached

This question is not about creating an actual alternative for the proven functions to prevent injections, but about how to argue with people that don't see the flaw in their homebrewed injection-prevention code!

I'm trying to make a point to a colleague but it seems his "solution" to SQL injection seems fairly safe to me.

He clears the query by doing

$query = $_POST['username'];
$look = array('&', '#', '<', '>', '"', '\'', '(', ')', '%');
$safe = array('&amp;', '&#35;', '&lt;', '&gt;', '&quot;', '&#39;', '&#40;', '&#41;', '&#37;');
str_replace($look, $safe, $query);

And then proceeds with the login

"SELECT * FROM users WHERE username = '" . $query . "'
    AND password = '" . md5($_POST['password']) . "'";

I am trying to get him to use PDO or equivalents, but how could you actually breach this protection? I don't have an answer and it's really bugging me because I can't explain him how this is unsafe and why it should not be done this way.

Upvotes: 3

Views: 168

Answers (3)

Your Common Sense
Your Common Sense

Reputation: 157880

This is actually a terrible way of "escaping" with a lot of pitfalls, and only few of them are

  1. This code is actually changing the data in the assumption that the only output medium would be HTML. From my 15 years experience I would say that it is not true. Altering your data is always a bad thing, it will lead to inconsistency and a lot of pain in the back in the future. One immediate of them is that such a "formatting" would be multiplied on every edit, making &ampampamp out of mere quote.
  2. Injections through numbers.
  3. Injections through identifiers.
  4. Second order injections.

...and much more.
Strict rules of this site prevents me from using proper words in naming such a "protection" but feel free to imagine them yourself.

Upvotes: 1

Colin M
Colin M

Reputation: 13348

While this is likely to cover most typical SQL injection issues - there are a couple of known issues using multi-byte character sets and certain locale settings on the server.

However, this isn't simply escaping - this is actually changing the data being input to the database. Consider simply adding slashes where appropriate, or using one of the built in escape methods such as mysqli_real_escape_string when inputting data, and htmlentities or the like when re-displaying it to the browser. This ensures that what you store in your database is always what the user actually entered - unless you have a reason not to.

Better yet, when in doubt, use prepared statements and bound parameters. Then you're safe from SQL injection entirely.

Upvotes: 2

Andy Lester
Andy Lester

Reputation: 93725

I suggest that the question of "can this approach be breached" is not relevant. The real question to ask is "Why would you use a homegrown ad hoc solution rather than one that has already been written, tested and debugged and is in use by thousands of users already?"

Upvotes: 2

Related Questions