CODEWITHSUNDEEP
c#sql-servert-sqlquoted-identifier
t3chb0t
t3chb0t

Reputation: 18675

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

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

Upvotes: 1

Views: 312

Answers (1)

Luaan
Luaan

Reputation: 63732

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.

Upvotes: 2

Related Questions