1

The app is .Net Core 3.1, using EF Core 3 and a SQL Server on Azure

So I'm trying to create a table in my database with data from the client and I want to be safe from SQL injection.

So far I've tried with using a FormattableString which according to the doc is safe against SQL injection:

public Task CreateTableAsync(string tableName, JSchema tableSchema)
    {
        return TransactionAsync(async () =>
        {
            // Get the fields for the table creation
            var fields = await ParseJSchemaForCreationAsync(tableSchema);

            var sql = "CREATE TABLE {0} (";

            var sqlParams = new List<object>
            {
                tableName
            };

            var first = true;
            var count = 1;

            foreach (var entry in fields)
            {
                // entry.Value is from code so it's safe againt injection
                sql += first ? $"{{{count}}} {entry.Value}" : $", {{{count}}} {entry.Value}";
                first = false;

                sqlParams.Add(entry.Key);
                count++;
            }

            sql += ");";

            var safe = FormattableStringFactory.Create(sql, sqlParams.ToArray());

            // Create the table
            await _dbContext.Database.ExecuteSqlInterpolatedAsync(safe);
        });
    }

But I've an error : "incorrect syntax near '@p0'", despite it seems to generate a valid query (when getting the value of sage I got : "CREATE TABLE sqlDataSourceGrainTest (Id uniqueidentifier NOT NULL PRIMARY KEY, CreatedAt datetime2(0), UpdatedAt datetimeoffset(3), FirstName nvarchar(4000), Birthdate date, XId uniqueidentifier, Datetime datetime2(0), Timestamp timestamp, Height decimal(18, 2), HasFoodAllergy bit, Age bigint);"

I've also tried to use with SQLParameter (which I prefer):

public Task CreateTableAsync(string tableName, JSchema tableSchema)
    {
        return TransactionAsync(async () =>
        {
            // Get the fields for the table creation
            var fields = await ParseJSchemaForCreationAsync(tableSchema);

            var sql = "CREATE TABLE @tableName (";

            var sqlParams = new List<SqlParameter>()
            {
                new SqlParameter
                {
                    ParameterName = "tableName",
                    Value = tableName,
                }
            };

            var first = true;

            foreach (var entry in fields)
            {
                sql += first ? $"@{entry.Key} {entry.Value}" : $", @{entry.Key} {entry.Value}";
                first = false;

                var sqlParam = new SqlParameter
                {
                    ParameterName = $"{entry.Key}",
                    Value = entry.Key
                };

                sqlParams.Add(sqlParam);
            }

            sql += ");";

            // Create the table
            await _dbContext.Database.ExecuteSqlRawAsync(sql, sqlParams);
        });
    }

But I've have the error : "Incorrect syntax near '@tableName'."

Can someone help me to find the correct way to create the table? Is there any rules that say we can't use sql with parameters to create the table.

I've will also need to made update of the table, insert records and update records

Thanks

Edit: Based on answers from DavidG and HoneyBadger I've tried:

public Task CreateTableAsync(string tableName, JSchema tableSchema)
    {
        return TransactionAsync(async () =>
        {
            // Get the fields for the table creation
            var fields = await ParseJSchemaForCreationAsync(tableSchema);
            var sql = $"CREATE TABLE {tableName} (";
            var sqlParams = new List<SqlParameter>();
            var first = true;

            foreach (var entry in fields)
            {
                sql += first ? $"@{entry.Key} {entry.Value}" : $", @{entry.Key} {entry.Value}";
                first = false;

                var sqlParam = new SqlParameter
                {
                    ParameterName = $"{entry.Key}",
                    Value = entry.Key
                };

                sqlParams.Add(sqlParam);
            }

            sql += ");";

            // Create the table
            await _dbContext.Database.ExecuteSqlRawAsync(sql, sqlParams);
        });
    }

But now the error is "Incorrect syntax near '@id'" which is the name of the first parameter

SQL I see: CREATE TABLE tableTests ( @Id uniqueidentifier NOT NULL PRIMARY KEY, @CreatedAt datetime2(0), @UpdatedAt datetimeoffset(3), @FirstName nvarchar(4000), @Birthdate date, @XId uniqueidentifier, @Datetime datetime2(0), @Timestamp timestamp, @Height decimal(18, 2), @HasFoodAllergy bit, @Age bigint);"

Can't I use any parameters at all in the creation of a table?

7
  • 2
    SQL Server doesn't allow you to use parameters for table names. Commented Dec 16, 2020 at 9:59
  • Are you sure the error you posted for the edit is accurate? Commented Dec 16, 2020 at 10:12
  • what value do you get for tableName when you debug? seems like your tableName-parameter is "@tableName" - or this is an "old" error. If you pass @tableName, it won't work. you cannot use any sql-parameter as tableName. You need to pass the actual tablename (without sql parameter) Commented Dec 16, 2020 at 10:16
  • Sorry, missed my edit, the error is now "Incorrect syntax near '@id". I added the SQL in the question Commented Dec 16, 2020 at 10:27
  • T-SQL QuoteName Commented Dec 16, 2020 at 10:43

1 Answer 1

2

Object names can't be parameters, so you'll need to use concatenation:

var sql = "CREATE TABLE " + tableName + " (";

I hope your users aren't involved in deciding the name of the table, so sql injection shouldn't be an issue.

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

4 Comments

I hope your users aren't involved in deciding the name of the table, so sql injection shouldn't be an issue a simple regex should do this. even if users are not involved, validating before concatenating is not wrong here I guess
@MatthiasBurger, indeed, some sanity checks are advisable.
Edited my question :) Yes the users can choose the tables names but there is some validations
😱 allowing your app users to specify SQL tables names is just nuts!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.