2

I don't know if this is really needed... but I would like write as robust code as possible, so:

What SqlDbType should be used when creating a table? SqlDbType.NChar? Or would there be a problem with a Unicode table name? Or perhaps one of the Var types because the length is unknown? I'm used to thinking about these types in the context of a database row, not table, so I'm not sure what should be used here.

EDIT

For example, a line like:

sqlCommand.Parameters.Add(new SqlParameter(parameterName,SqlDbType.NChar){ Value = value });

for a CREATE TABLE command.

TaW
  • 53,122
  • 8
  • 69
  • 111
ispiro
  • 26,556
  • 38
  • 136
  • 291
  • Which value that you try to parameterize exactly? – Soner Gönül May 26 '15 at 11:44
  • @SonerGönül The value is a simple string, e.g. `table1`. I'm just asking for the correct **SqlDbType**. – ispiro May 26 '15 at 11:46
  • 2
    The documentation on SqlDbType says it applies to fields, not tables. – Dan Bracuk May 26 '15 at 11:50
  • @DanBracuk I'm not sure [they meant](https://msdn.microsoft.com/en-us/library/system.data.sqldbtype%28v=vs.110%29.aspx) to exclude tables. It's simply unusual to be used in that way. But if so - Then how _do_ I parametrize a table name? – ispiro May 26 '15 at 11:54

2 Answers2

3

You can't parameterize your table name or column names or any other database objects. You can only parameterize your values.

You can still using string concatenation it but getting your table name as an input is too risky in my opinion. You should either perform very stringent validation on the table name before putting it into the SQL, or have a white-listed set of valid table names, in order to avoid SQL Injection attacks.

Other than that, parameterized statements is only for DML Statements not DDL statements.

Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
  • Thank. But I've updated the question. For example for creating a table. – ispiro May 26 '15 at 11:50
  • @ispiro Even if you find a _correct_ type for your column name (which looks like `nvarchar(128) NOT NULL` based on your comment) you should not do that in my opinion. This is **not** what parameterized queries are for. – Soner Gönül May 26 '15 at 12:01
  • I completely agree about not letting a user choose a table name. However, I would like to use this for avoiding **un**intentional mistakes when creating a table name. But as I said - I agree with you. – ispiro May 26 '15 at 12:04
  • 1
    My code fails with `Incorrect syntax near '@abc'.` So it seems you're right. – ispiro May 26 '15 at 13:40
2

All object names in SQL Server are of sysname datatype. Don't know if the SqlDbType contains sysname, but it is functionally identical to nvarchar(128), so you can use this.

dean
  • 9,960
  • 2
  • 25
  • 26
  • Thanks. That's probably it. (And I found [this answer](http://stackoverflow.com/a/5720277/939213) talking about it). – ispiro May 26 '15 at 11:57
  • My code fails with `Incorrect syntax near '@abc'.` So it seems it can't be done. But your answer is helpful. Thanks. – ispiro May 26 '15 at 13:39