Creating a database programmatically in SQL Server with parameters

Question

As of right now I have a working piece of code, that looks something like this:

string sqlCreateDBQuery = " CREATE DATABASE "
                                  + "["+databaseName+"]"
                                  + " ON PRIMARY "
                                  + " (NAME = Data, "
                                  + " FILENAME = '" + strDataPath + databaseName + ".mdf', "
                                  + " FILEGROWTH = 1MB) "
                                  + " LOG ON (NAME = Log, "
                                  + " FILENAME = '" + strLogPath + databaseName + "_log.ldf', "
                                  + " FILEGROWTH = 10%) "
                                  + " COLLATE Latin1_General_CI_AS ;";

SqlCommand command = new SqlCommand(sqlCreateDBQuery, connection);

This has, with my current understanding, the potential issue of SQL-Injection attacks and well, user input errors.

So my question is, how do I safely create a database programmatically on an SQL Server where the user is able to name the database?

I know that one should use parameterized SQL-Queries to avoid SQL-Injection attempts, but for some reason I can´t seem to figure out how to do this for creating a new database or users/logins.

I´ve also read, that there is the option to use the SQL Server Management Object API as described in this answer here: Creating a database programmatically in SQL Server

Unfortunately this is not an option for us.

As of right now I haven´t figured out how to use a parameterized Query for this task. This is what I would assume what the Code should look like to achieve this, but with no working result.

SqlCommand command = connection.CreateCommand();

command.CommandText = " CREATE DATABASE "
                              + "@dbName"
                              + " ON PRIMARY "
                              + " (NAME = Data, "
                              + " FILENAME = @dataPath, " 
                              + " FILEGROWTH = 1MB) "
                              + " LOG ON (NAME = Log, "
                              + " FILENAME = @logPath, " 
                              + " FILEGROWTH = 10%) "
                              + " COLLATE Latin1_General_CI_AS ;";

command.Parameters.AddWithValue("@dbName", StrDBName);
command.Parameters.AddWithValue("@dataPath", $"{strDataPath}{StrDBName}.mdf");
command.Parameters.AddWithValue("@logPath", $"{strLogPath}{StrDBName}_log.ldf");

I´ve also read Here that this should be possible with dynamic SQL, but sadly all my attempts either ended in Syntax errors or the parameters not being replaced by the actual values.

Am I missing something here or is this just not as trivial as I initially thought?

You can't do that, you must use dynamic sql in this case. Parameters can only be used as value placeholders in SUDI statements, you can't use them in ALTER/DROP/CREATE statements. — Igor, Mar 19 '20 at 12:24
How exactly would this look like? My attempt looked like this: https://pastebin.com/qmVJLJ9a — Dominik Gattringer, Mar 19 '20 at 12:29
Your first code fragment is dynamic sql, that is the only way to do this. You will have to manually sanitize the input strings. — Igor, Mar 19 '20 at 12:34
You could add a step to validate any strings you are using, to flag up any non-alphanumeric characters. This might need extending slightly; you might decide that a database name can allow underscores (for example), but definitely prevent any semi-colons. It's not ideal, but it's better than nothing. — Richard Hansell, Mar 19 '20 at 13:09

Creating a database programmatically in SQL Server with parameters

0 Answers0