Using the like command in SQL

[MasterChief]

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)

 

[Bob Barrows [MVP]]

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&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]

http://www.google.com/[email protected]&oe=UTF-8&output=gplain

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&[email protected]

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

SQL Server
http://tinyurl.com/jyy0

 

[MasterChief]

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.