How to use parameters in SQL query with column name

Question

I have the following SQL query which takes the header from a file and creates a column with the same name as the header:

SqlCommand createtable = new SqlCommand("CREATE TABLE " + tbXLSTableName.Text + " (" + dc.ColumnName + " varchar(MAX))", myConnection);

It is open for an SQL injection attack so I decided to use parameters like this:

string strCreateTable = "CREATE TABLE @TableNameCreateXLS (" + dc.ColumnName + " varchar(MAX))";

SqlCommand createtable = new SqlCommand(strCreateTable, myConnection);      
createtable.Parameters.AddWithValue("TableNameCreateXLS", tbXLSTableName.Text);

dc is a DataColumn object.

I am getting the following error:

Incorrect syntax near @TableNameCreateXLS

How can I resolve the error?

As I said in a comment on your previous (now-deleted) question: "you usually can't parameterize table and column names." — Jon Skeet
– Jon Skeet, Commented Jun 20, 2014 at 15:50
It was deleted by accident and I lost the page to undelete. So use the old command that I was using? — Si8
– Si8, Commented Jun 20, 2014 at 15:50
Yes. But validate the heck out of the value first - ideally restricting it really heavily. (In cases where you want to select from a number of tables, use a whitelist - this doesn't work here, of course.) — Jon Skeet
– Jon Skeet, Commented Jun 20, 2014 at 15:51

Habib · Accepted Answer · 2014-06-20 15:57:55Z

7

You can't use Parameters for Table Name and Column Names, but you can use SqlCommandBuilder.QuoteIdentifier method to escape their values. Like:

SqlCommandBuilder sqlBuilder = new SqlCommandBuilder();
string columnName = dc.ColumnName;//"somecolumn"
string TableNameCreateXLS = "someTable";
string escapedColumnName = sqlBuilder.QuoteIdentifier(columnName);
string escpaedTableName = sqlBuilder.QuoteIdentifier(TableNameCreateXLS);

string strCreateTable = string.Format("CREATE TABLE {0} ({1} varchar(MAX))",escpaedTableName, escapedColumnName);

answered Jun 20, 2014 at 15:57

Habib

224k30 gold badges420 silver badges446 bronze badges

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

Comments

cost · Accepted Answer · 2014-06-20 15:51:46Z

2

Unfortunately, you cannot use parameters for table names. I usually see this being done when people want to query a dynamic table, and in those cases I say the safe thing to do is query the table of table names using a parameter, but in your case it doesn't work.

So aside from sanitizing the table name input yourself, I don't think you have any in-SQL way to do this safely. If creating a table dynamically is unavoidable like this, then at least be sure to sanitize the first.

answered Jun 20, 2014 at 15:51

cost

4,5009 gold badges51 silver badges85 bronze badges

3 Comments

Si8 Over a year ago

What would be the best way to sanitize the textbox for the table name. Any SQL method?

cost Over a year ago

@SiKni8 Admittedly I don't know of a good way to do it manually. The almost universal advice is to use parameterized queries, but here it really doesn't work. At the very least remove ' (apostrophes). Are you sure you want to give the user direct access, with a textbox, to create any table they want?

Si8 Over a year ago

Well the good news is the application will be used internally within our group. The bad news is, anyone can be evil at any time ;). I only allow ALPHANUMERIC for the textbox so I should be fine.

Habib · Accepted Answer · 2014-06-20 16:06:27Z

0

Use this and see if it works:

createtable.Parameters.AddWithValue("@TableNameCreateXLS", tbXLSTableName.Text);

edited Jun 20, 2014 at 16:06

Habib

224k30 gold badges420 silver badges446 bronze badges

answered Jun 20, 2014 at 15:51

Dean.DePue

1,0131 gold badge21 silver badges47 bronze badges

Collectives™ on Stack Overflow

How to use parameters in SQL query with column name

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related