SQL LIKE clauses wildcard injection

I’m going to talk about a little known vulnerability and traditionally considered of low risk, although as we are going to see in some situations it can have a big impact.

This vulnerability involves the possibility of injecting a wildcard in the search field of a LIKE clause in a SQL statement.

OWASP covers briefly this kind of injections in its guides.

In SQL we have 2 types of wildcards:

• % equivalent to any string of cero or more characters.
• _ equivalent to any character.

An application is vulnerable to this attack when it uses the LIKE operator with a user received parameter not filtering any of these 2 wildcards.

For example if we have the following URL:
http://www.example.com/fruit.php?name=apple

That shows a text extracted from the database with a SQL query like the following:
SELECT text FROM table WHERE fruit LIKE ‘$name’

Instead of using the simple form:
SELECT text FROM table WHERE fruit=‘$name’

Even if the $name parameter is sanitized for avoiding SQL injection (for example filtering the single quote) it’s still possible to inject wildcards in the search field, as following:
http://www.example.com/fruit.php?name=ap%

In an application like this, altering the search logic it’s not critical, but what happens if we have another application like the following? (Where we don’t know the name of the users).
http://www.example.com/userphoto.php?name=john

We can easily do the following and obtain a listing of all available users:
http://www.example.com/userphoto.php?name=a%
http://www.example.com/userphoto.php?name=b%
http://www.example.com/userphoto.php?name=c%
…

We can automate the process with a simple script that will go pulling the names of each user character by character (like in the war games movie).

In what situations this kind of vulnerabilities can be dangerous?

• In login forms. I have found sometimes this vulnerability in the “username” field of some forms and less commonly even in the “password” field.
• In password recovery forms. This vulnerability can allow us to reset the password of other users.
• In fields containing session identifiers or tokens. This vulnerability can allow us to “steal” or “predict” the tokens or the sessions ids of other users.

It’s incredible but this works sometimes:

The injection of the % wildcard sometimes can be hard, because this character is usually filtered to avoid encoding attacks o precisely because it is wrongly decoded (in this case we can replace it by %25 or %2525).

Some time ago I found a curious situation where an application authenticated users with a session identifier stored in a database. The extraction of the values stored was made with a vulnerable query, this way:
http://www.example.com/admin/private.php?sessionid=0123456789

The server filtered the % wildcard, but the _ character was permitted. With the following trick we could exploit the vulnerability:
http://www.example.com/admin/privado.php?sessionid=__________

If we wanted to access a specific session, we only needed to do a sweep:
http://www.example.com/admin/privado.php?sessionid=0_________
http://www.example.com/admin/privado.php?sessionid=1_________
http://www.example.com/admin/privado.php?sessionid=2_________
…

Why some programmers fall with this evident bug?

I suppose that sometimes it’s only an oversight, but I have detected that some programming frameworks encapsulate the SQL statements transparently for the programmer but internally they use the LIKE operator, without him even knowing.

For example the following Django sentence:
result=Entry.objects.get(headline__contains='Lennon')

Results in:
SELECT ... WHERE headline LIKE '%Lennon%'

This can easily lead to multiple vulnerabilities if the developer is not careful.

Also this other bug very similar in the Propel module of Symfony.