I am making a Doctrine query and I have to do a wildcard match in the where clause. How should I escape the variable that I want to insert?
The query I want to get:
SELECT u.* FROM User as u WHERE name LIKE %var%
The php code until now:
$query = Doctrine_Query::create()
->from('User u')
->where();
What should come in the where clause? The variable I want to match is $name
Nobody answered your question correctly, so I'll make a stab at it.
->where('u.name LIKE ?', array("%$name%"));
->where('u.username LIKE ?', '%'.$username.'%')
Neither of these are safe. Let me explain a few scenarios.
Scenario 1
Imagine you want to let users search for matching usernames, but you never want to list ALL usernames. Perhaps you don't want somebody to easily steal a list of a million usernames from you. somewhere prior to this code, you did something like this:
if (strlen(trim($name)) < 5) throw Boogey_Monster_Exception();
You thought this would prevent somebody from leaving the field blank and pulling down a list of all usernames... but in reality the user can submit "_____" or "%%%%%" or anything similar to get a list of all usernames, not just matching 5 or more known characters.
I have personally seen this form of attack used on several large, public websites.
Scenario 2
You have a website with lots of users and lots of user data. You have 10,000,000 rows in your user table. You want to enable site's users to find another user's username by searching for known prefixes.
So you write some code like this, modified slightly from the example above to only have a wildcard AFTER the search string.
->where('u.name LIKE ?', array("$name%"));
If you have an index on u.name, then this LIKE query will use the index. So if the user submits $name="john", then this query will efficiently match users like johndoe, johnwayne, johnwaynegacy, etc.
However, if the user submits $name="%john" instead, this query no longer uses the index and now requires a full table scan. On a very large database this can be a very slow query.
The MySQL manual on SQLi mentions this same thing (pages 78-79) and I googled for some examples of slow query performance and found one link.
This may not sound like a big deal, but for sites backed by an RDBMS, the RDBMS is usually a significant bottleneck, and much of the performance engineering revolves around reducing contention on the RDBMS. IF you have a handful of users launching an attack that ties up a database handle for 60+ seconds, and you have a small pool of database handles, you can see how this could quickly scale to monopolize all of your database handles and prevent legitimate users from being able to get one.
Links
http://dev.mysql.com/tech-resources/articles/guide-to-php-security-ch3.pdf
http://forums.mysql.com/read.php?24,13397,13397
Solution
Anyway, the better solution (as mentioned in the MySQL manual linked above and by commenter @Maxence, is to use addcslashes()):
$username = addcslashes("%something_", "%_");
Note that since the sql examples here use prepared statements, which are completely immune to sql injection, it is not necessary or desirable to use mysql_real_escape_string(); the escaping it performs is solely to prevent sql injection. What we're trying to prevent is wildcard injection, and that requires a function that escapes the two sql wildcard characters, '%' and '_'.
Something bad happened to Doctrine's documentation so here's the Google copy (check Like Expressions section)
...
->where('u.name LIKE ?', array("%$name%"));
