Passing percent sign in querystring

[Joey Martin]

I am passing a sql string thru my querystring for the next page to
capture.
example: www.xxxxxxxx.com/index.asp?str=select * from table where name
like '%doe%'

Passing a basic string works fine. But, when I use the LIKE statement it
does not work. I know it's because of the % sign, so how do I translate
this thru, so that the following page picks up the percent sign?

 

[McKirahan]

I am passing a sql string thru my querystring for the next page to
capture.
example: www.xxxxxxxx.com/index.asp?str=select * from table where name
like '%doe%'

Passing a basic string works fine. But, when I use the LIKE statement it
does not work. I know it's because of the % sign, so how do I translate
this thru, so that the following page picks up the percent sign?

A JavaScript solution:

var url = "www.xxxxxxxx.com/index.asp?str=";
var sql = "SELECT * FROM table WHERE name LIKE '%doe%'";
window.open(url + escape(sql),"","");

 

[Kyle Peterson]

well, hopefully your only doing this in a secure area of the site that only
admins use

regardless you want to Server.URLEncode that string before you send it to
the next page

Server.URLEncode(YourSQLString)

it will encode certaint characters so they make it over ok...
you dont have to worry about decoding it as the request object takes care of
that

 

[ASPfool]

Hey Joey,

i think writing the whole sql statement in the querysting is a bad idea -
you are open to sql injection attacks and the like. All your user has to do
is substitute delete for select, and hey presto, your table is empty (unless
you've denied delete rights on your db user account)....

regards,
Jon.

 

[Joey Martin]

Ok. So if I do not include the sql querystring in the address bar (and I
appreciate you pointing out the security problems), how do I perform
sortable colums? I need a way to pass the querystring to the next page
that re-sorts the columns.

 

[Mark Schupp]

I would do the sort using client-side JavaScript myself (no trips to the
server just to get the same data in a different order). If you cannot, then
keep the current query parameters in session variables or in a database on
the server. Or pass the parameters used to build the query instead of the
query itself.

 

[larrybud2002]

Ok. So if I do not include the sql querystring in the address bar (and I
appreciate you pointing out the security problems), how do I perform
sortable colums? I need a way to pass the querystring to the next page
that re-sorts the columns.

What I do is have a sortby in the querystring, which matches the column
names... i.e.

resultpage.asp?sortby=last_name,first_name

Then in resultpage.asp you just dynamically build your sql...


mysql="select * from personnel order by " & sortby

You should check to see if sortby is empty, and set it to a default
sorting method if so.

 

[Mark Schupp]

What I do is have a sortby in the querystring, which matches the column
names... i.e.

resultpage.asp?sortby=last_name,first_name

Then in resultpage.asp you just dynamically build your sql...


mysql="select * from personnel order by " & sortby

You should check to see if sortby is empty, and set it to a default
sorting method if so.

This can open you up to SQL Injection attacks. You should never include any
data from the request in a SQL statement without validating it and escaping
special characters in it first.

 

[larrybud2002]

What I do is have a sortby in the querystring, which matches the
column

This can open you up to SQL Injection attacks. You should never include any
data from the request in a SQL statement without validating it and escaping
special characters in it first.

How can it do that when it's forced after "order by" in a select
statement?

 

[Mark Schupp]

I'm not an expert on it but if I understand correctly one attack involves
appending SQL Statements. Some DBMSs allow multiple statements to be
executed in one call.

sortby = "last_name,first_name"
mysql="select * from personnel order by " & sortby
mysql=> select * from personnel order by last_name,first_name

Now try:
sortby = "last_name,first_name;delete from personnel"
mysql="select * from personnel order by " & sortby
mysql=> select * from personnel order by last_name,first_name;delete from
personnel

If you do a search on "sql injection" you will probably find a dozen
articles that explain this and other attacks much better.

 

[larrybud2002]

I'm not an expert on it but if I understand correctly one attack involves
appending SQL Statements. Some DBMSs allow multiple statements to be
executed in one call.

sortby = "last_name,first_name"
mysql="select * from personnel order by " & sortby
mysql=> select * from personnel order by last_name,first_name

Now try:
sortby = "last_name,first_name;delete from personnel"
mysql="select * from personnel order by " & sortby
mysql=> select * from personnel order by last_name,first_name;delete from
personnel

Duly noted. Stripping out all spaces from the sortby should take care
of that.

 

[Bob Barrows [MVP]]

Duly noted. Stripping out all spaces from the sortby should take care
of that.

Better yet, use parameters just in case the hacker is aware of that trick.
SQL cannot be injected if parameters are used.

Bob Barrows