t-sqlxafxpo

Transact Sql Query with double quotes and field names containing spaces?

I have the following code that produces sql with field names delimited by quotes

using DevExpress.Data.Filtering

with

CriteriaOperator criteria = StockFilter.GetCriteria();  
string sWhere = CriteriaToWhereClauseHelper.GetMsSqlWhere(criteria);

This produces SQL like the following which runs ok in SQL Server Express 2017

select * from  myextitem           
where  (isnuLL(CharIndEX(N'te', myextitem."Description"), 0) > 0)

However I want to use the [Item Number] field instead of the Description field.

When I try

select * from  myextitem           
where  (isnuLL(CharIndEX(N'test', myextitem."[Item Number]"), 0) > 0)

I get a message

Invalid column name '[Item Number]'.

I have thought of parsing the text to replace "[Item Number]" with [Item Number] which would produce this sql that works

select * from  myextitem           
where  (isnuLL(CharIndEX(N'text', myextitem.[Item Number]), 0) > 0)

but am hoping there is a more general way.

I am mindful that this code is vulnerable to SQL Injection depending on the source of the test text.

Solution

  • The CriteriaToWhereClauseHelper.GetMsSqlWhere(criteria) method has an overload which allow you to prevent the quotes.

    string sWhere = CriteriaToWhereClauseHelper.GetMsSqlWhere(criteria, setQuotedIdentifiersOff: true);

    There's another method signature which allows you to pass in your own property formatter function (Func<OperandProperty, string>).

    string sWhere = CriteriaToWhereClauseHelper.GetMsSqlWhere(criteria, (operandProperty) => operandProperty.PropertyName)