February 22, 2010 at 12:46 pm
Looking for possible occurrences of UPDATE, DELETE, ALTER, etc. in a query parameter could get very lengthy and won't cater for new features released in later versions of SQL.
You can more easily avoid SQL injection attacks, catering for all sorts of possible syntax, by checking for and replacing any single quotes (') in the parameter with two single quotes ('') ... this may be necessary anyway to cater for situations where the parameter may legitimately include them, like surnames with apostrophes, otherwise the SQL statement may fail anyway.
By replacing any single quotes with two single quotes (in any/all parameter values) you can prevent a malicious statement being entered as a parameter. It's not a perfect solution, but probably better than trying to cover all the potentially harmful SQL syntax.
🙂
Chris
February 22, 2010 at 12:52 pm
Greg Snidow (2/22/2010)
That's pretty scary. I'm thinking an IF statement looking for key words like 'SET','UPDATE','DROP','ALTER', etc might work to thwart that kind of attemp. But if the user does not have permissions to drop or update a table, he or she would get an error anyway. Right?
That's a start, but it's a long climb to try to deal with every possible permutation someone might throw at you. There have been tricks played with encoding those strings so you can't find them, etc...
If you were in .NET, the SQLParameters there provide some level of security if used correctly, but the ADO code you have may not lend itself to it. You oculd then look at creating well-formatted stored procs that can help as well. RBarryYoung just posted a couple of links to his blog dealing with the injection aspect (and how you might use user input safely). His blog is at
Look over the Dynamic SQL topics.
As to security - yes if the user has no permissions, they would get an error. Tightening up the security in any way you can is certainly a good thing. Of course - if you don't log those kinds of failures/look for them, someone can keep "hammering" on the system until they find the weak spot.
Ultimately - it comes down to Mr Myagi's motto (from the Karate Kid of course):
Best defense - no be there.
In other words - don't leave an opening. Most people don't have any intention to do something malicious, but then again, it only takes one.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply