Create SQL Database with parameters-CodePudding

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.

CodePudding user response：

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, 255).Value = dbFile.Replace("'", "''");
    comm.Parameters.Add("@logFile", SqlDbType.NVarChar, 255).Value = logFile.Replace("'", "''");

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

CodePudding user response：

Consider this working TSQL,

DECLARE @sql NVARCHAR(MAX) = N'
DECLARE @quotedDatabaseName sysname = QUOTENAME(@databaseName);
DECLARE @logName sysname = FORMATMESSAGE(''%s_Log'', @name);
DECLARE @sql NVARCHAR(MAX) = FORMATMESSAGE(
    N''CREATE DATABASE %s
        ON PRIMARY (NAME = N''''%s'''', FILENAME = N''''%s'''')
        LOG ON (NAME = N''''%s'''', FILENAME = N''''%s'''') FOR ATTACH;'',
    @quotedDatabaseName,
    @name,
    @dataFileName,
    @logName,
    @logFileName);

EXEC sp_executesql @sql, N'''';'

DECLARE @params NVARCHAR(4000) = N'@databaseName sysname,
@name sysname,
@dataFileName NVARCHAR(260),
@logFileName NVARCHAR(260)';

EXEC sp_executesql @sql,
@params,
@databaseName = '...',
@name = '...',
@dataFileName = '...,
@logFileName = '...';

It is not perfect but, it is a significant improvement in terms of protection. Caution is still advised.

The parameters for the dynamic query are passed in via sp_executesql, see here for discussion of why that is a good idea. This is how the ORM of your choice will send queries to SQL Server.

The code is transcribed so apologies for errata.