Skip to content

Instantly share code, notes, and snippets.

@zippy1981
Created October 12, 2012 11:55
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save zippy1981/3878873 to your computer and use it in GitHub Desktop.
Save zippy1981/3878873 to your computer and use it in GitHub Desktop.
Code Review request for dynamic SQL that drops a database

I have this C# function in some SQLCLR that will drop any given database. Naturally, where and how I call it raises security concerns. My question is, is there any way to do an injection attack with that code? I have to rely on EXEC() and QUOTENAME() because you can't paramaterize the database name in T-SQL.

/// <summary>
/// Drop the db if it exists.
/// </summary>
private static void DropDbIfExists(SqlConnection cn, string dbName)
{
using (var cmd = cn.CreateCommand())
{
cn.Open();
cmd.CommandText =
@"IF EXISTS(SELECT name FROM sys.databases WHERE name = @dbName)
BEGIN
RAISERROR('DROPPING Database ''%s''', 0, 1, @dbname);
DECLARE @strSql NVARCHAR(MAX);
SET @strSql = '
ALTER DATABASE ' + QUOTENAME(@dbName) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE ' + QUOTENAME(@dbName) + ';'
EXEC(@strSql);
END";
cmd.Parameters.AddWithValue("@dbName", dbName);
SqlContext.Pipe.ExecuteAndSend(cmd);
cn.Close();
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment