Use SqlCommand in .net to modify database using parameters

Question

I have to modify a SQL Server database dynamically, depending on some inputs. What I wanted to do is to generate the modification commands and then execute them. And, to avoid SQL injection, I wanted to use parameters. But it doesn't work.

Here is a snippet of my PoC code (just a rough thing):

        public static void ModifyDatabase(string name)
    {
        string connectionString = "Server=localhost; Database=DEV_Tests; Integrated Security=True;";
        var sqlCommand = "CREATE SCHEMA @schemaname";
        
        using (var connection = new SqlConnection(connectionString))
        using (var command = new SqlCommand(sqlCommand, connection))
        {
            connection.Open();
            command.Parameters.AddWithValue("@schemaname", name);

            command.ExecuteNonQuery();
            connection.Close();
        }
    }

This throws a syntax error. But this one works:

        public static void ModifyDatabase()
    {
        string connectionString = "Server=localhost; Database=DEV_Tests; Integrated Security=True;";
        var sqlCommand = "CREATE SCHEMA AAAA";
        using (var connection = new SqlConnection(connectionString))
        using (var command = new SqlCommand(sqlCommand, connection))
        {
            connection.Open();

            command.ExecuteNonQuery();
            connection.Close();
        }
    }

So, or I'm doing something wrong (could be), or I can't use parameters with the "create schema". In fact, in the second one (the one without params), if I add a parameter, without using it, fails too. Is that so? If we can't use params here, what would be the best way to sanitize the input? Trim it and remove conflictive parameters?

Thanks.

Charlieface · Accepted Answer · 2021-03-23 20:46:35Z

2

You cannot use a parameter in a CREATE statement. Instead, use QUOTENAME and sp_executesql to execute it dynamically:

DECLARE @sql nvarchar(max) = N'CREATE SCHEMA ' + QUOTENAME(@schema);
EXEC sp_executesql @sql;

If you have any other parameters that can be used in a query, then you can pass that through to sp_executesql also.

You should pass the schema name through as nvarchar(128), which is the same sysname:

command.Parameters.Add("@schemaname", SqlDbType.NVarChar, 128).Value = name;

Another little tip: to avoid getting in the habit of concatenating/injecting SQL, I always declare my query variable as const string query.

edited Mar 23, 2021 at 20:46

answered Mar 23, 2021 at 20:45

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use SqlCommand in .net to modify database using parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related