Is a mysql LIKE statement with an escaped string containing unescaped wildcards '%' (percent) or '_' (underscore) vulnerable?

Question

Let's say we have the following code (for some kind of search or similar):

$stmt = $pdo->prepare("SELECT * FROM users WHERE username LIKE ?");
$stmt->execute(array('%' . $username . '%'));

The username supplied is properly escaped, but the characters %(= 0 or more arbitrary characters) and _ (= exactly 1 arbitrary characters) get interpreted as a wildcard by MySQL.

I understand that users could enter the % or the _ for a search and I should escape it if I want the search function to work properly. (In cases like set_pt and getting setopt in the result).

But my question is: Could someone exploit this? If yes, how could someone exploit this and how to prevent it? Would the function below suffice?

function escape_like_string($str) {
  return str_replace(Array('%', '_'), Array('\%', '\_'), $str);
}

One possibility I could think of would be by entering tons of %, so the server would need to allocate a lot of memory. Would this work?

Answer 1

Could someone exploit this?

For SQL-injection? No.

For an easter-egg like behavior? Probably. In this case, if you don't want let your users use wildcards in this search, you can do 2 things:

1. proper escape wildcards (and the escape character),

   str_replace(array('\\', '%', '_'), array('\\\\', '\\%', '\\_'), $str);
   // or:
   str_replace(array('|', '%', '_'), array('||', '|%', '|_'), $str);
   // with SELECT * FROM users WHERE username LIKE ? ESCAPE '|'
   

2. or use LOCATE(substr, str) > 0 to find exact matches.