Apostrophe's and ASP

mada999

hey all im having problems with handling apostrophe's in asp/sql

this is my code

<%
name = Request.QueryString("name")
name = Replace(Name, "'", "''")
sql= ""
strsql = ""
strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("../whatever.mdb")
strsql="SELECT * FROM customers WHERE Category like '%"& request.querystring("name")&"%'"
set conntemp=server.createobject("adodb.connection")
conntemp.open strconn
set rstemp=conntemp.execute(strsql)
response.write(strsql)
If rstemp.eof then
response.write "<center><font face=arial size=-1>Sorry, there were none found with that name<br>"
response.write "<br>Please click <a href='javascript:history.back(1)' target=main>here</a> to search again!</font>"
conntemp.close
set conntemp=nothing
response.end
end if
%>
<%response.write(strsql)%>

<font face="verdana" color="336699" size="2"><b><%=category%></b></font>
<table border="0" width="100%" cellspacing="0" cellpadding="0">
<tr>
<td width="10%" bgcolor="#0099CC"><font size="2" face="Arial"><b>Info</b></font></td>
<td width="24%" bgcolor="#0099CC"><font size="2" face="Arial"><b>Name</b></font></td>
<td width="32%" bgcolor="#0099CC"><font size="2" face="Arial"><b>Address</b></font></td>
<td width="16%" bgcolor="#0099CC"><font size="2" face="Arial"><b>Category</b></font></td>
<td width="18%" bgcolor="#0099CC"><font size="2" face="Arial"><b>Description</b></font></td>
</tr>
</table><% do while not rstemp.EOF %>
<table border="0" width="100%" cellspacing="0" cellpadding="0">
<tr>
<td width="10%"><font size="2" face="Arial"><a href="busview.asp?id=<%response.write(rstemp(0))%>">View</a></font></td>
<td width="24%"><font size="2" face="Arial"><%response.write(rstemp(4))%></font></td>
<td width="32%"><font size="2" face="Arial"><%response.write(rstemp(5))%></font></td>
<td width="16%"><font size="2" face="Arial"><%response.write(rstemp(2))%></font></td>
<td width="18%"><font size="2" face="Arial"><%response.write(rstemp(3))%></font></td>
</tr>
</table>

<%
rstemp.MoveNext
' move to the next row in the data set
loop
' goto the next itteration of the while loop
rstemp.Close
Conntemp.Close
%>



i have been googling for ages and find things but they dont work .... cracking up!!
can anyone help me ??

cgarvey

strsql="SELECT * FROM customers WHERE Category like '%"& request.querystring("name")&"%'"

You should wrap that in a replaceAll( "'", "''" ), or whatever the string function is in VB ASP.

strsql="SELECT * FROM customers WHERE Category like '%" & request.querystring("name").replaceAll( "'", "'' ) & "%'"

.cg

Cake Fiend

Also, there should be no apostrophe in "apostrophes". It's plural, not possessive.

The irony!

Talliesin

mada999 wrote:

name = Request.QueryString("name")
name = Replace(Name, "'", "''")

Here you escape apostrophes in the querystring for SQL.[/QUOTE]

mada999 wrote:

strsql="SELECT * FROM customers WHERE Category like '%"& request.querystring("name")&"%'"

Here you obtain the querystring again rather than use your escaped version.
So you dealt with the apostrophes, but then didn't make use of your having dealt with them.

I imagine it's because there was code in there to do this right, but it wasn't being used where needed, that you kept not seeing the bug.

mneylon

Doesn't your text editor show you the syntax problems?

DJB

As Talliesin said...

Your sql statement doesn't make use of you original request of the name variable... it should be...

strsql="SELECT * FROM customers WHERE Category like '%"& name &"%'"

Rgds,

Dave

rsynnott

This is, erm, actually a very big problem. (Though it's NOT a syntax problem as such). You should do the apostrophe replace thing EVERYWHERE, even where there's no reasonable expectation of the user using apostrophes. Otherwise, a user can just type:
'; DROP TABLE importantdata
or
'; INSERT INTO users VALUES('cracker', 'admin', 'mypassword'
and you're in serious trouble.

The equivalent in PHP is addslashes(); PERL and Java do this much more gracefully. Also, you must be sure to undo it afterwards.