Create SQL Database with parameters

Question

I have a code in SQL that creates a database using this command:

string query = String.Format(
            "CREATE DATABASE [{0}] ON PRIMARY (NAME = N'{0}', FILENAME = N'{1}') LOG ON (NAME = N'{0}_Log', FILENAME = N'{2}') FOR ATTACH",
            dbName, dbFile, logFile)

However, this code is exposed to SQL Injection.

Re-writing the above query with parameters did not work, and I understand that DDL commands like CREATE DATABASE does not support parameters.

What is the correct way to write this SQL command in code with dynamic parameters?

I would prefer not to use store procedures as it is a huge change in my code infrastructure.

Answer 1

To quote the names you need to use QUOTENAME with dynamic SQL.

const string query = @"
DECLARE @sql NVARCHAR(MAX) = N'
CREATE DATABASE ' + QUOTENAME(@dbName) + '
ON PRIMARY (NAME = ' + QUOTENAME(@dbName) + ', FILENAME = ' + @dbFile + ')
LOG ON (NAME = ' + QUOTENAME(@dbName + '_Log') + ', FILENAME = ' + @logFile + ')
FOR ATTACH;
';

EXEC sp_executesql @sql;
";
using (var conn = new SqlConnection("YourConnectionString"))
using (var comm = new SqlCommand(query, conn))
{
    comm.Parameters.Add("@dbName", SqlDbType.NVarChar, 128).Value = dbName;
    comm.Parameters.Add("@dbFile", SqlDbType.NVarChar, 260).Value = dbFile.Replace("'", "''");
    comm.Parameters.Add("@logFile", SqlDbType.NVarChar, 260).Value = logFile.Replace("'", "''");

    conn.Open();
    comm.ExecuteNonQuery();
}