Multiple parameters from Querystring to serach the Database

Question

Multiple parameters from Querystring to serach the Database

babar_ch

SSC Rookie

Points: 28
More actions
January 24, 2018 at 9:01 am

#410099

Hi,
I have a form where users can click on the colors check-boxes. These values go to query string like.
http://www.example.com/page?Color=Red&Color=Black&Color=Blue
ColorC=Request.QueryString("Color")
This gives values like ColorC=Red, Black, Blue
(1-20 color selections)
My question is that how to search the database by SQL statement. The following will not work in this case. I am using VBscript and MS SQL database.
SQL = "Select * from Products where DESCRIPTION LIKE '%" & ColorC & "%'"
The number of colors are not fixed, there could be 1, 2, 3, 5, 6 more colors as selected by a user. And it has LIKE as it finds colors in product description. How does IN work in this case.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please don't ever do that. There's a critical security flaw in your code (SQL Injection). NEVER concatenate parameters into a string and execute the string.

Have a look at table-type parameters to pass multiple values to a stored procdure.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Thom A SSC Guru Points: 99126 More actions · Answer 2

Alternatively, instead of using a Table-type parameter, you could consider passing the value as a delimited string and then using a string splitter.

Either way, Gail is completely right; parametrise your SQL, or use a Stored Procedure. SQL Injection is never a good thing.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

babar_ch SSC Rookie Points: 28 More actions · Answer 3

Hi,
Thank you for the reply. I am using a function to avoid injection like ColorC=Sqlinj(Request.QueryString("Color")) that removes the harmful statements.
I am not very good at coding. Can you please write the statements in detail to solve the problem mentioned above.

Thanks

babar_ch SSC Rookie Points: 28 More actions · Answer 4

babar_ch - Wednesday, January 24, 2018 10:46 PM
Hi,
Thank you for the reply. I am using a function to avoid injection like ColorC=Sqlinj(Request.QueryString("Color")) that removes the harmful statements.
I am not very good at coding. Can you please write the statements in detail to solve the problem mentioned above.
Thanks

I am using SQL Server 2005.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

babar_ch - Wednesday, January 24, 2018 10:46 PM
Hi,
Thank you for the reply. I am using a function to avoid injection like ColorC=Sqlinj(Request.QueryString("Color")) that removes the harmful statements.

Blacklisting doesn't work except against the simplest forms of SQL injection. It's a false sense of security that anyone competent (or any well-written injection tool) will get around.
Parameterise your queries and DO NOT concatenate input into strings and execute them

I don't think that 2005 had table-type parameters (any why are you using something so old and out of support), so you'll need to pass a comma-delimited list and use a string splitting function.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass