Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Issues

Select view

Select search mode

 
3 of 3

Broken date handling + passing odbcdate or odbcdatetime to queryExecute() gets treated as varchar

Fixed

Description

Lucee treats all dates as dateTime and, when passing dates to queryExecute, it includes timezone information which nearly caused a $750,000 bad trade in a financial services application recently. Passing in '2021-09-30' ended up in the database as '2021-09-29 21:00:00' as the lucee instance is EST and the server is UTC. This by itself is terrible, as I only wanted date information, not time, and certainly not the date/time "corrected" for me.

To get around this, I tried passing in myDate: createODBCDate('2021-09-30') as a parameter, but lucee treats it as a varchar, surrounding it with single quotes. The odbc date is created correctly as {d '2021-09-30'}, but it ends up in the SQL as WHERE myDate = '{d '2021-09-30'}' which, of course, breaks the query. I had to fall back to embedding the raw variable in the SQL which is bad for both security and performance, though the latter may be negligible.

Environment

None

Details

Assignee

Reporter

Priority

New Issue warning screen

Before you create a new Issue, please post to the mailing list first https://dev.lucee.org

Once the issue has been verified, one of the Lucee team will ask you to file an issue

Affects versions

Created 26 October 2021 at 11:47
Updated 23 December 2021 at 13:48
Resolved 23 December 2021 at 13:48

Activity

Show:

John Wilson23 December 2021 at 13:46

You can cancel this issue, though I still think it would be better if dates in lucee were not datetimes. They should be separate in Lucee as they are in reality. Dates cover all times in a day. Datetimes are far more specific and need to be treated as such. 2021-12-23 00:00:00 != 2021-12-23. The first is exactly midnight, not any time during that date.

Pothys - MitrahSoft9 December 2021 at 09:53

I've checked your test code and it throws error like Conversion failed when converting date and/or time from character string in both lucee and ACF. using cfsqltype in param solves this isssue.

 

And when the type of the column is DateTime and the timezone of the datasource/lucee are different means lucee includes the timezone information with the given date on the database. Could please check your datasource timezone and lucee server timezone?

John Wilson8 December 2021 at 14:26
Edited

BTW, if I had my choice, date objects would be date objects and datetime objects would be datetime objects. This problem is caused by lucee converting a date to a datetime which adds the timezone info. So glad I caught this issue before it bit us, but it takes a lot of extra work to get around.

This code:

Yields

John Wilson8 December 2021 at 14:24

I missed it. See below. I just threw this together on lucee 5.3.8

This code:

yields

Pothys - MitrahSoft8 December 2021 at 14:14

Did you see my above comment? using type in param solved your issue? If no means, please share the test file to reproduce the issue. It will improve the status of the ticket

Flag notifications