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

Question

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.

You can't do `select * from @someparam` unless `@someparam` is a table variable, you cannot substitute a variable for a table name or for a list of columns in an insert. — Ben Robinson, Dec 09 '14 at 09:51
Parameters could be used only to represent VALUES in INSERT/UPDATE statements or WHERE clause not for field names or table names. For these cases, if you have strict control on the table and field names (not inputted by the user) then you could use a string concatenation — Steve, Dec 09 '14 at 10:02
ok, I get the point about the parameters are only for values and insert/update, (clear explanation @Steve and @Ben) The table and columns are not user input, however I might need that later so that is why I made the decision to do it here.. The question still remains on, how do I do it then... — CularBytes, Dec 09 '14 at 10:13

score 1 · Accepted Answer · answered Dec 09 '14 at 10:56

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.

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

1 Answers1