Link to home
Start Free TrialLog in
Avatar of pcalabria
pcalabriaFlag for United States of America

asked on

Need best practice for saving sql which contains user input single and double quotes

I'm sure this is an extremely common problem.  I looking of a best practice solution.

Users enter text in a form.  I load the response into a variable... strUserText=me.UserText

Next, I save this response into a table using an variety of different SQL statements.

User's however enter text that includes both single and double quotes.  What is the best practice to prevent these characters from causing SQL errors?


Example:
The user enters: It's my favorite day of the week
into a text box UserText

my code:

strUserText=me.UserText

strSQL="UPDATE logShippingStatus SET ShippingStatus = '" & strUserText & "' WHERE Status.ID=680491;"
docmd.runsql(strSQL)


The apostrophe in "it's" breaks my SQL

Thanks in advance.
p
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
use function replace : 
Replace(strUserText, "'", "''")

Open in new window

@Norie you are the fastest ;-)
The same logic applies for double quotes
strUserText="Experts Exchange is the ""best"" as always"


strSQL="UPDATE logShippingStatus SET ShippingStatus = '" & strUserText & "' WHERE Status.ID=680491;"
docmd.runsql(strSQL) 

Open in new window

Avatar of pcalabria

ASKER

Thanks everyone.. some solutions  are so simple once you know them...  LOL.. I didn't realize I could using double single quotes... :-)
Louis... I think Norie got it two minutes sooner.. you're both amazing..

John... how do you do you use replace to replace double quotes?  Is that a different question?  I can start one if yes...
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

I would also suggest you not use string concatenation to build SQL. Especially using input directly from users.  You are open to SQL Injection.

For example, I find out there is a qtyShipped column in logShippingStatus.  
What if I enter this in UserText:  , qtyShipped=999
@slightwv.. thanks for pointing that out... I we've had hackers try SQL injection on our website before.. but I haven't been thinking in those terms for internal use software.. I should!