CODEWITHSUNDEEP
c#sqlsql-serverdatacolumn
Si8
Si8

Reputation: 9225

How to use parameters in SQL query with column name

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?

Upvotes: 1

Views: 2134

Answers (3)

Dean.DePue
Dean.DePue

Reputation: 1013

Use this and see if it works: 

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

Upvotes: 0

Habib
Habib

Reputation: 223247

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);

Upvotes: 7

cost
cost

Reputation: 4480

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.

Upvotes: 2

Related Questions