Dynamic SQL and Executing SQL Text - 'Incorrect Syntax Near Column' and 'Unclosed Quotation Mark After the Character String'

Question

I am having a problem figuring out where I have a syntax error and unclosed quotation marks.

When I run this query:

    query.CommandText = "DECLARE @sql varchar(max);"
    query.CommandText = query.CommandText + " DECLARE @cond varchar(max);"
    query.CommandText = query.CommandText + " SET @cond = replace(replace(@search, '''', ''''''),' ','"" and ""');"
    query.CommandText = query.CommandText + " SET @sql = 'SELECT distinct datawarehouse.dbo.orderformdump.itemno, basedescription,upc,CAST((SELECT [UNITPRICE] FROM PPPLTD.dbo.[ICPRICP] WHERE [ITEMNO] = replace([DataWarehouse].[dbo].[ORDERFORMDUMP].[ITEMNO],''-'','''') AND [PRICELIST] = (select top 1 priclist from PPPLTD.dbo.ARCUS where IDCUST = (select top 1 CUSTID from PPPLTD.dbo.WEBLOGINACCESS where [USER] = ''" + Session("Username") + "'')) and [CURRENCY] = ''CDN'' and DPRICETYPE = 1) AS DECIMAL(18,2)),caseqty, qty AS userquantity FROM [DataWarehouse].[dbo].[ORDERFORMDUMP] LEFT JOIN pppltd.dbo.weboeordd ON pppltd.dbo.WEBOEORDD.ITEMNO = REPLACE(datawarehouse.dbo.ORDERFORMDUMP.ITEMNO,''-'','''') and orduniq not in (select orduniq from pppltd.dbo.weboeordsubmit) and WEBOEORDD.ORDUNIQ in (select orduniq from pppltd.dbo.weboeordh where [user] = ''" + Session("Username") + "'') where (allowinbc = ''Yes'' or allowinab = ''Yes'') '"
    query.CommandText = query.CommandText + " SET @sql = @sql + 'and (contains((basedescription, category, datawarehouse.dbo.orderformdump.description, itembrand, itemgroup, itemname, datawarehouse.dbo.orderformdump.itemno, itemsubtype, itemtype, subcat, upc), ''""' + @cond + '""'') '"
    query.CommandText = query.CommandText + " SET @sql = @sql + 'or (select top 1 1 from PPPLTD.dbo.ICITEMO where OPTFIELD like ''UPC%'' and VALUE like ''%' + replace(@search, '''', '''''') + '%'''"
    query.CommandText = query.CommandText + " SET @sql = @sql + 'and ITEMNO = DataWarehouse.dbo.ORDERFORMDUMP.itemno) is not null) order by DATAWAREHOUSE.dbo.ORDERFORMDUMP.BASEDESCRIPTION'"
    query.CommandText = query.CommandText + " EXECUTE (@sql)"

