help.net


Musing on .Net

News

Windows Phone Apps

FotoBank


FotoIreland

Windows 8 Apps




hit counters






Open source CMS


My blog

Irish blogs

Locations of visitors to this page Get Chitika eMiniMalls

.Net useful

Blogs I read

PocketPC

SQL

Usability

How to protect yourself against the latest SQL injection

Now it's time to rejoice and be positive. Following my different posts about the latest SQL injection attacks, I got all sort of comments.

Roughly half of the commenters saying I am a moron, why you don't go back to school and protect your databases, it's all your fault, don't blame OUR Microsoft! 

The other half being more positive and more indulgent and trying to know more about the threat, even thanking me for breaking the news.

I feel myself closer to the latter crowd, because those are the guys who share the pain, and like to see some answers, rather than being just plain negative.

Yes I admit I was strong in the headline of my first post on the subject, but the message was towards Microsoft, to make them moving a bit. Yes I am some time opinionated but it's always for the good cause. And I got the reaction I wanted from Microsoft, with different replies, all positive.

Now back to the problem of SQL injection. I think that the new threat posed by the trojan recently is that it used a flaw in Internet Explorer to be able to expose a cookie to a request stream and execute some SQL command. Basically what the T-SQL do is reading the sysobjects table from the master table, and paste inormation in a temporary table, using this infoemation to get all the details about tables and text fields, then populating the tables randomly with some javascript script.

Really nasty! Anyway the only way I was able to block it was to create a low level access user for my front end web application, and revoking EXECUTE rights for this particular user  at the server level.

Not enough, you need also to remove the EXECUTE rights at the Master table level. I tried to remove the full access to the Master table for this user, but for some unknown reasons, ADO.NET needs to have some access to this table, probably in the case like mine where I use ExecuteScalar.

Another approach would be to use a Windows user running on low level, but it didn't work for me, the connection was refused with this message:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.  If anyone knows more about that let me know?

So far it had workswell for me and I encourage everyone to do the same. Now I wish that in the future, Microsoft release a version of SQL Server with all the dangerous things disabled by default.

After all, they did that with Windows Server 2008, and it has been an enormous relief to manage a server where you know that everything you don't need is locked down.

Finally even if I have some good knowledge of SQL Server, I wish they could make the security settings a tad more easier to manage and understand. I like the granualrity approach, but it's a little bit complicated for some basc stuff (try to understand the difference between Grant and With Grant!)

UPDATE: As one reader points out you need to check or add some attributes in your config files, like forcing cookies to be in httponly mode.

Check Liam post for the details and more security locks

 

Read more:

- REVOKE command http://msdn.microsoft.com/en-us/library/ms187719(SQL.90).aspx

- Protect from SQL injection http://msdn.microsoft.com/en-us/library/ms998271.aspx

- Vulnerability in Internet Explorer Could Allow Remote Code Execution  by Roger Halbheer

http://blogs.technet.com/rhalbheer/archive/2008/12/13/vulnerability-in-internet-explorer-could-allow-remote-code-execution.aspx

Comments

pbz said:

"I confirm that my code is doing all the checks required on the frontend. Because it's a cookie exploiting a flaw in IE, the execution of the code is totally transparent, nothing really I can do in my code. However securing the basics of SQL Server like the EXEC rights on the Master table has been successful. Paschal" 

If this a new threat that can bypass all my parameterized queries I'm all ears! If that's the case  a simple proof of concept would be helpful. What you're doing here, however, is securing SQL Server rather than fixing your application. Don't get me wrong, nothing wrong in securing your database, but you should fix your application first. If you can prove us that's fixed and the trojan successfully bypasses your checks please let us know with an example. Thanks.

# December 21, 2008 3:37 PM

Jonathan said:

How exactly are they able to execute the SQL code on your server?

# December 22, 2008 8:16 AM