4

I'm trying to harden some of my PHP code and use mysqli prepared statements to better validate user input and prevent injection attacks.

I switched away from mysqli_real_escape_string as it does not escape % and _. However, when I create my query as a mysqli prepared statement, the same flaw is still present. The query pulls a users salt value based on their username. I'd do something similar for passwords and other lookups.

Code:

$db = new sitedatalayer();

if ($stmt = $db->_conn->prepare("SELECT `salt` FROM admins WHERE `username` LIKE ? LIMIT 1")) {

  $stmt->bind_param('s', $username);
  $stmt->execute();
  $stmt->bind_result($salt);


  while ($stmt->fetch()) { 
    printf("%s\n", $salt);
  } 

  $stmt->close();    

}

else return false;
  • Am I composing the statement correctly?
  • If I am what other characters need to be examined? What other flaws are there?
  • What is best practice for doing these types of selects?

Thanks,

barfoon
  • 27,481
  • 26
  • 92
  • 138
  • 2
    Why a `LIKE` clause and not an exact comparison `=`? – BoltClock Jun 14 '10 at 16:05
  • Ignoring the LIKE-hating, as far as "injection" goes, escaping out of the var is impossible with bind variables (as long as you use them exclusively), so injecting other queries is prevented with use of mysqli. Others have covered the semantic issues on LIKE. – Joe Mastey Jun 14 '10 at 16:09

4 Answers4

8

% is not an inherently harmful character.

The question is: why are you using a LIKE in the first place? Are there any circumstances in which you wouldn't require an exact match for username?

The query should be simply:

SELECT `salt` FROM admins WHERE `username` = ? LIMIT 1

In that case, if I were to enter %bsmith my username would have to be (literally) "%bsmith" in order for you to find a match.

VoteyDisciple
  • 37,319
  • 5
  • 97
  • 97
  • Thanks - I tried using an = sign earlier and my query didnt work. Must have had an error somewhere else. Works now. – barfoon Jun 14 '10 at 16:10
1

You are confusing two different levels of evaluation here.

The LIKE operator takes a string and evaluates any '%' and '_' as placeholders.

The job of query parameters is it only to bring values (e.g. strings) verbatim to the database engine, so they cannot be mistaken for SQL code. They don't care how the LIKE operator makes special use of certain characters within the string they've just transported. Everything just works as designed here.

If you want exact matches, use the = operator in place of LIKE.

If you must use LIKE (even though your LIMIT 1 indicates otherwise here), escape the the special characters accordingly yourself beforehand.

Tomalak
  • 332,285
  • 67
  • 532
  • 628
  • If they are placed verbatim, then why use prepared statements and not just insert $username right into a string? – barfoon Jun 14 '10 at 16:09
  • @barfoon: As I said, parameters work on the "SQL string literal" level. They make sure string literals do not break. `LIKE` works one abstraction level higher, because it interprets the string *contents* according to its own rules. Parameters play no part here anymore, all they do is making sure your SQL statement does not break. – Tomalak Jun 14 '10 at 16:12
-1

These are the characters not escaping by prepared statements % _ \

SCC
  • 509
  • 7
  • 13
-1

It is one who is using LIKE to match a username to blame, not escaping function.

And, just for your info: native prepared statements do not escape anything.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • ADODB and PDO for PHP uses mysql_real_escape_string(), if you don't believe me, look at the code, i have. I should give you a -1, but i think i have given you enough of 'em :p – rook Jun 14 '10 at 16:20
  • Okay well i still care about incorrect statements `prepared statements do not escape anything.` – rook Jun 14 '10 at 16:52
  • for the record i didn't give you the -1. – rook Jun 14 '10 at 17:41