Is a mysql LIKE statement with an escaped string containing unescaped wildcards '%' (percent) or '_' (underscore) vulnerable?
escaping, mysql, php, sql-like, wildcard
Solution
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:
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 '|'
or use `LOCATE(substr, str) > 0` to find exact matches.
Problem
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?