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]

@kkshansid

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]

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]

@kkshansid
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]

@kkshansid
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