Is the mysql LIKE statement with escaped string containing unprotected wildcards "%" (percent) or "_" (underscore) vulnerable?

Question

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

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

The username you entered is correctly escaped, but % (= 0 or more arbitrary characters) and _ (= exactly 1 arbitrary characters) are interpreted as a MySQL wildcard.

I understand that users can enter % or _ to search, and I should avoid it if I want the search function to work correctly. (In cases like set_pt and getting setopt as a result).

But my question is: could anyone use this? If so, how can someone use this and how to prevent it? Could the function be lower?

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

One of the possibilities that I could think of is to enter tons of % , so the server will need to allocate a lot of memory. Will this work?

Answer 1

Can anyone take advantage of this?

For SQL injection? No.

For an easter egg, how is the behavior? Probably. In this case, if you do not want your users to use wildcards in this search, you can do 2 things:

• valid escape masks (and escape character),

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

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