This is a bit of a rant, so please ignore it you’re looking for technical info.
There’s been a fair bit of news on SQL injection in the last week or so. Mainly cause some people figured out a way to automate the exploit.
What scares me if the widespread lack of knowledge of SQL injection. I’m fairly active on a couple of the SQL forums and on monday this week there were 2 posts by people who have had their databases hacked via a SQL injection exploit.
If this was a new exploit, I wouldn’t be so disappointed, but it’s not. SQL injection’s been around for years. I first read about it in 2001 when I started in web development.
So, why 7 years later are people still being hit with it? Why does a quick google search turn up a number of web sites with entire queries in the url? Sites including some government organisations, a couple of police departments, online stores and the coast guard (No, I’m not posting links. Search for yourself if you’re curious)
Completely preventing SQL injection is not hard. If web pages call the database using properly parameterised calls to stored procedures then SQL injection cannot be done. Set minimum permissions in the database for the web user and it’s even more secure.
So, why is it that so many sites, new and old, are still vulnerable?
Edit: For some in-depth info on preventing SQL injection, see this blog post on Technet
Hi, I don’t disagree that many sites are poorly constructed to handle basic SQL injection attacks. However the problem with the latest epidemic was due to the maliciously ingenious use of a cast operator:
DECLARE @S VARBINARY(4000);SET @S=CAST(0x440045004300
4C00410052004500200040005400200076006100720063006800610072
00280032003500350029002C0040004300200076006100720063006800
610072002800320035003500290020004400450043004C004100520045…
This resolves to SQL syntax, but none of the usual flags are triggerd. Quite sneaky.
-D
(Editor: Cleaned up the sql syntax to be legible)
Indeed and I do not ever recommend that key word filtering is used as the sole method of preventing injection. As this recent attack showed, there are ways and means around it.
Proper use of parametrised queries and procedures and proper security permissions would have stopped even this attack.
With parametrised queries, the additional add-on of SQL does not get executed as a separate query, buy gets treated as a string literal. Worst case, you end up with SQL code been stored in the table, but it won’t get executed.
If the web user has no rights to the base tables, an attack like the recent one would have failed to even retrieve the list of tables (on SQL 2005). It certainly would have failed to execute the update.
I’ll write another article on injection sometime soon, this time focused on how to prevent it from working.
p.s. Nice to see you here Dion.