SQL Injection and What to Do About It (July 18, 2008)
Chat Topic: SQL Injection and What to Do About It
Date: Friday, July 18, 2008
Please note: Portions of this transcript have been edited for clairty.
Daniel (Moderator):
Hello everyone-- thanks for coming to our SQL Injection chat today. Let's get started with our chat. Before we begin, though, I'd like to have our Experts introduce themselves and then they'll get started answering your questions.
Introductions:
Tom - Microsoft (Expert):
My name is Tom Christian and I'm from the IIS/ASP.NET Support team. I own the http://blogs.msdn.com/tom blog.
Matt Weber [MSFT] (Expert):
My name is Matt Weber and I'm a developer on the Problem Response team investigating and responding to Windows error reports. I previously supported SQL Server connectivity.
Terrell [MSFT] (Expert):
I am Terrell Andrews. I am a Support Escalations Engineer with the SQL Developer team. My group deals with coding to the SQL Server back end from Visual Studio and various scripting languages.
Joe Stagner [MSFT] (Expert):
I'm Joe Stagner - a Security Geek on the Web Tools and Platforms Team.
Start of chat:
Joe Stagner [MSFT] (Expert):
Q: Hello Gent's. With the rash of SQL Injection attacks lately (especially from the query string), how can I as a QA developer help patch old ASP.NET websites without having to re-write several hundred lines? Will the Global.asax be a place to start patching?
A: Simply modify the SELECT statement to use a PARAMTERIZED Query and MOST of the issues go away (you should still do follow-up to insure that there are no remaining issues.)
Joe Stagner [MSFT] (Expert):
Q: Few weeks back we had an SQL injection attack. Due to this some java script code got added to text fields in the database. We managed to restore from the back up. My questions is...did those javascript execute any kind of code ..??
A: That's a hard question to answer. JavaScript can't directly run code on your server, but it might call a service that runs code or it might trick the user into taking an acting that writes code.
Joe Stagner [MSFT] (Expert):
Q: In developing db-driven enterprise software, what would you construe as more important, a data layer that protect all-and-sundry from writing sql injectable code OR spending hours educating developers on how to write such code ?
A: I don't know that I'd say one was more important than the other - but if I HAD to choose, I'd take my most knowledgeable Security Dev and have them build a bulletproof DAL and then do code audits to make SURE none of the devs were bypassing the DAL when accessing the DB.
Tom - Microsoft (Expert):
Q: How can I test for SQL Injection?
A: There are a few different ways that you can test for this. One of the best ways is to use a tool that can check your code for you. There are a few of these mentioned on Joe's blog at: http://www.misfitgeek.com/Tools+To+Block+And+Eradicate+SQL+Injection.aspx
Joe Stagner [MSFT] (Expert):
Q: How can I test for SQL Injection?
A: That's a very broad question. The best way is to use a 3rd party penetration tool. A simple way (that doesn't ALWAYS work) is to include "or 1=1 --" in your input somewhere and see if you get an error - if NOT, you have issues. (That is in NO WAY a comprehensive test - just a quickie)
Joe Stagner [MSFT] (Expert):
Q: to continue my question, what if it is not viable to do both. From a time and resource stand point? Would you rather have a few experts working on mitigating sql injection attacks or have everyone be a "jack" at it?
A: Yes - if you can only pick one - have your Gurus build a secure DAL.
Joe Stagner [MSFT] (Expert):
A: I think it's great and as many useful tools that you can put in your toolbox, the less likely that a vulnerability will slip through.
Tom - Microsoft (Expert):
Q: But can anything be done in SQL server, not .NET... so no matter where the query comes from, maybe malicious queries could be detected? Queries could come from ASP classic, ASP.NET, windows apps, etc.
A: SQL Server doesn't have much in it that will protect you from this, but you can run the SQL profiler to check for malicious activity and act accordingly.
Terrell [MSFT] (Expert):
Q: Can anything be done at the server level to prevent or help minimize the issue?
A: Yes. Only give users the minimal rights they need. This depends on your dev infrastructure. If you have the power to restrict access to your developers. Say if you can only allow your devs to access tables via Stored procs or views.
Joe Stagner [MSFT] (Expert):
Q: If I disable validation on the ASPX but still use "sandbox" technique? Or its not recommended?
A: It depends on what you mean by a "sandbox" approach. It is often necessary to disable validation but when you do you must aggressively filter and escape userinput.
Matt Weber [MSFT] (Expert):
Q: If I disable validation on the ASPX but still use "sandbox" technique? Or its not recommended?
A: Any user-supplied data coming from an ASP.NET -- or any other -- application should be validated in some fashion before being used in a SQL query. Ideally, queries should be parameterized such that users are unable to directly manipulate the query text that reaches the database. Sandboxing is of limited use when attempting to mitigate SQL injection attacks.
Joe Stagner [MSFT] (Expert):
Q: Hello Gent's. With the rash of SQL Injection attacks lately (especially from the query string), how can I as a QA developer help patch old ASP.NET websites without having to re-write several hundred lines? Will the Global.asax be a place to start patching?
A: Global.asax should only matter if you have code implemented there.
Joe Stagner [MSFT] (Expert):
Q: or 1=1 --" : I use this as my first test usually.
A: It's a great place to start - but don't depend on it solely.
Joe Stagner [MSFT] (Expert):
Q: Fair enough. along the same lines, one recommended mitigation strategy is the use of parameterized queries. How strong is this as a barrier? Is it possible to run sql injection attacks inspite of parameterization?
A: It is theoretically possible under specific conditions much the same as using stored procedures does not guarantee that no SQL injections are possible in your application. You still especially have to worry about down-stream data based attacks. But using Stored Procs or Parameterized queries is MUCH MUCH better.
Joe Stagner [MSFT] (Expert):
Q: cmd.parameters.AddWithValue("@p1", {object})
A: Yep - that's what I'm talking about
Tom - Microsoft (Expert):
Q: Rob: How can you do this if you have a shopping cart that is writing to the database
A: It doesn't really matter what it is that is trying to write to the database, the procedure is the same. Either use a parameterized query or use a stored procedure. Those are your best ways to interact with the database layer.
Joe Stagner [MSFT] (Expert):
Q: what are your thoughts on urlscan 3.0 beta?
A: URLSCAN 3 is super !
Joe Stagner [MSFT] (Expert):
Q: sandbox - parameterized query - typesafe or etc.$)
A: Definitely parameterized query - you should NEVER write "static" SQL and execute it in your application uless there is TRULY no other way.
Joe Stagner [MSFT] (Expert):
Q: or 1=1 --" : I use this as my first test usually.
A: Yep - but don't depend on it only !
Joe Stagner [MSFT] (Expert):
Q: But does it work better than URLSCAN 2, which didn't have much granularity to it.
A: Yes - still, great for a free tool
Tom - Microsoft (Expert):
Q: Does URLSCAN 3 work on all servers 2000 2003 2008
A: According to http://www.microsoft.com/downloads/details.aspx?FamilyId=EE41818F-3363-4E24-9940-321603531989&displaylang=en it is supported on Windows Server 2003, 2008, XP and Vista
Tom - Microsoft (Expert):
Q: how about if you are a hosting company with hundreds to thousands of clients who may or may not have development resources anymore and they don't know how to handle the issue, is there any non code editing thing that can be done to help.
A: Urlscan is your best bet for a non code editing situation.
Joe Stagner [MSFT] (Expert):
Q: how about if you are a hosting company with hundreds to thousands of clients who may or may not have development resources anymore and they don't know how to handle the issue, is there any non code editing thing that can be done to help.
A: If they are writing code and induce vulnerabilities then those vulnerabilities can do whatever the account the code runs as is permissioned to do. In a hosting environment, make sure you have GREAT virus and rootkit detection. Account isolation, and isolate critical users to a dedicated subnet.
Tom - Microsoft (Expert):
Q: <continued> because the problem is much worse on old ASP based sites as compared to .Net..
A: The problem can be bad either way. But Urlscan is a good solution that doesn't involve changing code so it is perfect for the hosting company to use. You can get lots of details on Urlscan 3 at http://weblogs.asp.net/steveschofield/archive/2008/06/24/urlscan-3-0-help-with-sql-injection-attacks.aspx
Joe Stagner [MSFT] (Expert):
Q: U suggest to use stored procedures with some parameters collection stored in my App? I didn't understand what is it mean NEVER? Why not?
A: Yes - you should never use static SQL unless there is no other way to solve your problem.
Tom - Microsoft (Expert):
Q: But does it work better than URLSCAN 2, which didn't have much granularity to it.
A: I think you will really like Urlscan 3. One of the main things that was done is to make it easier to handle things like SQL Injection. Be sure to check out the blog link for urlscan 3 - help with sql injection.
Joe Stagner [MSFT] (Expert):
Q: I use either parameterized queries or stored procedures everywhere ..but there are still ways by which sql injection can occur.. can you mention a few of them?
A: Sure - one of the most common is a downstream attack where the injected SQL code is already in the database (entered in another application or another part of the application and not filtered before storage because the developer assumes it will be checked before it gets used. The dev writing the display logic assumes the code was filtered on input and doesn't check it be he/she assumes that because it's already in the database it should be ok.... Make sense ?
Mike Zawadzki (Expert):
Q: there are places where we are *forced* to use the execute statement in sql.. one place is for rethrowing exceptions in sql 2005 using custom errors. hopefully, sql 2008 will have something in place which avoids us from having to use the execute statement. Like: http://sqlblog.com/blogs/roman_rehak/archive/2007/12/01/how-to-rethrow-errors-in-t-sql.aspx
A: Are you referring to using EXEC (stmt) or dynamic execution?
Mike Zawadzki (Expert):
Q: Mike, EXEC
A: I think I am misunderstanding you. Just to be clear. In a batch you can call a stored procedure with the following as an example "exec sp_who" or you could use the following: set @x = 'exec sp_who'; exec (@x) If you are just calling the stored procedure there should not be a problem except from the starting batch, is this what you are referring too?
Joe Stagner [MSFT] (Expert):
Q: do you guys use ssl encryption between your db and webserver or something like IPSEC?
A: In hyper secure scenarios - YES - ABSOLUTLY. The FBI Cyber Crime survey suggests that 50% of all successful hacking activity comes from inside your organization. If I have other machines on the same sub-net as my SQL boxes then a simple packet sniffer/collector can collect TONS of hack worthy data !!
Daniel (Moderator):
Well we're out of time for today's chat. Thank you very much to all of our guests who joined us today as well as to our Experts for answering so many great questions. Have a great day everyone!