The most critical character to replace if you are executing SQL queries instead of stored procedures (though if you do an EXEC within a stored procedure, you're right back to the same problem) or whatever equivalent your DBMS may offer, is the string delimiter for your DBMS. This is to prevent SQL injection attacks, which can be a far greater threat than being able to alter page HTML and add clientside script.
You don't even need to worry about HTML tags going in if the data going in isn't being retrieved later for display in a web page.
wait what was that last part... the way it is worded confused me. So with access it is not neccessary to use the replace ' with ''? I have been doing that lol (but I only use access data bases for some very very minor data, only 1 or 2 mdbs floating around).
Access appears to consider both 'string' and "string" as strings.
However, neither "string' nor 'string" is a string on its own.
Matching only, no mixing allowed.
So, if you pick one that you will use as your string delimiter, that's the one you need to escape, and you can safely ignore the other. Of course, if you're building them out of string literals in VB(script/.NET/whatever), you'll be using " for strings in the code, so you'd probably want to use ' for strings in the SQL (which would mean you'd need to escape ' in the user input).
I've written a little function that should take care of my "bad character" woes:
reduce=replace(replace(replace(what, "&", "&amp;"), "<", "&lt;"), ">", "&gt;")
However I also use Access databases so if you wouldn't mind clearing up those delimeters for me as well.
I know that for a simple SQL injection you can use 'or to make it screw up but that's pretty much all I know.
Last edited by David Harrison; 05-20-2004 at 05:54 PM.
Every fight is a food fight when you’re a cannibal.
well if you do not use an escape character, you could always just run a
replace(string, "'", "&asciivaluefor'here;")
that way when you display the html it would not get yucky. I not sure if you could do a just put in a raw vb ascii value, because that would still put a loose ' in the db right?
Escaping it is the cheapest way to deal with it and won't affect what's stored in the database.
Replacing it with the ASCII code increases the storage amount (unlikely to be a significant issue) and will cause issues if you're pulling the data for purposes other than generating HTML. Note that the OP has never indicated that the details being input to the db will ever be pulled to be presented in HTML format. (Granted, he hasn't ever stated they wouldn't either.)
Right, if you try to do a replace with the VB character code, you'll end up with the exact same string value after the replace as before.
To deal with characters that would screw up HTML, you can replace them after you pull from the db. (Aren't there server.[whatever] functions to do this?) At any rate, what is best depends on one's needs and one's resources, though circumstances exist where there's not a significant difference in performance or convenience between the various alternatives.