ExecuteSql to create table in EF Core

Question

I have a method to create tables at runtime:

    public async Task CreateTable(string tableName, IEnumerable<DatabaseColumn> columns)
    {
        var columnsString = string.Join(", ", columns.Select(GetColumnString));
        /// for example columnsString is:
        /// [field_1] NVARCHAR(50),
        /// [field_2] INT,
        /// [field_3] INT

        await _db.ExecuteSqlAsync($"CREATE TABLE [{tableName}] ({columnsString})");
    }

After execute this method I get error:

Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (5ms) [Parameters=[@p0='?' (Size = 4000), @p1='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [@p0] (@p1)

Api request return that message:
{
  "message": "Incorrect syntax near '@p1'."
}

I understand that p1 is incorrect because this parameter is usually used to pass values.

Question: How to pass field params to create a table in ExecuteSqlAsync?

Of course I can use ExecuteSqlRawAsync, but in my case I get a warning:

Warning EF1002  Method 'ExecuteSqlRawAsync' inserts interpolated strings directly into the SQL, without any protection against SQL injection. Consider using 'ExecuteSqlAsync' instead, which protects against SQL injection, or make sure that the value is sanitized and suppress the warning.

Answer 1

ExecuteSqlAsync automatically parameterizes your query if you pass in a FormattableString using $"". This is normally a good thing.

But CREATE TABLE and other DDL are not parameterizable. You need to use ExecuteSqlRawAsync which doesn't do that and just takes a normal string.

await _db.ExecuteSqlRawAsync($"CREATE TABLE [{tableName}] ({columnsString})");

You need to make sure to properly escape or whitelist the names if they are coming from user input, otherwise you leave yourself open to dangerous SQL injection.