Have you seen this weird QoQ Error [ColdFusion]?

Question

I have confirmed that the first query works as expected

<cfquery name="validation_info" dbtype="query">
    select      shipViaName,TOTALSHIPRATE
    from        s_c_c.qShipCalc
    WHERE       sku in (#preserveSingleQuotes(validate)#)
</cfquery>
<cfquery name="validation_info2" dbtype="query">
    select      TOTALSHIPRATE
    from        validation_info
    WHERE       shipViaName = "FedEx 3 Day"
</cfquery>

But on the second query, I get this error:

Encountered "shipViaName. Incorrect conditional expression, Incorrect conditional expression, Lexical error at line 0, column 0. Encountered: "\"" (34), after : ""

It says the error is happening on this line:

<cfquery name="validation_info2" dbtype="query">

I was also getting this error when I had the two queries combined into one.

Update from comments:

Dump of the validation_info query:

SHIPVIANAME TOTALSHIPRATE

1 | FedEx Ground | 11.9  ||
2 | FedEx 3 Day  | 22.99 || 
3 | FedEx 2 Day  | 26.99 || 
4 | FedEx 1 Day  | 44.55 || 
5 | FedEx Ground | 0     || 
6 | FedEx 3 Day  | 23.63 || 
7 | FedEx 2 Day  | 26.71 || 
8 | FedEx 1 Day  | 41.9  ||

Do you have a sample of what is in validate? And a sample of what is in validation_info? — Blaise Swanwick, Oct 23 '12 at 17:43
Have you tried single quotes in the second query? As an aside, I would recommend using cfqueryparam in your QoQ's. Since your first query uses a list of values, tack on the `list` attribute ie `WHERE SKU IN ( ) `. — Leigh, Oct 23 '12 at 17:47
validation_info SHIPVIANAME TOTALSHIPRATE 1 | FedEx Ground | 11.9 || 2 | FedEx 3 Day | 22.99 || 3 | FedEx 2 Day | 26.99 || 4 | FedEx 1 Day | 44.55 || 5 | FedEx Ground | 0 || 6 | FedEx 3 Day |23.63 || 7 | FedEx 2 Day | 26.71 || 8 | FedEx 1 Day | 41.9 || — Travis Heeter, Oct 23 '12 at 18:28
(Edit) Typically the jdbc convention is to use single (not double) quotes. But using `cfqueryparam`, instead of messing with quotes, avoids the whole issues altogether. It handles everything for you. — Leigh, Oct 23 '12 at 18:48
@Leigh Can you post this as an answer so I can give you credit? Also, for some reason cfqueryparam wasn't working, I've used it a million times before, so I don't know what was going on, probably some weird scoping issue, but that's why I didn't use cfqueryparam, and I was too under the gun to figure it out. — Travis Heeter, Oct 27 '12 at 15:38
@TravisHeeter - Appreciated, but feel free to accept KRC's answer as we both came to the same conclusion around the same time :) Just glad you figured out the problem. — Leigh, Oct 27 '12 at 16:28

score 7 · Accepted Answer · answered Oct 23 '12 at 18:53

7

You need to use single quotes instead of double quotes for your string in your second SQL query.

answered Oct 23 '12 at 18:53

K_Cruz

729
6
17

Of course, I missed seeing this suggestion in the comments of the post. – K_Cruz Oct 23 '12 at 18:56
Haha, no worries. If I had wanted to make it an answer I would have :) – Leigh Oct 23 '12 at 19:25

John · Answer 2 · 2012-10-23T18:24:47.837

0

I'd recommend using cfqueryparam for your variables, try this

<cfquery name="validation_info" dbtype="query">
    SELECT      
        shipViaName,
        TOTALSHIPRATE
    FROM        
        s_c_c.qShipCalc
    WHERE       
        sku IN (<cfqueryparam value="#validate#" cfsqltype="CF_SQL_INTEGER" list="yes" /> )
</cfquery>

edited Oct 23 '12 at 18:24

answered Oct 23 '12 at 17:56

John

13,197
7
51
101

*hopefully preserveSingleQuotes doesn't return a list* [PreserveSingleQuotes](http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-6cbf.html) stops CF from automatically protecting the query by doubling single quotes. So using cfqueryparam instead is definitely the way to go. Even with QoQ's. – Leigh Oct 23 '12 at 18:18
@Leigh I didn't realize that it was a function build into CF. – John Oct 23 '12 at 18:23
Yep. But it is not used much anymore because it presents a sql injection risk in *database* queries. – Leigh Oct 23 '12 at 18:34

Have you seen this weird QoQ Error [ColdFusion]?

2 Answers2