QueryExecute adding single quotes to datetime variable

[Andrew Dixon]

Hi All,

I've got a weird issue with QueryExecute adding single quotes to a datetime variable. I have the following:

queryExecute("

SELECT col1,

col2

FROM table

WHERE col_date > :varDate",

{varDate=arguments.aDate, cfsqltype="cf_sql_datetime"}

);

This results in the varDate looking like this:

'{ts ''2015-02-13 10:10:33''}' 

when it should be:

{ts '2015-02-13 10:10:33'} 

These extra single quotes cause the query to not return any results. However if I change the queryExecute to simply use the date passed into the function directly, which I obviously don't want to do, it works fine.

Has anyone else experienced this or do I have something wrong in the above way I have used queryExecute?

Thanks.

Kind regards,

Andrew

about.me
mso - Lucee - Member

[Alex Skinner]

Try using cf_sql_date I don't think date time is a valid option

A

Sent from my phone

--
You received this message because you are subscribed to the Google Groups "Lucee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/CAG1WijX%2BAcBb_W2myyrxLtVp17ftsG-XdgdwiFHGSmuyRpa52w%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

[Andrew Dixon]

Nope same thing, now the next issue, it doesn't actually seem to matter what I set it to, I can just select any random string and it doesn't error or anything. If I do the same in <cfqueryparam> it errors, so either I'm doing it completely wrong or cfsqltype in script is broken completely.

Kind regards,

Andrew

about.me
mso - Lucee - Member

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/CAFrbJ5UFai%3DxoeG6k%2BOrzdhukx-muLV8jHexW%2BcR%2BREVXjiA4Q%40mail.gmail.com.

[Adam Cameron]

I suspect this:

{varDate=arguments.aDate, cfsqltype="cf_sql_datetime"}

Should be this:

{varDate={value=arguments.aDate, cfsqltype="cf_sql_timestamp"}}

That might not be precisely the syntax (not in a position to test), but your initial syntax to me suggests two params: one called varDate and another cfsqltype.

That said, Lucee seems to be escaping the single quotes in your varDate param value, and it should not be.

It should also be raising an exception if you pass it an incorrect number of parameters.

-- 

Adam

[Andrew Dixon]

Hi Adam,

Spot on, I just worked that out by looking at the CF11 docs (sorry Lucee docs you need examples!!!)...

Also agree that it should have errored and not just assumed it was a string and escaped it and wrapped it in single quotes. I will raise a ticket.

Kind regards,

Andrew

about.me
mso - Lucee - Member

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/ab95fdbb-275d-435b-bcd5-d520ffd4bd3e%40googlegroups.com.

[Adam Cameron]

On 9 March 2015 at 08:46, Andrew Dixon <andrew...@gmail.com> wrote:

Hi Adam,

Spot on, I just worked that out by looking at the CF11 docs (sorry Lucee docs you need examples!!!)...

Also agree that it should have errored and not just assumed it was a string and escaped it and wrapped it in single quotes. I will raise a ticket.

Cool. Post back the ticket refs if poss. I'll vote for both of those.

-- 

Adam

[Andrew Dixon]

Ticket ref #215

https://bitbucket.org/lucee/lucee/issue/215

@Adam, you said "tickets" but I'm not sure what the other would be.

Kind regards,

Andrew

about.me
mso - Lucee - Member

--

You received this message because you are subscribed to the Google Groups "Lucee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/CAFwR%2BKdkp8Ytwb7RkidUzLJg59p07Lbz0htJ4bkU3qc_3UZSqA%40mail.gmail.com.

[Adam Cameron]

On Monday, 9 March 2015 09:02:53 UTC, Andrew Dixon wrote:

Ticket ref #215

https://bitbucket.org/lucee/lucee/issue/215

@Adam, you said "tickets" but I'm not sure what the other would be.

1) that the single quotes were escaped in the parameter value

2) that the incorrect number of params did not yield an exception.

That's two different things. Both wrong.

-- 

Adam

[Andrew Dixon]

If it assumed it was a string then surely escaping the single quotes would be correct?

Kind regards,

Andrew

about.me
mso - Lucee - Member

--
You received this message because you are subscribed to the Google Groups "Lucee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/a4343b84-9848-4dc4-80e3-24f6880abee8%40googlegroups.com.

[Adam Cameron]

On Monday, 9 March 2015 10:23:18 UTC, Andrew Dixon wrote:

If it assumed it was a string then surely escaping the single quotes would be correct?

The only time one needs to escape single quotes is when the string in questions is hard-coded in the SQL statement. When it's being passed as a param, the reason for doing this escaping isn't present, so it shouldn't need to be done.

-- 

Adam

[Nando Breiter]

Or perhaps, if the correct syntax is used, the correct results are obtained.

I had a similar recent tangle with queryExecute, and I suspect that the underlying problem was that I wasn't using the correct syntax, and in my attempts to correct it, lost track of how I screwed it up. Igal tried to replicate it here:

https://gist.github.com/TwentyOneSolutions/120562d0e62c77511f1d

but could not with a modest attempt. He told me that to fix a bug, if I had indeed run across one, they would need a test case that would reliably replicate the issue. In working with Igal for over an hour to try to nail my issue down precisely and develop a test case that would replicate it, I got a taste of how difficult this can be for the Lucee dev team if we leave that up to them. On our end, developing those test cases and including them as part of a bug report is a big help.

