While sanitizing user-provided data, I encountered a string that ColdFusion evaluated as a date, but SQL Server didn’t agree with due to "out of range" for the date SQL Server data type. The date was 7/22/22019
.
I was curious to see what the max limit was and discovered that ColdFusion 2016 & 2018 behave differently when validating date. Apparently 12/31/292278993
is a valid date in CF2016 & 2018, but the next year doesn’t exist in CF2016. ("1/1 - 9/8" month/days are valid in CF2016, but time ceases to exist on/after 9/10/292278993
.)
I'm not sure why these dates are considered valid. If I try to store these "dates" into either Microsoft SQL Server or MySQL, both will throw an "out of range" error. Even creating an inline java-based CFQuery throws an error. If you use CreateODBCDate() using ColdFusion 2018, it will throw a hard "uncatcheable" error which can't be prevented using try/catch.
Since upgrading to ColdFusion 2016, I've had to write my own custom validation functions to deal with the shortcomings of Adobe's changes regarding how it validates integers, URLs, email... and now dates.
Here's a quick proof-of-concept script:
TryCF.com Demo
https://trycf.com/gist/01e14657a47846bb3aa7ca88295b8832
Source
<!--- 2019-07-22 | |
Blog Post | |
https://dev.to/gamesover/coldfusion-dates-m-d-yyyyyyyyy-555h | |
GitHub Gist | |
https://gist.github.com/JamoCA/01e14657a47846bb3aa7ca88295b8832 | |
Try it at Trycf.com (or paste online at CFFiddle.org) | |
https://trycf.com/gist/01e14657a47846bb3aa7ca88295b8832 | |
Reported as "crash/data loss" CF2016 bug to Adobe (7/22/2019 3:15 PM Pacific) | |
https://tracker.adobe.com/#/view/CF-4204879 | |
---> | |
<p>During ColdFusion 2016 server-side date validation, invalid-ish futuristic years (ie, "star dates") | |
were being identified as "valid dates". (NOTE: CF2018 and Lucee don't throw errors on isDate/isValid("date") like CF2016 does, | |
but CF2018 throws an uncatchable hard error when using CreateODBCDate() on a date larger than 12/31/1999.</p> | |
<p>According to <a href="https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-2017">Microsoft SQL</a> and | |
<a href="https://dev.mysql.com/doc/refman/8.0/en/datetime.html">MySQL</a> documentation</a>, | |
the maximum date value accepted is <u>12/31/9999 23:59:59.9999</u>.</p> | |
<cfset FutureDate = "9/9/292278994"> | |
<cfoutput> | |
<pre> | |
<b>FutureDate = "#FutureDate#" (invalid date)</b> | |
isDate(FutureDate) = #isDate(FutureDate)# | |
isValid("date", FutureDate) = #isValid("date", FutureDate)# | |
</pre> | |
<p><b>NOTE:</b> 1/1/292278994 - 9/8/292278994 are valid, but stops being valid on 9/9/292278994.</p> | |
<cfset FutureDate = "12/31/292278993"> | |
<pre> | |
<b>FutureDate = "#FutureDate#" (valid date?)</b> | |
isDate(FutureDate) = #isDate(FutureDate)# | |
isValid("date", FutureDate) = #isValid("date", FutureDate)# | |
</pre> | |
<CFSET FutureDate = "9/8/292278994"> | |
<cftry> | |
<pre><b>FutureDate = DateAdd("d", 1, FutureDate) (invalid date + 1 day = valid date?)</b></pre> | |
<cfset FutureDate = DateAdd("d", 1, FutureDate)> | |
<pre>FutureDate = "#FutureDate#"</b> | |
isDate(FutureDate) = #isDate(FutureDate)# | |
isValid("date", FutureDate) = #isValid("date", FutureDate)# | |
</pre> | |
<cfcatch>#cfcatch.message#</cfcatch> | |
</cftry> | |
<hr> | |
<pre> | |
createODBCDate("12/31/9999") = #createODBCDate("12/31/9999")# | |
createODBCDate("9/8/292278993") = #createODBCDate("9/8/292278993")# | |
</pre> | |
<cfset TestQuery = QueryNew("myDate", "date")> | |
<cfset queryAddRow(TestQuery)> | |
<div>set 12/31/9999 | |
<cftry> | |
<cfset QuerySetCell(TestQuery, "myDate", createODBCDate("12/31/9999"))> | |
<cfcatch>: #cfcatch.message#</cfcatch> | |
</cftry> | |
</div> | |
<cfset TestDate = "9/8/292278993"> | |
<cfset queryAddRow(TestQuery)> | |
<div>set #TestDate# | |
<cftry> | |
<cfset QuerySetCell(TestQuery, "myDate", createODBCDate(TestDate))> | |
<cfcatch>: #cfcatch.message#</cfcatch> | |
</cftry> | |
</div> | |
</cfoutput> | |
<cfdump var="#TestQuery#" label="test query"> | |
<hr> | |
<h2>HTML5 "Date" Input validation</h2> | |
<form action=""> | |
<input type="date" value="292278993-09-08" size="15" required> ("292278993-09-08" won't prepopulate; obviously invalid, right?)<br> | |
<input type="date" value="22019-07-22" size="11" required><br> | |
<input type="date" value="1999-12-31" size="10" required> | |
<button type="submit">Test</button> | |
</form> | |
<cfdump var="#Server#" label="Server Info"> |
Top comments (0)