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,

Comments

Why a LIKE clause and not an exact comparison =?

Written by BoltClock

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.

Written by Joseph Mastey

Accepted Answer

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

Written by VoteyDisciple
This page was build to provide you fast access to the question and the direct accepted answer.
The content is written by members of the stackoverflow.com community.
It is licensed under cc-wiki