What characters are NOT escaped with a mysqli prepared statement?

Question

Hey everyone,

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,

Answer 1

% 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.

Answer 2

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.

Answer 3

One who use LIKE to match a username IS wrong. Not escaping function.

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