Handling an apostrophe in user input search
I have created this function to compare user input to a set of addresses in one of my tables, but I get an error when the user uses an apostrophe. How do I handle that?
varINPUT = varDATA
If IsNull(varINPUT) = True Or IsNumeric(varINPUT) = True Then
fADDR_MATCH = False
Else
Set CNT = CurrentProject.Connection
strSQL = "SELECT vGIS_Data_Address.ADDR FROM "
strSQL = strSQL & "vGIS_Data_Address WHERE (("
strSQL = strSQL & "(vGIS_Data_Address.ADDR)=" & "'" & varINPUT & "'" & "));"
RST.Open strSQL, CNT, adOpenKeyset, adLockOptimistic
intCOUNT = RST.RecordCount
RST.Close
CNT.Close
Set CNT = Nothing
If intCOUNT <> 1 Then
fADDR_MATCH = False
Else
fADDR_MATCH = True
End If
End If
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Using ADO.NET command objects handles this and SQL injection attacks for you. Maybe the plain old ADO command object does the same--I am not sure.
option 2.) replace all user input ' apostrophes with the ` (char below the tilde ~)