Access database - escaping special characters question

[Desolator144]

Long story short, I'm using text gathered by a website form that users type in that can contain ANYTHING and putting it into XML, importing it into a VB.NET app, rearranging it, and then importing it into an Access database. People apparently love typing stuff with double quotes and single quotes and asterisks and all that for their name and their dog's name and their address.
To get those characters safe in the first place to use PHP to put them in a MYSQL database online, the person in control of the web side used a function to replace them all with something looking similar to HTML entity codes. Like & turns into &amp; and ' into &apos;. So my VB app has to change them back but when it does, some of those characters blow up my SQL statements. So far I've narrowed most problems down to apostrophes aka single quotes and I heard that's the only character that Access/SQL in general can't handle cuz that's what it uses to determine the start and end of strings. I heard the trick is to replace one single quote with two single quotes cuz that's the escape character. So all I have to do is loop through the whole table and change ' to '' and it should be able to handle all other wacky characters like < and | and " right? But I'm pretty sure my SQL statements containing an asterisk inside a string value make the database throw back an error too. Do I use the same escape character and replace * with '* or does that not work? And are there any other characters I need to handle?

[RhinoBull]

...I heard the trick is to replace one single quote with two single quotes ...

That is correct, here is a quick sample to get records:

strSQL = "Select * From Employees" & vbNewLine
strSQL = strSQL & "Where EmployeeName = '" & Replace(strName, "'", "''") & "'"

You do basically the same thing with Insert/Update/Delete.

[si_the_geek]

The ' character is by no means the only one to worry about, and which characters are special (as well as how you need to deal with them) often varies by database system/version... but the good news is that you don't need to write lots of code to deal with it all - because it has already been done for you, in what is known as parameterised queries.

For an explanation of why you should be using them (and links to code examples), see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of this forum).

[Desolator144]

I did read up yesterday on parameterized SQL statements and I have used them before but I thought they were just a stupid, ugly, less intuitive way of doing it :P I didn't know the objects used automatically handled single quotes and stuff. I'm going to replace all my lovely string concatonation SQL statements eventually but for the time being we defintely don't have the time to replace every single SQL statement in the entire program and remove all the existing single quote wrapping code for strings and stuff. I basically have to fix the problem right by um...yesterday :P so I figured since I have to write code to switch back the &apos; and all them, I might as well just add in one line that adds an escape character if that works and then after that's in place, start redoing the database interaction the correct way. So would it work in the meantime to just replace every character that access in sensitive to with a single quote before it?

[GaryMazzone]

No the single qoute is just for a single qoute. for double qoute (") you would use two double qoutes ("").

[Desolator144]

aha, so * should be ** then? Is that basically the Access database rule is duplicate it and you're good? Also would anyone happen to have a list of all the characters that screw up SQL statements for Access?

[si_the_geek]

No. Each special character (or combination of characters) has a different way of being escaped, for example I think the [ character becomes [[]

I'm afraid I don't know of a list, but note that Access is not actually relevant - you need to find the list for the Provider you are using to work with the database (which I presume is either Jet or ACE).

[Desolator144]

ugh, I can't find a complete list for JET anywhere. There's a few here and there and they're all different just like you said. It almost seems faster to change to parameterized queries. Of course one database structure I have to import to won't open in Access because of a weird error and Open Office can't get the character limits right so I know the data field specifications won't be right for that one. Plus, I'd rather do it completely correct instead of trying to just patch a crappy, wrong way. If I can my hands on the exact specs for every field in that second database, I think I'll just really, really quickly switch over to parameterized queries. Thanks for the suggestion