I'm building a simple page that will add details to a Database.
I'v been told that I need to replace cetain characters when text going inside the Database.
I would like to know what characters I need to replace and how to do it?
those would stop someone from using html, and would most likely be all you would need. You might also do what this forum does and replace javascript with java script (this forum does it sometimes, or it did, but now it does want too, dono why it did but it did it so you might take it into consideration). Also a { and a } and maybe the & with & That should do it, I can't think of other vulnerabilities or annoyances off the top of my head.
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.
Originally posted by CardboardHammer ' is used by SQL Server, which is the only one I use at the moment.
And it is escaped with another ', so it would then be ''
Ah yes, I have those. I forgot, thought there was something else I was missing, though I needed to do a replace & vbcrlf or something. What about access?
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:
Code:
function reduce(what)
reduce=replace(replace(replace(what, "&", "&amp;"), "<", "&lt;"), ">", "&gt;")
end function
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.
I believe you just double up on whichever character is being used for the quote character.
'' for a ' when used in a string: 'She took the dog''s collar.'
"" for a " when used in a string: "He said,""you did what?"""
You don't want to double up the other character, though, or you'll end up with the character doubled up in the db: "She took the dog''s collar." would show ... dog''s ... in the db.
That's the way I think it is. I don't use Access with web pages, and haven't even done anything at all with it in quite a while.
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.
Reply With Quote
Bookmarks