How to use select with IN operator and value from variable?

Question

I want to select all from the table 'products' where city IN (Varberg,Falkenberg) and if I use the below it works, but if I have the cities in a variable I can´t get it to work? I'm using a mySql database.

sql = "SELECT * FROM products where city in ('Varberg','Falkenberg')" 
set rs = conn.Execute (sql)

So if I use this, it is not working.

cities=request.querystring(cities)

so that the variable becomes like this

cities="Varberg,Falkenberg"

sql = "SELECT * FROM products WHERE city IN ('"& cities &"')" 
set rs = conn.Execute (sql)

How many times is this question going to be asked, there are numerous duplicates already. — user692942, Jan 02 '19 at 21:26

score 1 · Answer 1 · answered Jan 02 '19 at 15:39

The query you generate misses two quotes. The generate query is

SELECT * FROM products where city in ('Varberg,Falkenberg')

and not

SELECT * FROM products where city in ('Varberg','Falkenberg')

BTW: Never use parameters provided by the user without validation and proper sanitization.

score 0 · Answer 2 · answered Jan 02 '19 at 15:38

0

The SQL statement created with your variable will be

SELECT * FROM products WHERE city IN ('Varberg,Falkenberg')

which is different than the one you're looking for:

SELECT * FROM products where city in ('Varberg','Falkenberg')

You need to adjust your code logic to generate the correct list for your variable. Your variable holds a single value list because of the missing single quotes. You need to add the quotes so the variable holds a multi-value list.

PS - your code is also wide open to SQL injection. You should be using a parameterized query.

answered Jan 02 '19 at 15:38

squillman

13,363
3
41
60

Thanks sqluillman, how would a parameterized query look like? I will try to change the logic as you say, thanks. – Claes Gustavsson Jan 02 '19 at 15:44
@ClaesGustavsson It depends on what language you're using. Looks like VB.net. Have a look here: https://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i – squillman Jan 02 '19 at 15:47
1

Its old asp classic. – Claes Gustavsson Jan 02 '19 at 15:52
Ok. Here's a link using classic asp. https://stackoverflow.com/questions/7654446/parameterized-query-in-classic-asp – squillman Jan 02 '19 at 15:54
Ok, thanks, will check it out later. – Claes Gustavsson Jan 02 '19 at 16:00

How to use select with IN operator and value from variable?

2 Answers2