?
Solved

Handling an apostrophe in user input search

Posted on 2009-04-27
3
Medium Priority
?
412 Views
Last Modified: 2013-11-27
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

Open in new window

0
Comment
Question by:AaronGreene1906
  • 2
3 Comments
 
LVL 25

Expert Comment

by:SStory
ID: 24245512
option 1.)  replace all ' in user input with two '' in database, when getting from database to user input, replace all '' double apostrophes with ' single

option 2.) replace all user input ' apostrophes with the ` (char below the tilde ~)

0
 
LVL 46

Accepted Solution

by:
tbsgadi earned 1500 total points
ID: 24248396
Hi Aaron,

You can replace the apostrophes with chr(34)

Good Luck!

Gary
0
 
LVL 25

Expert Comment

by:SStory
ID: 24250134
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question