Sep 7, 2009

Looking For More Security Vulnerabilities in Database Code

A few days back I read a blog from Kimberly L. Tripp, one of the great teachers on SQL.Server. She opened the question on ways to identify vulnerable code written inside stored procedures. Even though she came up with dynamic SQL and execution context the discussion went on one direction only on dynamic SQL.
She proposed two main areas for search:

  CASE

      WHEN sm.definition LIKE '%EXEC (%' OR sm.definition LIKE '%EXEC(%' THEN
'WARNING: code contains EXEC'

      WHEN sm.definition LIKE '%EXECUTE (%' OR sm.definition LIKE '%EXECUTE(%' THEN
'WARNING: code contains EXECUTE'


When discussion proceeded two major additions came up: One on handling white spaces and commented lines and the other on sp_ExecuteSQL

Does that mean if we take care of the above vulnerabilities our code is secure? Unfortunately, we have many other possibilities to look into.

In fact, when I saw Kimerly’s blog, I thought of writing some additional vulnerabilities there as a response, but a few factors prevented me: 1. My answer seems to be slightly larger for a response. 2. The original post was made months ago. So, here I have the things I found.
I suggest you all to read her blog and the responses.
Looking for security vulnerabilities in database code

Well SQL Server 2000 prior to sp3 had an undocumented extended stored procedure called xp_ExecResultset which allowed the SQL statement to be constructed using another query. In later service pack this was replaced with a TSQL stored procedure. With sp3, another procedure named sp_ExecResultSet too was added (this did nothing more than calling the previous procedure).

Even though the extended procedure was removed, it is not impossible to use the same dll used for xp_ExecResultset in SQL Server 2000 to create a new extended procedure.

Of course no one can take the same dll and attach to SQL Server 2005 due to architectural changes, but it does not mean that no one can attach a dll and call dynamic SQL. Actually calling dynamic SQL is simpler than before due to the introduction of CLR integration. That means someone can write code that simply runs ad-hoc query (or similar) statement based on the parameters passed.

In SQL Server 2005, there is a new stored procedure named sp_sqlExec which accepts SQL statement/variable as parameter and simply executes it.

Additionally there is another undocumented procedure sp_execute which accepts @handle (int) as a parameter. This refers to a cached plan in memory prepared by sp_prepare (another undocumented extended procedure) That means someone could call sp_execute and pass the handle of a dynamic statement (which would have been executed before too).

Interested in finding more security vulnerabilities? I see a lot of other places like xp_cmdshell, sqlcmd, jobs (which can run ActiveX & WMI scripts, OS commands and TSQL statements) and alerts (where you have a chance to run powershell script too.

So, be wise and do a security audit on the system.


Share this post :



No comments:

Post a Comment