CODEWITHSUNDEEP
phpsecuritymysqliprepared-statement
barfoon
barfoon

Reputation: 28177

What characters are NOT escaped with a mysqli prepared statement?

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;

Thanks,

Upvotes: 4

Views: 2136

Answers (4)

Your Common Sense
Your Common Sense

Reputation: 157885

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.

Upvotes: -1

SCC
SCC

Reputation: 509

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

Upvotes: -1

Tomalak
Tomalak

Reputation: 338248

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.

Upvotes: 1

VoteyDisciple
VoteyDisciple

Reputation: 37803

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

Upvotes: 8

Related Questions