C cricketbird Registered User. Local time Today, 08:52 Joined Jun 17, 2013 Messages 118 Mar 5, 2014 #1 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?
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?
jdraw Super Moderator Staff member Local time Today, 08:52 Joined Jan 23, 2006 Messages 15,574 Mar 5, 2014 #2 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?
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?
C cricketbird Registered User. Local time Today, 08:52 Joined Jun 17, 2013 Messages 118 Mar 5, 2014 #3 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
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
G Galaxiom Super Moderator Staff member Local time Today, 22:52 Joined Jan 20, 2009 Messages 12,895 Mar 6, 2014 #4 cricketbird said: I know single and double quotes are problematic, and I'm pretty sure parentheses, brackets, # and * would cause problems as well. Click to expand... 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.
cricketbird said: I know single and double quotes are problematic, and I'm pretty sure parentheses, brackets, # and * would cause problems as well. Click to expand... 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.
C cricketbird Registered User. Local time Today, 08:52 Joined Jun 17, 2013 Messages 118 Mar 6, 2014 #5 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.
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.