Using the like command in SQL

Discussion in 'ASP General' started by MasterChief, Nov 3, 2005.

  1. MasterChief

    MasterChief Guest

    I have a form that uses the POST method to call up test.asp and it
    passes what is typed into the text box. Since is uses the Like command
    the user can enter stuff like %Constant% to get something that is like
    what the user typed in. When I call up the test.asp page it is grabbing
    the text fine but isn't running the SQL command the correct way. The
    SQL command ends up being

    SELECT * FROM [Everyone] WHERE (Name LIKE ':: & strName & ::')

    Here is my code


    strName = Request.Form("Search")
    Set Conn = Server.CreateObject("ADODB.Connection")
    Set Rs = Server.CreateObject("ADODB.RecordSet")
    Conn.Open "PhoneList"
    sSQL = "SELECT * FROM [Everyone] WHERE (Name LIKE ':: & strName & ::')"
    Set Rs = Conn.Execute(sSQL)
     
    MasterChief, Nov 3, 2005
    #1
    1. Advertising

  2. MasterChief wrote:
    > I have a form that uses the POST method to call up test.asp and it
    > passes what is typed into the text box. Since is uses the Like command
    > the user can enter stuff like %Constant% to get something that is like
    > what the user typed in. When I call up the test.asp page it is
    > grabbing the text fine but isn't running the SQL command the correct


    What database are you using?

    > way. The SQL command ends up being
    >
    > SELECT * FROM [Everyone] WHERE (Name LIKE ':: & strName & ::')


    Is it Access?
    http://www.aspfaq.com/show.asp?id=2096

    >
    > Here is my code
    >
    >
    > strName = Request.Form("Search")
    > Set Conn = Server.CreateObject("ADODB.Connection")
    > Set Rs = Server.CreateObject("ADODB.RecordSet")
    > Conn.Open "PhoneList"
    > sSQL = "SELECT * FROM [Everyone] WHERE (Name LIKE ':: & strName &
    > ::')"


    What is the reason for the double-colons? They should not be there. Also,
    you need to concatenate the _value_ of the variable into your string, not
    the name of the variable.

    sSQL = "SELECT * FROM [Everyone] WHERE Name LIKE '" & _
    strName & "'"

    You should know that you are leaving your site and database vulnerable to
    hackers using sql injection:
    http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
    http://www.nextgenss.com/papers/advanced_sql_injection.pdf

    You should not be using dynamic sql, and you should not be allowing users to
    control your sql statement by what they type in your form. You should
    provide a checkbox on your form to allow users to specify whether or not a
    wildcard search is to be performed. Then, depending on the value of the
    checkbox, use the appropriate sql statement:

    if wildcard_on then
    sSQL = "SELECT * FROM [Everyone] WHERE Name LIKE '%" & _
    strName & "%'"
    else
    sSQL = "SELECT * FROM [Everyone] WHERE Name ='" & _
    strName & "'"
    end if

    Here are some links about using parameters:
    http://groups.google.com/groups?hl=...=1&selm=

    http://groups.google.com/groups?hl=...=1&selm=ukS$6S$

    http://www.google.com/groups?selm=&oe=UTF-8&output=gplain

    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=

    Using Command object:
    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

    SQL Server
    http://tinyurl.com/jyy0

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Nov 3, 2005
    #2
    1. Advertising

  3. MasterChief

    MasterChief Guest

    This is an Access Database. I am not to worried about SQL injections.
    This is actually an intranet site for a lumber company. I know almost
    every employee here and they have a hard enough time with the basic
    stuff. But yes I do understand your point about using this in a real
    world scenario. I will try your suggestion in a while. I put the ::
    because original the search page was one page and I used a tutorial
    online that worked and they had me use the :: so I thought it was
    something that had to be used.
     
    MasterChief, Nov 4, 2005
    #3
    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. HalcyonWild
    Replies:
    2
    Views:
    937
    HalcyonWild
    Dec 19, 2005
  2. Christian Seberino
    Replies:
    0
    Views:
    480
    Christian Seberino
    Oct 21, 2003
  3. Remi Morin
    Replies:
    0
    Views:
    517
    Remi Morin
    Oct 23, 2006
  4. Patrick Kowalzick
    Replies:
    5
    Views:
    477
    Patrick Kowalzick
    Mar 14, 2006
  5. MasterChief

    Using the like command in SQL

    MasterChief, Nov 3, 2005, in forum: ASP General
    Replies:
    0
    Views:
    88
    MasterChief
    Nov 3, 2005
Loading...

Share This Page