Code to sanitize text for use in VBA/SQL

cricketbird

Registered User.
Local time
Today, 08:52
Joined
Jun 17, 2013
Messages
118
Is there a function that will sanitize user-entered text and remove problematic characters for SQL? Or do I have to replace() each one individually?
 
You can parse the user's input and drop certain characters.
There is no magic function to do it. But you could build something to match your requirements.
Do you have a list of characters you want to avoid?
 
Any that would break an SQL statement called from within VBA.

I know single and double quotes are problematic, and I'm pretty sure parentheses, brackets, # and * would cause problems as well.

If there is a definitive list, that would help too.

Thanks :)
 
I know single and double quotes are problematic, and I'm pretty sure parentheses, brackets, # and * would cause problems as well.

Quotes and double quotes only cause a problem because they can potentially close a quoted string.

Parentheses, # and * should only appear inside a quoted string and hence won't cause an issue.

I usually drop double quotes and escape single quotes by doubling them.

BTW You might also want to consider avoiding SQL injection. It is possible to insert SQL commands into a text box and have them executed.
 
Thanks! Okay, I'll just sanitize quotes, then. I'm not worried about sql injection - this is a local db for use by just a few people.
 

Users who are viewing this thread

Back
Top Bottom