MS SQL Parameterized Database Creation

Security static code scanner was complaining that creation of a database with parameters was susceptible to SQL injection.  In theory this could be possible as the database creation script was a sql command run, the likelihood was low as the parameters were checked prior to substitution.

However a static scanner is unaware of these checks so I parameterized the creation of a database in a script.  I watched the trace of a SQL ATTACH operation run on SQL Server to extract the proper amount of quote characters.

The following is using the Dapper library for sql command parameterization and handling.

var createNewDb = $@"
declare @execstring nvarchar (max)

if ((@dbname is null or datalength(@dbname) = 0) or (@mdfPath is null or datalength(@mdfPath) = 0))
begin
	raiserror (15004,-1,-1)
end
select @execstring = 'CREATE DATABASE '
	+ quotename( @dbname , '[')
	+ ' ON PRIMARY (' +
	+ ' Name = '
	+ ''''
	+ REPLACE(@dbname,N'''',N'''''')
	+ '_Primary.mdf'
	+ ''''
	+ ', FILENAME ='
	+ ''''
	+ REPLACE(@mdfPath,N'''',N'''''')
	+ ''''
	+ ', SIZE = ' + CONVERT(varchar(50), @mdfSize) +'MB, FILEGROWTH = ' + CONVERT(varchar(50), @mdfGrowth) + 'MB)'
select @execstring = @execstring
	+ ' LOG ON ('
	+ ' Name = '
	+ ''''
	+ REPLACE(@dbname,N'''',N'''''')
	+ '_Primary.ldf'
	+ ''''
	+ ', FILENAME ='
	+ ''''
	+ REPLACE(@ldfPath ,N'''',N'''''')
	+ ''''
	+ ', SIZE = ' + CONVERT(varchar(50), @ldfSize) +'MB, FILEGROWTH = ' + CONVERT(varchar(50), @ldfGrowth) + 'MB)'
select @execstring = @execstring
EXEC (@execstring)
";

var cmd = new CommandDefinition(createNewDb, new
{
	dbName = databaseName,
	mdfPath = data.Path,
	mdfSize = data.SizeInMegabytes,
	mdfGrowth = data.GrowthInMegabytes,
	ldfPath = log.Path,
	ldfSize = log.SizeInMegabytes,
	ldfGrowth = log.GrowthInMegabytes
}, null, (int)(timeout ?? DefaultTimeout).TotalSeconds, new CommandType?());

As a script

USE [master]
DECLARE	@dbname sysname
DECLARE @mdf nvarchar(260)
DECLARE @ldf nvarchar(260)
DECLARE @mdfSize int
DECLARE @mdfGrowth int
DECLARE @ldfSize int
DECLARE @ldfGrowth int

DECLARE @defaultDbPath nvarchar(2000);
SELECT @defaultDbPath = SUBSTRING([f].[physical_name],1, LEN([f].[physical_name])-(CHARINDEX('\',REVERSE([f].[physical_name]))-1))
FROM [sys].[master_files] [f]
INNER JOIN [sys].[databases] [d]
ON d.database_id = f.database_id
WHERE [d].[name] = 'model'
AND [f].[type_desc] = 'ROWS'


SET @dbname = '_TEST_'
SET @mdf = @defaultDbPath + @dbname + '_Primary.mdf'
SET @ldf = @defaultDbPath + @dbname + '_log.ldf'
SET @mdfSize = 1000
SET @mdfGrowth = 100
SET @ldfSize = 1000
SET @ldfGrowth = 1024

declare @execstring nvarchar (max)

if ((@dbname is null or datalength(@dbname) = 0) or
	(@mdf is null or datalength(@mdf) = 0))
begin
	raiserror (15004,-1,-1)
end

-- build initial CREATE DATABASE
select @execstring = 'CREATE DATABASE '
	+ quotename( @dbname , '[')
	+ ' ON PRIMARY (' +
	+ ' Name = '
	+ ''''
	+ REPLACE(@dbname,N'''',N'''''')
	+ '_Primary.mdf'
	+ ''''
	+ ', FILENAME ='
	+ ''''
	+ REPLACE(@mdf,N'''',N'''''')
	+ ''''
	+ ', SIZE = ' + CONVERT(varchar(50), @mdfSize) +'MB, FILEGROWTH = ' + CONVERT(varchar(50), @mdfGrowth) + 'MB)'
select @execstring = @execstring
	+ ' LOG ON ('
	+ ' Name = '
	+ ''''
	+ REPLACE(@dbname,N'''',N'''''')
	+ '_Primary.ldf'
	+ ''''
	+ ', FILENAME ='
	+ ''''
	+ REPLACE(@ldf ,N'''',N'''''')
	+ ''''
	+ ', SIZE = ' + CONVERT(varchar(50), @mdfSize) +'MB, FILEGROWTH = ' + CONVERT(varchar(50), @mdfGrowth) + 'MB)'

-- note it as for attach
select @execstring = @execstring

--PRINT @execstring
EXEC (@execstring)

Hopefully it will help you out