Why isn't my sql command accepting a quoted identifier for a table name?

Question

I wanted to make my sql query bullet proof and found this question about sanitizing table/column names.

I followed the advices but somehow it doesn't work as expected.

My table name is foo.Bar so I passed it to the CommandBuilder's QuoteIdentifier method and it gave me [foo.Bar] back.

When I call (string)cmd.ExecuteScalar(); it complains that this table doesn't exist. So I tried to initially define the table name as [foo].[Bar]. In this case QuoteIdentifier creates [[foo]].[Bar]]] which also doesn't work.

The only thing that works is when I specify just [foo].[Bar] without using the QuoteIdentifier method.

At this point I'm wondering whether I can use it at all to protect my queries? It is not of much use and tuning the quoted query like another answer suggests also doesn't seem to be right.

Is there anything I can do to protect the query and make it work and accept the name [foo].[Bar] without breaking it?

This is how I'm creating the query:

cmd.CommandText = string.Format(
    "SELECT {0} FROM {1} WHERE {2} = '{3}'{4}",
    ...,
    sanitizedTableName, // only [foo].[Bar] works -- [[foo]].[Bar]]] and [foo.Bar] fail
    ...,
    ...,
    ...);

Have you tried SqlParameter? https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter(v=vs.110).aspx — , Jan 20 '16 at 11:29
@buffjape I was going to use it for the `{3}` value later. Can I also use it to insert the table name? I have never tried to insert anything else. I guess that's the only usage I've ever seen... I'll try it and we'll see in a moment ;-) — t3chb0t, Jan 20 '16 at 11:32
You don't need the quotations if you use a parametrized query. Just add a parameter and specify the type of the column in the DB. — Gnqz, Jan 20 '16 at 11:33
@Gnqz it's not the parameters that I'm having problems with (I'm going to use a parameter later). Currently it doesn't like the table name but I'll try to use them also for its name like buffjape suggested. I didn't know I could use them also for table names. — t3chb0t, Jan 20 '16 at 11:34
I've tried to use `cmd.Parameters.AddWithValue` for the `{1}` parameter that I named `@TableName` but it complains _Must declare the table variable "@TableName"_. Apparently it cannot be used to insert table names. — t3chb0t, Jan 20 '16 at 11:47
The problem is, your table's name *isn't* `foo.bar`. You table's name is `bar` and its in the `foo` schema. Trying to sanitize it using functions that only expect to deal with a table name are going to fail because they're going to assume the `.` is part of the name and not a separator. — Damien_The_Unbeliever, Jan 20 '16 at 11:52
@Damien_The_Unbeliever ok, that makes sense. Then I'll quote each part separately and concatenate them. — t3chb0t, Jan 20 '16 at 11:56

score 2 · Accepted Answer · answered Jan 20 '16 at 11:55

The problem is that the name of the table is bar, not foo.bar. You're using a multi-part identifier - foo is the schema, and bar is the table name.

To get proper quoting, you need to quote each identifier separately:

QuoteIdentifier(schemaName) + "." + QuoteIdentifier(tableName)

The reason for this should be obvious - it's perfectly valid for a quoted identifier to use . as part of the name. That's kind of the whole point of having quoting in the first place.

Why isn't my sql command accepting a quoted identifier for a table name?

1 Answers1