使用LIKE语句执行时PDO的疯狂行为

Question

I got code something like that:

$stmt = $pdo->prepare('SELECT * FROM table WHERE user LIKE :user');

and later I got

$stmt->bindValue(':user', '%'.$user.'%', PDO::PARAM_STR);
$stmt->execute();

And it do not work. I am pretty sure that this is correct way to do a LIKE statement with MySQL, but It not works when I enter some part of username, but when I enter full username it goes like a charm. Any ideas why LIKE statement don't want to do a simple regex?

Answer 1

What I use in my own code for LIKE queries is:

$stmt = $pdo->prepare('SELECT * FROM table WHERE user LIKE CONCAT(\'%\', :user, \'%\')');

$stmt->bindValue(':user', $user, PDO::PARAM_STR);
$stmt->execute();

using the CONCAT force MySQL to generate the comparison string after the $user variable has been escaped by PDO.