ASP Replace function not working

Question

<%  
Dim objRs 
Dim conn
Dim strSearchString

strSearchString = Request.Form("name")     
Set objRs = Server.CreateObject("ADODB.recordset")
objRs.CursorLocation = 3
set conn = Server.CreateObject("ADODB.Connection")
conn.open "Data Source=" & Server.Mappath("../db/certs.mdb") & ";Provider=Microsoft.Jet.OLEDB.4.0;"

'replace apostrophe in name to avoid issues
strSearchString = Replace(strSearchString.tostring, "'", "''")

'Sql Query
sql = "Select * FROM [cert] Where [name] like '" & strSearchString & "'"

'open connection
ObjRs.Open sql,conn

'setup the table
with response
    .write "<table border=1 width=100% cellspacing=0 cellpadding=0 class=CustomerTable>" & vbcrlf
    .write "<tr>"
    .write "<th class=AccName colspan=9><div align=center>" & strSearchString & "'s Certifications</div></th></tr>"
    .write "<tr>" & vbcrlf
    .write "<th class=AccName>Name</th>"
    .write "<th class=AccName>Certification</th>"
    .write "<th class=AccName>Date Completed</th>"
    .write "<th class=AccName>Industry</th>"
    .write "<th class=AccName colspan=2>Certification #</th>"
    .write "<th class=AccName>Vendor</th>"
    .write "<th class=AccName>Date Expires</th>"
    .write "<th class=AccName><a href='viewall_sortTechnology.asp'>Technology</a></th>"
    .write "</tr>" & vbcrlf
End with
%>

I'm attempting to use the replace function in order to avoid issues with names containing apostrophes. It seems that this isn't working as when I run the page, the output displays only "O's Certifications" instead of "O'Brien's Certifications".

I should note that the code works as expected for any person without an apostrophe in their name.

The back-end database is MS Access.

I'm fairly new to asp, so any assistance here is greatly appreciated.

@HansUp Good catch no it isn't, in fact that will be what's causing their `replace()` to fail. — user692942, Feb 04 '15 at 17:55

score 2 · Answer 1 · edited May 23 '17 at 12:28

2

The safer, better way to do this is to use parameterized queries.

See: Parameterized query in Classic Asp

edited May 23 '17 at 12:28

Community

1
1

answered Feb 04 '15 at 16:53

Diodeus - James MacFarlane

112,730
33
157
176

1

What Diodeus means is if you did use parametrised queries not only would you be protected from SQL injection attacks but you wouldn't need to do replaces etc., to sanitise the input. – user692942 Feb 04 '15 at 17:11

ASP Replace function not working

1 Answers1