The query runs fine however, when I add the line OR REPLACE(ITEMBRAND, '''''', '''') LIKE ''%' + @search + '%''

It gives me the errors:

Incorrect syntax near 'UPC'.

and

Unclosed quotation mark after the character string 'and ITEMNO = DataWarehouse.dbo.ORDERFORMDUMP.itemno) is not null) order by DATAWAREHOUSE.dbo.ORDERFORMDUMP.BASEDESCRIPTION'.

Here is the full query that's giving me trouble:

    query.CommandText = "DECLARE @sql varchar(max);"
    query.CommandText = query.CommandText + " DECLARE @cond varchar(max);"
    query.CommandText = query.CommandText + " SET @cond = replace(replace(@search, '''', ''''''),' ','"" and ""');"
    query.CommandText = query.CommandText + " SET @sql = 'SELECT distinct datawarehouse.dbo.orderformdump.itemno, basedescription,upc,CAST((SELECT [UNITPRICE] FROM PPPLTD.dbo.[ICPRICP] WHERE [ITEMNO] = replace([DataWarehouse].[dbo].[ORDERFORMDUMP].[ITEMNO],''-'','''') AND [PRICELIST] = (select top 1 priclist from PPPLTD.dbo.ARCUS where IDCUST = (select top 1 CUSTID from PPPLTD.dbo.WEBLOGINACCESS where [USER] = ''" + Session("Username") + "'')) and [CURRENCY] = ''CDN'' and DPRICETYPE = 1) AS DECIMAL(18,2)),caseqty, qty AS userquantity FROM [DataWarehouse].[dbo].[ORDERFORMDUMP] LEFT JOIN pppltd.dbo.weboeordd ON pppltd.dbo.WEBOEORDD.ITEMNO = REPLACE(datawarehouse.dbo.ORDERFORMDUMP.ITEMNO,''-'','''') and orduniq not in (select orduniq from pppltd.dbo.weboeordsubmit) and WEBOEORDD.ORDUNIQ in (select orduniq from pppltd.dbo.weboeordh where [user] = ''" + Session("Username") + "'') where (allowinbc = ''Yes'' or allowinab = ''Yes'') '"
    query.CommandText = query.CommandText + " SET @sql = @sql + 'and (contains((basedescription, category, datawarehouse.dbo.orderformdump.description, itembrand, itemgroup, itemname, datawarehouse.dbo.orderformdump.itemno, itemsubtype, itemtype, subcat, upc), ''""' + @cond + '""'') OR REPLACE(ITEMBRAND, '''''', '''') LIKE ''%' + @search + '%'' '"
    query.CommandText = query.CommandText + " SET @sql = @sql + 'or (select top 1 1 from PPPLTD.dbo.ICITEMO where OPTFIELD like ''UPC%'' and VALUE like ''%' + replace(@search, '''', '''''') + '%'''"
    query.CommandText = query.CommandText + " SET @sql = @sql + 'and ITEMNO = DataWarehouse.dbo.ORDERFORMDUMP.itemno) is not null) order by DATAWAREHOUSE.dbo.ORDERFORMDUMP.BASEDESCRIPTION'"
    query.CommandText = query.CommandText + " EXECUTE (@sql)"

I've tried printing the statment and I still can't find where my error is.

I'm adding the new line of SQL at the end of the 6th line.

Thank you.

Write out @SQL before you execute it and post the generated SQL. My guess is you'll see the error then. — xQbert, Mar 14 '16 at 17:53
Your sql strings are inside of single quotes, but then you escape from those with double quotes to do your variable concatenation. why are you using double quotes when the string is enclosed with single quotes? For instance, it should look like `where [USER] = ''' + Session("Username") + '''))` That's two single quotes to make a single-quote-literal, then a third single quote to end the string and concat your var, then the three single quotes and back into your sql.. — JNevill, Mar 14 '16 at 18:14
@JNevill I've used double quotes because I was executing the query inside an aspx file. I've updated the post to reflect this. — thetipsyhacker, Mar 14 '16 at 18:19
Nothing you're doing requires dynamic sql. Much less double dynamic sql (once in vb and again in SQL). You should use paramterized sql instead. Not only does it actually stop sql injection problems you can do searches for single quotes too. See http://stackoverflow.com/q/15537368/119477 — Conrad Frix, Mar 14 '16 at 18:29
@ConradFrix I'd definitely like to know how to do that in this case. Do you know any good articles talking about parameterized sql? — thetipsyhacker, Mar 14 '16 at 18:33
Plus 1 for "Double Dynamic SQL" I was trying to think of a good term but my mind drew a blank. — JNevill, Mar 14 '16 at 18:39
@bkhosh2 [Here's a SO example question](http://stackoverflow.com/a/542542/119477) The question also includes [a link to a longer discussion about dynamic sql in general](http://www.sommarskog.se/dynamic_sql.html) — Conrad Frix, Mar 14 '16 at 18:53

score 1 · Accepted Answer · answered Mar 14 '16 at 18:20

1

Try changing REPLACE(ITEMBRAND, '''''', '''') to REPLACE(ITEMBRAND, '''''''', '''')

Added another ', so instead of REPLACE(ITEMBRAND ,''' ,'') being returned, it returns REPLACE(ITEMBRAND ,'''' ,'')

answered Mar 14 '16 at 18:20

Doolius

854
6
18

Dynamic SQL and Executing SQL Text - 'Incorrect Syntax Near Column' and 'Unclosed Quotation Mark After the Character String'

1 Answers1