January 9, 2012 at 11:44 am
I'm a bit new to this, so please forgive me if this is bogus.
It seems to me, that a simple function to correct data will completely protect against all SQL injection.
We can replace every apostrophe, single straight-quote ['] (U+0027) with an right single quotation mark [’] (U+2019) on every variable before we pass it to SQL.
@UserAddress = replace(@UserAddress, '''','’')
-- So,
-- 1313 Smith's Lane becomes 1313 Smith’s Lane
If we do this for ALL data, we can even compare passwords where people have entered apostrophes, as the data in the database, and the entered data would both have the replacement character instead of the apostrophe.
The down side is that we need to be aware of this when doing searches on the database. However, we could create a user defined function to do the same replace on the search criteria and everything would match.
Would this inconvenience be worth completely preventing what is debatably the #1 threat to SQL security? Or, am I missing something???
What other characters would we need / want to replace?
January 9, 2012 at 11:56 am
yep you are missing the #1 fix to SQL injection: using parameters instead.
generally, you never want to change users data. using it for comparisons, maybe...but even then, you want to compare datavalue to datavalue,.
if they entered it with single quotes, that's the data. aside from your address example, there are lots of situations where changing the data might break or create invalid / unexpected output for the end user...
plus, if they actually USED the right single quote in their data, you could never "undo" the replace you are talking about to get exactly what was originally entered.
for example, if i use the database to store html(some people use <a href=' instead of the standard dblquote) or vb.Net scripts(it's the comment indicator)
...the single quote is of critical importance.
using parameters for data input is the recommended way to address SQL injection.
Lowell
January 9, 2012 at 12:28 pm
It also assumes all data in the database will never be either consumed or modified by anything outside the scope of your application.
And it might not even prevent injection. If the string is run in-code as a literal instead of as a parameter, what's to stop someone from putting the ASCII code value of the apostrophe in the string? Can you count on that not bypassing your replace statement? It might be consumed in the exec command, or it might spawn a sub-exec with its own execution string. A parameterized statement CAN'T be used for injection, if it's done right. Tricks with strings simply call for more clever strings.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply