Skip to main content
Ben Nadel at dev.Objective() 2015 (Bloomington, MN) with: Jessica Kennedy
Ben Nadel at dev.Objective() 2015 (Bloomington, MN) with: Jessica Kennedy

ColdFusion 2021 MySQL Query No Longer Returning Dates As Dates

By
Published in — Comments (7)

Yesterday, I went to my site and was greeted with a Bad Request - Request Too Long error. IIS was rejecting my request because my HTTP request headers exceeded 16Kb in size. When I looked at my cookies, I had dozens upon dozens of cookies that should not have been there. And, when inspecting the cookies, they all had expiration dates in 2092. After a few hours of digging, I discovered that my MySQL database queries were no longer returning date/time values as date/time values. Instead, the ColdFusion query object was presenting them as strings; which was breaking my CFCookie settings in a very subtle way.

I am not sure. But, I believe this is related to recent attempts at fixing the "Value Class java.time.LocalDateTime Cannot Be Converted To A Date In ColdFusion" error in the MySQL database driver (see discussion on ColdFusion forum). It seems that Adobe's "fix" for this might have been to just stop returning database dates as dates. See also the Adobe ColdFusion bug tracker.

Let's dig into why this is a problem. Here's my replication demo running on the latest Adobe ColdFusion 2021 (to match my server). Note that I am using MySQL to generate a date. Then, I'm using that date to set a cookie expiration date:

<!--- Generate a MySQL date value 6-hours in the future. --->
<cfquery name="results">
	SELECT
		( UTC_TIMESTAMP() + INTERVAL 6 HOUR ) AS expiresAt
	;
</cfquery>

<cfdump var="#results#" />


<!--- Test the data-type that is coming out of the query object. --->

<p>
	getMetadata( results.expiresAt ).getName():
</p>

<cfdump var="#getMetadata( results.expiresAt ).getName()#" />


<!--- Test the data-type after we try to perform some data mutation. --->

<p>
	getMetadata( results.expiresAt.add( "d", 0 ) ).getName():
</p>

<cfdump var="#getMetadata( results.expiresAt.add( "d", 0 ) ).getName()#" />


<!--- Try to get milliseconds (WILL BREAK WITH NEW CHANGE!). --->
<cftry>
	<cfdump var="#results.expiresAt.getTime()#" />
	<cfcatch>
		<p>
			<cfdump var="#cfcatch.message#" />
		</p>
	</cfcatch>
</cftry>


<!--- Try setting a cookie with the given expiresAt date (WILL BREAK IN SUBTLE WAY). --->
<cfset cookie.poc_test = {
	value: "test",
	expires: results.expiresAt,
	preserveCase: true,
	httpOnly: true
} />

When we run this Adobe ColdFusion 2021 code, we get the following output:

ColdFusion is no longer returning database dates as dates, which is breaking their consumption in CFCookie expires settings.

Several things to see:

  1. The value coming out of the ColdFusion query object is of type, java.lang.String.

  2. We can call date member methods (.add()) on that string, and ColdFusion will magically make it work. And, the resulting value of the date method call is of type, coldfusion.runtime.OleDateTime.

  3. We cannot call Java Date member methods (.getTime()) on that string because the given value isn't actually a date, it's a string.

  4. If we try to pass the string value for the date (2024-03-08 18:15:54) into the cookie.expires setting, we can see that the HTTP response shows the designated expiration date as 26-Mar-2092 03:30:01.

So, it doesn't explode; but, it definitely breaks existing logic in a really subtle way.

The good news (if you can call it that) is that I can fix this one issue by just converting the string value to an actual date value (using .add( "d", 0 )). Then, when I pass the "Date-ified" value into the cookie, it will work as expected.

To be clear, this is not a bug in the way cookies are handled. I consider this a bug in the way dates are returned from the database. Though, I suppose that is a matter of opinion.

Want to use code from this post? Check out the license.

Short link: https://bennadel.com/4608

Reader Comments

15,848 Comments

In my proof-of-concept server above, I tried to set the JVM argument to turn off this behavior:

-Dcoldfusion.jdbc.mysql.datetime.str=false

But, it made no difference. I believe, in the latest ColdFusion update, this behavior is no longer controlled by a JVM argument and is now just the way things are.

238 Comments

Whoa, this is a really important and difficult to find bug! Appreciate the heads-up. I haven't noticed any issues since my upgrade to 2021, but I will now be on the lookout 👀

Fortunately, there's a workaround 🙏

Great find!

15,848 Comments

@Chris,

Yeah, it's a strange one. In my case, when you go to leave a comment on the blog, I set an "edit token" cookie so that you have 6-hours to come back-in and edit your comment. However, if you look at your cookies, you'll probably see that most of them are still there (from the past 5-months or so). Since the expires isn't working. But, I just deleted an update. In fact, I'm hoping that this comment's edit token is set to expire (hit "submit" while holding breath).

15,848 Comments

Woot woot! Looking at the Set-Cookie HTTP header from my previous comment, I get:

editToken_1890={ my long token value };
    Max-Age=21600;
    Expires=Fri, 08-Mar-2024 07:35:35 GMT;
    Path=/;
    HttpOnly;
    SameSite=Lax

Phew - set to expire 08-Mar-2024 - that's today 💪

On the server-side, when I get the token out of the database (to set the cookie) value, I'm just adding 0-days to the value:

/**
* I return the given comment edit token query row as a data-transfer object (DTO).
*/
private struct function asDTO(
	required query tokensQuery,
	numeric rowIndex = 1
	) {

	// HACKY FIX: ColdFusion 2021.0.12 is now returning database dates as STRINGS. As
	// such, we have to use dateAdd() to convert the string to an actual date object.
	// Without this, the cookie expires settings won't work.
	return({
		id: tokensQuery.id[ rowIndex ],
		entryID: tokensQuery.entryID[ rowIndex ],
		pendingCommentID: tokensQuery.pendingCommentID[ rowIndex ],
		commentID: tokensQuery.commentID[ rowIndex ],

		createdAt: dateAdd( "d", 0, tokensQuery.createdAt[ rowIndex ] ),
		expiresAt: dateAdd( "d", 0, tokensQuery.expiresAt[ rowIndex ] ),

		value: tokensQuery.value[ rowIndex ]
	});

}

Super lame - but at least it works.

10 Comments

Hi Ben,

I don't have the environment to test, but couldn't you just use ParseDateTime instead of dateAdd to make sure the variable is treated as a date?

15,848 Comments

@Tony,

Yes, I think that would have worked as well. I was just picking something that would automagically cast it from String to Date. I didn't think too deeply beyond that.

Post A Comment — I'd Love To Hear From You!

Post a Comment →

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
— Ben Nadel