Search code examples
c#sqlsqlparameter

Incorrect syntax near @para and must declare the table variable @para SQL c#

I load my INFORMATION_SCHEMA.TABLES into a listbox. I let the user select table, then in the listbox next to it the columns for this table appear. I simply get the talbe name like this:

tableName = lbTables.GetItemText(lbTables.SelectedItem);

now to get the columns, this works:

SqlCommand allTables = new SqlCommand("SELECT * FROM "+tableName+";", conn);

and this doesn't work:

SqlCommand allTables = new SqlCommand("SELECT * FROM @0;", conn);
allTables.Parameters.AddWithValue("@0", tableName);

Secondly I try to insert a new row into the same table:

SqlCommand comm = new SqlCommand("INSERT INTO [dbo.][@0] (@1) VALUES ('@2')",conn);
comm.Parameters.AddWithValue("@0", tableName);
comm.Parameters.AddWithValue("@1", columnName);
comm.Parameters.AddWithValue("@2", someValue);

Again, hardcoriding tableName and 'columnName' works.

For the tableName I've tried this:

SqlParameter typeTable = comm.Parameters.AddWithValue("@0", tableName);
typeTable.SqlDbType = SqlDbType.Structured;
typeTable.TypeName = "dbo." + tableName;

But that gave me the error: Must declare the table variable @0 I'm kind of spending to much time on this problem right now and I have not find the right answer yet, i'll keep searching though...

EDIT: After some comments it turns out that I was not aware that parameters can only be used for VALUES in INSERRT/UPDATE and WHERE clauses. So I am looking for a 'SQL injection safe' method to dynamically choose a table and column.

Solution

  • Table names need to be static. You cannot pass a variable as the table name in SQL:

    Use this:

    SqlCommand comm = new SqlCommand(String.Format("INSERT INTO [dbo][{0}] ({1}) VALUES ('@val')", tableName, columnName),conn);
comm.Parameters.AddWithValue("@val", someValue);

    In SQL, you can achieve this with sp_executesql:

    declare @query nvarchar(4000) = 'INSERT INTO [dbo][' + @tableName + '] (' + @columnName + ') VALUES (@p)'
declare @Params nvarchar(500) = '@v nvarchar(10)'
execute dbo.sp_executesql @query, @params, @p = 'someValue'

    But you need to be very carefull with this and check your tableName and columnName variables to avoid SQL Injection.