Script Injection in Sql Server

farhan_iac · August 19th, 2004, 01:44 AM

Hello
I need a little Help in a Case. I have Dynamic Sql Queries into my Asp.net Pages that create Queries relevant to the user Input. My Search Query is like

"Select * from tablename where fieldname like '"+Request.Form["textbox"]+"';

it is a very stupid thing,i know that,but it my application is small scale , i dont want to use Access built-in Queries , or Sql Server Stored Procedure with that application,

I have also tried it with Asp.net Prepared Statement, but it only works with exact Match like Fieldname=value;with 'like' clause this thing fails.My requirement is that a solution that works with wild Card Search as well as exact search.

PLease Help me to solve this problem

Thank You

ChrisScott · August 19th, 2004, 01:59 AM

You need to add wildcards to the like clause...

Code:

LIKE '%" + Request.Form["textbox"] + "%'

It would be probably also be worth replacing any single quotes in the search criteria to prevent sql errors.

HTH,

Chris

happygv · August 19th, 2004, 02:05 AM

"Select * from tablename where fieldname like '%"+Request.Form["textbox"]+"%';

You should use % around the values, when using LIKE operator.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

farhan_iac · August 19th, 2004, 03:58 AM

Thnx, I know that Wild Card would help in searching, but the problem is that when user enters the following chun of code, ' or 1=1;--, it returns all the records int the database regardless of where clause, becuase of that '" + var + "' situation
Kindly consider it

ChrisScott · August 19th, 2004, 04:06 AM

Just replace single quotes with two single quotes, so if you get

Code:

' or 1=1

Turn it into

Code:

 '' or 1=1

before placing it in your query, this will give a query something like...

Code:

Select * from tablename where fieldname like '%'' or 1=1%';

and should run fine.

Cheers,

Chris

farhan_iac · August 19th, 2004, 04:11 AM

I have Tried it too, but for that I have to check the validity of input on server-side, but plz understand me, I dont want to check the validity of Input at server-side, I just want that my Qeury would always be okay and nobody can break it, By the way, I have tested it also, but when u give empty field and submit, it gets all records like in the Script Injection, so I have to validate the input at server-side, but I want to remove that Hassle,
Thanks very much for your Help

ChrisScott · August 20th, 2004, 03:41 AM

If you want to dynamically build queries in this way, you will have to use server-side validation, otherwise people will always be able to break your code.

Kind regards,

Chris