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?
tableNamewhen you debug? seems like yourtableName-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)