search query and protect this text box against sequel injection

[kkshansid]

i have a database as
table name school
field name location
eg in location column data
green school,tagore garden,chink road,jammu
i want to make search on location such that when user enter
green
green school, tagore
search words should come continous
problem
on entering
green school, jammu
record not show
search query is
select*from school where location like'%a%'
where a is variable of text box
also suggest me to protect this text box against sequel injection

[Soniad]

i have a database as
table name school
field name location
eg in location column data
green school,tagore garden,chink road,jammu
i want to make search on location such that when user enter
green
green school, tagore
search words should come continous
problem
on entering
green school, jammu
record not show
search query is
select*from school where location like'%a%'
where a is variable of text box
also suggest me to protect this text box against sequel injection

In ASP Vbscript , when u r comparing form variables in sql statements, use within quotes as shown below :

sql = "select * from school where location like '%"&a&"%'"

then execute this statement

Regards,
"D"

[kkshansid]

search words should come continous in search field

eg in location column data
green school,tagore garden,chink road,jammu
i want to make search on location such that when user enter
green
green school, tagore
broblem is that it takes condition that search words should come continous
problem
on entering
green school, jammu
record not show
search query is
sql = "select * from school where location like '%"&a&"%'"
where a is variable of text box

[jhardman]

When my users enter multiple search terms often no result is showed. For example when they search by location, the field being searched might contain the following data: "green school,tagore garden,chink road,jammu". If the user enters "green" or "green school, tagore" it returns the correct record, but if the search terms are not contiguous the search fails, for example "green school, jammu" no results are returned. My search query is

sql = "select * from school where location like '%"&a&"%'"

where a is the text entered in the text box

It looks like your grasp of ASP is OK, but the SQL needs to be more complicated. Try splitting the entered data by the space character, and enter each term separately into the sql like this:

asplit = split(a, " ") 'asplit is now an array of the terms in a
sql = "SELECT * FROM school WHERE "
for each x in asplit
   sql = sql + "location LIKE '%" & x & "% AND "
next

'sql now ends with "AND " and this needs to be removed
sql = left(sql, len(sql)-4) 'removes the last 4 characters from the string

Try this and let me know if it works.

Jared

[jhardman]

also suggest me to protect this text box against sequel injection

In order to protect against SQL injection, you will need to search and replace special characters in your text - the first one that comes to mind is the semi-colon (;) since you need to separate sql statements with the semicolon. There are a whole bunch of characters it would be good to replace, some people have lists, anyway, the basic replace function looks like this:

searchString = Replace(a, ";", "|")

Jared