Passing percent sign in querystring

Discussion in 'ASP General' started by Joey Martin, Apr 22, 2005.

  1. Joey Martin

    Joey Martin Guest

    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?

    *** Sent via Developersdex http://www.developersdex.com ***
    Joey Martin, Apr 22, 2005
    #1
    1. Advertising

  2. Joey Martin

    McKirahan Guest

    "Joey Martin" <> wrote in message
    news:OJgK$...
    > 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),"","");
    McKirahan, Apr 22, 2005
    #2
    1. Advertising

  3. 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


    "Joey Martin" <> wrote in message
    news:OJgK$...
    >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?
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    Kyle Peterson, Apr 22, 2005
    #3
  4. Joey Martin

    ASPfool Guest

    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.

    "Kyle Peterson" wrote:

    > 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
    >
    >
    > "Joey Martin" <> wrote in message
    > news:OJgK$...
    > >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?
    > >
    > > *** Sent via Developersdex http://www.developersdex.com ***

    >
    >
    >
    ASPfool, Apr 22, 2005
    #4
  5. Joey Martin

    Joey Martin Guest

    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.

    *** Sent via Developersdex http://www.developersdex.com ***
    Joey Martin, Apr 22, 2005
    #5
  6. Joey Martin

    Mark Schupp Guest

    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.

    --
    --Mark Schupp
    Head of Development
    Integrity eLearning
    www.ielearning.com

    "Joey Martin" <> wrote in message
    news:...
    >
    >
    > 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.
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    Mark Schupp, Apr 22, 2005
    #6
  7. Joey Martin

    Guest

    Joey Martin wrote:
    > 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.
    , Apr 22, 2005
    #7
  8. Joey Martin

    Mark Schupp Guest

    <> wrote in message
    news:...
    >
    > Joey Martin wrote:
    >> 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.
    >

    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.
    Mark Schupp, Apr 22, 2005
    #8
  9. Joey Martin

    Guest

    > > 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.


    How can it do that when it's forced after "order by" in a select
    statement?
    , Apr 22, 2005
    #9
  10. Joey Martin

    Mark Schupp Guest

    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.

    --
    --Mark Schupp
    Head of Development
    Integrity eLearning
    www.ielearning.com

    <> wrote in message
    news:...
    >> > 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.

    >
    > How can it do that when it's forced after "order by" in a select
    > statement?
    >
    Mark Schupp, Apr 22, 2005
    #10
  11. Joey Martin

    Guest

    Mark Schupp wrote:
    > 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.
    , Apr 22, 2005
    #11
  12. wrote:
    > Mark Schupp wrote:
    >> 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.


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

    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Apr 22, 2005
    #12
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    13
    Views:
    53,213
    John Machin
    Sep 26, 2006
  2. Adeel Ahmad
    Replies:
    1
    Views:
    368
    Anthony Jones
    Mar 7, 2006
  3. Replies:
    2
    Views:
    227
  4. Replies:
    2
    Views:
    169
  5. Bartek Lakomiec
    Replies:
    4
    Views:
    226
    Eric Schwartz
    Apr 5, 2007
Loading...

Share This Page