Peter's Gekko

public Blog MyNotepad : Imho { }

SELECT LIKE %

Trying to be smart I ran into this. In an app the user is looking for some person, the query has some parameters to help her.

SELECT Naam, Afkorting FROM dbo.Persoon WHERE (Naam LIKE @Param1) AND (Functie LIKE @Param2)

To the parameter values is appended the % character. The query will return all rows which start with the value supplied.

PersoneelZoeken.SelectCommand.Parameters["@Param2"].Value = string.Format("{0}%", paramvalue);

The catch is what happens when an empty parameter is passed in. Which will result in a query which looks like

SELECT Naam, Afkorting FROM dbo.Persoon WHERE (Naam LIKE '%') AND (Functie LIKE '%')

At first sight the query will return all rows. It does not. It will return al rows which do contain a value for the Naam and the Functie column. % matches any text but does not match a dbNull value. Makes sense. But was a catch.

Published Apr 08 2005, 02:19 AM by pvanooijen

Filed under: Data

Comments

Thomas Eyde said:

How do you handle @Param2 = "' go drop database go --"?

# April 8, 2005 5:08 AM

John Papa said:

You gotta love NULLs. ;-) Actually, I do, but its always fun trying to account for them.

# April 8, 2005 5:15 AM

Mark Bonafe said:

Nice catch.

Thomas - how do YOU handle SQL injection?

# April 8, 2005 6:08 AM

darrell said:

You can make it return NULLS by doing:

SELECT IsNull(Naam), Afkorting FROM dbo.Persoon WHERE (Naam LIKE @Param1) AND (Functie LIKE @Param2)

But I'm sure you already knew that. :)

# April 8, 2005 7:57 AM

Thomas Eyde said:

I am a rookie on sql injection handling. I happen to know about the LIKE vulnerability by accident: A colleague was responsible for a solution that was hacked this way.

I guess the solution must be to sanitize the parameter in some way. One could look for the first unescaped quote and trim down to that:

"O''Brian is valid" -> OK
"inject' go drop database go --" -> trim down to: "inject"

or one could escape them:
"inject'' go drop database go --" -> "inject' go drop database go --"

# April 8, 2005 8:37 AM