ASP.NET passing value from textbox to selectcommand with IN vb

Question

I have an asp.net webpage with an asp.net gridview with the following query:

 SelectCommand="SELECT * from details2 as t2 WHERE (t2.OCC IN ('+ @txtCOUNTRY +') OR t2.DCC IN ('+ @txtCOUNTRY +')) and t2.ac='Y'"

The textbox txtCOUNTRY value can have the following values (for example):

'AR','ES'

However the parameter @txtCOUNTRY doesn't seem to be properly, written as the gridview shows nothing.

if I change it to (for example) it works:

 SelectCommand="SELECT * from details2 as t2 WHERE (t2.OCC IN ('AR,'ES') OR t2.DCC IN ('AR','ES')) and t2.ac='Y'"

So I can only assume the @txtCOUNTRY parameter is incorrectly written.

Any ideas ?

Answer 1

When you pass the values 'AR','ES' via a SQL parameter, they will automatically be escaped which would make your query literally look for the value: 'AR','ES'.

See this: Parameterize an SQL IN clause

So in your case (since you have a comma separated list of values, each in single quotes):

SelectCommand = "SELECT * from details2 as t2 WHERE " &
    "(','+@txtCOUNTRY+',' LIKE '%'','+t2.OCC+''',%' OR ','+@txtCOUNTRY+',' LIKE '%'','+t2.DCC+''',%') " & 
    "and t2.ac='Y'"

If you are not enclosing your values in single quotes (for example: AR,ES) then the query becomes a bit more readable as you are not having to escape the single quotes in your query:

SelectCommand = "SELECT * from details2 as t2 WHERE " &
    "(','+@txtCOUNTRY+',' LIKE '%,'+t2.OCC+',%' OR ','+@txtCOUNTRY+',' LIKE '%,'+t2.DCC+',%') " & 
    "and t2.ac='Y'"

Edit to provide some clarity as to why this works.

So for example, providing AR,ES as the value for the @txtCOUNTRY parameter would yield a condition:

,AR,ES, LIKE *,[t2.OCC],*   OR   ,AR,ES, LIKE *,[t2.DCC],*

This condition would be a match if either t2.OCC or t2.DCC were either AR or ES because the pattern would be matched.

This provides a simple way to pass in a comma separated list as a parameter so your query isn't exploitable with a SQL injection.