In that gist, note that he tried 2 different syntax, queryExecute and query using an echo statement. Igal told me that under the hood, queryExecute simply calls the script version of query, which has been in use much longer and is much more reliable. He recommended that if I continued to see issues with queryExecute, I should use the script version of query until issues with queryExecute are eventually sorted out.

That said, once I had the syntax of queryExecute correct, I couldn't replicate the problem I was seeing either. Nor could I figure out how I had messed the syntax up in a way that was truncating my data. I kept running into errors indicating my syntax was wrong.d

To get the syntax correct, it may be easier to separate the components of the queryExecute call into separate variables, so that you wind up with 

sql = "

SELECT  col1,

col2

FROM table

WHERE  col_date > :varDate

        AND     something = :anotherParam

";

params = {

        varDate : { value : arguments.aDate, cfsqltype : "cf_sql_timestamp" }

        ,anotherParam : { value : arguments.anotherParam, cfsqltype : "cf_sql_varchar" }

 

}

queryExecute( sql, params );

I find that less confusing, and much easier to remember, than munging it all together.

Aria Media Sagl
Via Rompada 40
6987 Caslano
Switzerland

+41 (0)91 600 9601
+41 (0)76 303 4477 cell
skype: ariamedia

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/CAG1WijUL5jHiqJPxpAzUCOyqD7Z2wz5bLUB_Pgcy6faqe43e5Q%40mail.gmail.com.

[Andrew Dixon]

@Adam - No the escaping would be required for the data being passed to the SQL engine in the SQL statement, otherwise the SQL statement would not be valid. It doesn't matter if it is hard coded or passed in the variable, when passed to the SQL engine it would be passed as text either way. So if Lucee is assuming it is a string and adding the single quotes around it, then it must escape the single quotes inside it.

@Nando - I have raised the ticket and included an example query that will replicate the issue. I think my main issue was that it didn't error, despite the syntax being completely wrong. Like you say, using the correct syntax produces the correct result, but surely using the wrong syntax should produce an error!!!

Kind regards,

Andrew

about.me
mso - Lucee - Member

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/CAGHrs%3D-8j9JfqJXhE8BNk2rLRx41b98P635cCz5sWJomtCL7Wg%40mail.gmail.com.

[Adam Cameron]

On 9 March 2015 at 12:20, Andrew Dixon <andrew...@gmail.com> wrote:

@Adam - No the escaping would be required for the data being passed to the SQL engine in the SQL statement, otherwise the SQL statement would not be valid. It doesn't matter if it is hard coded or passed in the variable, when passed to the SQL engine it would be passed as text either way. So if Lucee is assuming it is a string and adding the single quotes around it, then it must escape the single quotes inside it.

Parameter value are *not* passed with the SQL statement. This is the whole reason for using parameters: separating them from the SQL.

It's not just some fancy way of doing a "safe guarded" variable substitution. The parameter values are sent to the DB engine separately. What's sent works rather like how you use queryExecute(): SQL string is one argument, params are a separate argument. The param values are not put pack into the SQL string.

If you have this:
SQL:
SELECT * FROM t WHERE c = ?

param:

1

Then what's sent to the DB is not:

SELECT * FROM t WHERE c = 1

It's:

SQL:

SELECT * FROM t WHERE c = ?

params:

1

With strings, escaping single quotes is only relevant when string data values are embedded in the SQL, because inline strings need a delimiter: for SQL it's a single quote. If the data also has single quotes in it: it needs to be escaped. But when the string is sent as a param, as it's not embedded in the SQL statement, it doesn't need the single-quote delims, so it doesn't need escaping, eg:

SQL:
SELECT * FROM t WHERE c = ?

(note it's not '?', it's just ?)

param:

rabbit

This does not get sent as 

SELECT * FROM t WHERE c = 'rabbit'

or even

SELECT * FROM t WHERE c = '?'

params:

rabbit

The SQL statement is sent as is, and the params include a string "rabbit". When the SQL is executed the statement is compiled, then the "rabbit" is passed to the compiled statement as an argument. It never exists "inline" in the SQL statement.

Or at least this is the understanding I have been labouring under for a decade or so..?

-- 

Adam

[Nando Breiter]

@Nando - I have raised the ticket and included an example query that will replicate the issue. I think my main issue was that it didn't error, despite the syntax being completely wrong. Like you say, using the correct syntax produces the correct result, but surely using the wrong syntax should produce an error!!!

Using a wrong syntax within queryExecute() does produce an error, in lots of cases. I've run into some of them. Apparently what happened is that queryExecute() accepted what you passed into it, and processed your date as if it was a string, ignoring the cfsqltype parameter.

If I have a correctly formed parameter and try to use cfsqltype="cf_sql_datetime", I get an error: invalid CF SQL Type [DATETIME]

[Andrew Dixon]

Ok fair enough, wasn't aware of that, thanks for the explanation.

Kind regards,

Andrew

about.me
mso - Lucee - Member

--

You received this message because you are subscribed to the Google Groups "Lucee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/CAFwR%2BKcsXRk%2B%2Bc-ibdTrqHgU908KX%2BqacTRuNpRveJcLjqK48w%40mail.gmail.com.

[Andrew Dixon]

Yep, but still it should have errored in my case as well as the syntax wasn't correct.

Kind regards,

Andrew

about.me
mso - Lucee - Member

--
You received this message because you are subscribed to the Google Groups "Lucee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/CAGHrs%3D-zCQYknwa60TY5yL0jw8ETiJ9yTqOvwEaFQh2pCaRhLg%40mail.gmail.com.