agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19590: to_date/to_timestamp "Y,YYY" accepts out-of-range values
2+ messages / 2 participants
[nested] [flat]
* BUG #19590: to_date/to_timestamp "Y,YYY" accepts out-of-range values
@ 2026-07-31 05:57 PG Bug reporting form <noreply@postgresql.org>
2026-07-31 11:03 ` Re: BUG #19590: to_date/to_timestamp "Y,YYY" accepts out-of-range values Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2026-07-31 05:57 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: malis@pgrust.com
The following bug has been logged on the website:
Bug reference: 19590
Logged by: Michael Malis
Email address: malis@pgrust.com
PostgreSQL version: 18.4
Operating system: Debian 18.4-1.pgdg13+1, aarch64
Description:
The Y,YYY template field parses its millennia component with a bare
sscanf(..., "%d", ...), which silently truncates values too large for int
instead of rejecting them, so out-of-range input yields a wrong year:
SELECT to_date('4294969320,024','Y,YYY'); -- 2024024-01-01 (expected:
error)
SELECT to_date('-4294965272,024','Y,YYY'); -- 2024024-01-01 (expected:
error)
4294969320 is 2^32 + 2024, so it truncates to 2024 and is read as 2024
millennia; any multiple of 2^32 works, and %d accepts a sign, so wrapped
negatives too. to_timestamp() shares the code path. Every other numeric
field rejects this:
SELECT to_date('4294969320','YYYY');
-- ERROR: value for "YYYY" in source string is out of range
Cause: DCH_Y_YYY is the only numeric field using raw sscanf; the others go
through from_char_parse_int_len(), which range-checks with strtol/ERANGE.
The existing pg_mul_s32_overflow guard in DCH_Y_YYY runs too late. %d has
already discarded the magnitude.
Suggested fix: after the sscanf, re-scan the millennia field with strtol and
reject ERANGE or out-of-int-range values, matching
from_char_parse_int_len():
errno = 0;
lval = strtol(s, &endptr, 10);
if (errno == ERANGE || lval < INT_MIN || lval > INT_MAX)
ereturn(escontext,,
(errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
errmsg("value for \"%s\" in source string is out of range",
"Y,YYY"),
errdetail("Value must be in the range %d to %d.", INT_MIN,
INT_MAX)));
strtol skips leading whitespace and stops at the comma exactly as %d does,
so this only adds a rejection path; the ERANGE test covers 32-bit-long
platforms where strtol saturates.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19590: to_date/to_timestamp "Y,YYY" accepts out-of-range values
2026-07-31 05:57 BUG #19590: to_date/to_timestamp "Y,YYY" accepts out-of-range values PG Bug reporting form <noreply@postgresql.org>
@ 2026-07-31 11:03 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Andrey Rachitskiy @ 2026-07-31 11:03 UTC (permalink / raw)
To: malis@pgrust.com; pgsql-bugs@lists.postgresql.org
Hi, Michael!
Thanks for report.
DCH_Y_YYY used to parse millennia with sscanf("%d"). That silently
truncates values outside int range, so
```
SELECT to_date('4294969320,024', 'Y,YYY'); -- 2^32+2024
SELECT to_date('-4294965272,024', 'Y,YYY');
```
used to return 2024024-01-01 instead of erroring.
Other numeric fields already reject this via
from_char_parse_int_len(); the pg_mul_s32_overflow() check in
DCH_Y_YYY runs too late, after %d has discarded the magnitude.
This patch replaces the millennia %d with a single strtol(), using
the same ERANGE / INT_MIN / INT_MAX checks as from_char_parse_int_len().
The years part remains sscanf("%03d"): the field width limits the
conversion to three characters, so the value always fits in int. The
existing mul/add overflow checks are unchanged (they still catch
cases like 1000000000,999).
Y,YYY has two pieces: variable-width millennia up to a comma, then
three year digits. We parse and validate each separately.
Patch with tests, attached.
пт, 31 июл. 2026 г. в 14:47, PG Bug reporting form <noreply@postgresql.org>:
> The following bug has been logged on the website:
>
> Bug reference: 19590
> Logged by: Michael Malis
> Email address: malis@pgrust.com
> PostgreSQL version: 18.4
> Operating system: Debian 18.4-1.pgdg13+1, aarch64
> Description:
>
> The Y,YYY template field parses its millennia component with a bare
> sscanf(..., "%d", ...), which silently truncates values too large for int
> instead of rejecting them, so out-of-range input yields a wrong year:
>
> SELECT to_date('4294969320,024','Y,YYY'); -- 2024024-01-01 (expected:
> error)
> SELECT to_date('-4294965272,024','Y,YYY'); -- 2024024-01-01 (expected:
> error)
>
> 4294969320 is 2^32 + 2024, so it truncates to 2024 and is read as 2024
> millennia; any multiple of 2^32 works, and %d accepts a sign, so wrapped
> negatives too. to_timestamp() shares the code path. Every other numeric
> field rejects this:
>
> SELECT to_date('4294969320','YYYY');
> -- ERROR: value for "YYYY" in source string is out of range
>
> Cause: DCH_Y_YYY is the only numeric field using raw sscanf; the others go
> through from_char_parse_int_len(), which range-checks with strtol/ERANGE.
> The existing pg_mul_s32_overflow guard in DCH_Y_YYY runs too late. %d has
> already discarded the magnitude.
>
> Suggested fix: after the sscanf, re-scan the millennia field with strtol
> and
> reject ERANGE or out-of-int-range values, matching
> from_char_parse_int_len():
>
> errno = 0;
> lval = strtol(s, &endptr, 10);
> if (errno == ERANGE || lval < INT_MIN || lval > INT_MAX)
> ereturn(escontext,,
> (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
> errmsg("value for \"%s\" in source string is out of range",
> "Y,YYY"),
> errdetail("Value must be in the range %d to %d.", INT_MIN,
> INT_MAX)));
>
> strtol skips leading whitespace and stops at the comma exactly as %d does,
> so this only adds a rejection path; the ERANGE test covers 32-bit-long
> platforms where strtol saturates.
>
>
>
>
>
--
Regards,
Rachitskiy Andrey
Attachments:
[text/x-patch] 0001-Reject-out-of-range-millennia-in-Y-YYY-parsing.patch (4.6K, ../../CAB8bMiumLaBvuX+FYU8q4BRwzb9fG1+ubR0pVtTMTT2WqRo1Bg@mail.gmail.com/3-0001-Reject-out-of-range-millennia-in-Y-YYY-parsing.patch)
download | inline diff:
From: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Date: Fri, 31 Jul 2026 14:58:00 +0500
Subject: [PATCH] Reject out-of-range millennia in Y,YYY parsing
DCH_Y_YYY parsed the millennia component with sscanf("%d"), which
silently truncates values outside int range. Inputs like
4294969320,024 (2^32+2024) were therefore accepted as year 2024024
instead of being rejected.
Parse millennia with strtol() using the same ERANGE / INT_MIN /
INT_MAX checks as from_char_parse_int_len(). Keep the bounded %03d
scan for the years component and the existing mul/add overflow checks.
Author: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Reported-by: Michael Malis <malis@pgrust.com>
Discussion: https://www.postgresql.org/message-id/19590-991c467c2a601be0%40postgresql.org
---
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index d52d71b0a8c..8e2716a2f91 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -3543,13 +3543,29 @@ DCH_from_char(FormatNode *node, const char *in, TmFromChar *out,
break;
case DCH_Y_YYY:
{
- int matched,
- years,
+ char *endp;
+ int years,
millennia,
nch;
+ long lval;
- matched = sscanf(s, "%d,%03d%n", &millennia, &years, &nch);
- if (matched < 2)
+ errno = 0;
+ lval = strtol(s, &endp, 10);
+ if (endp == s || *endp != ',')
+ ereturn(escontext,,
+ (errcode(ERRCODE_INVALID_DATETIME_FORMAT),
+ errmsg("invalid value \"%s\" for \"%s\"", s, "Y,YYY")));
+ if (errno == ERANGE || lval < INT_MIN || lval > INT_MAX)
+ ereturn(escontext,,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("value for \"%s\" in source string is out of range",
+ "Y,YYY"),
+ errdetail("Value must be in the range %d to %d.",
+ INT_MIN, INT_MAX)));
+ millennia = (int) lval;
+
+ /* %03d: width limits conversion to 3 chars, fits in int */
+ if (sscanf(endp + 1, "%03d%n", &years, &nch) < 1)
ereturn(escontext,,
(errcode(ERRCODE_INVALID_DATETIME_FORMAT),
errmsg("invalid value \"%s\" for \"%s\"", s, "Y,YYY")));
@@ -3564,7 +3580,7 @@ DCH_from_char(FormatNode *node, const char *in, TmFromChar *out,
if (!from_char_set_int(&out->year, years, n, escontext))
return;
out->yysz = 4;
- s += nch;
+ s = endp + 1 + nch;
SKIP_THth(s, n->suffix);
}
break;
diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index 32cf62b6741..ee60fb0e5bf 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -3772,6 +3772,21 @@ SELECT to_timestamp('2015-02-11 86400', 'YYYY-MM-DD SSSSS');
ERROR: date/time field value out of range: "2015-02-11 86400"
SELECT to_timestamp('1000000000,999', 'Y,YYY');
ERROR: value for "Y,YYY" in source string is out of range
+-- millennia too large for int:
+SELECT to_date('4294969320,024', 'Y,YYY');
+ERROR: value for "Y,YYY" in source string is out of range
+DETAIL: Value must be in the range -2147483648 to 2147483647.
+SELECT to_date('-4294965272,024', 'Y,YYY');
+ERROR: value for "Y,YYY" in source string is out of range
+DETAIL: Value must be in the range -2147483648 to 2147483647.
+SELECT to_timestamp('4294969320,024', 'Y,YYY');
+ERROR: value for "Y,YYY" in source string is out of range
+DETAIL: Value must be in the range -2147483648 to 2147483647.
+-- malformed Y,YYY (no comma / no years after comma):
+SELECT to_date('2024', 'Y,YYY');
+ERROR: invalid value "2024" for "Y,YYY"
+SELECT to_date('2,', 'Y,YYY');
+ERROR: invalid value "2," for "Y,YYY"
SELECT to_timestamp('0.-2147483648', 'SS.MS');
ERROR: date/time field value out of range: "0.-2147483648"
SELECT to_timestamp('613566758', 'W');
diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql
index 8978249a5dc..c4dff557a68 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -657,6 +657,13 @@ SELECT to_timestamp('2015-02-11 86400', 'YYYY-MM-DD SSSS');
SELECT to_timestamp('2015-02-11 86000', 'YYYY-MM-DD SSSSS'); -- ok
SELECT to_timestamp('2015-02-11 86400', 'YYYY-MM-DD SSSSS');
SELECT to_timestamp('1000000000,999', 'Y,YYY');
+-- millennia too large for int:
+SELECT to_date('4294969320,024', 'Y,YYY');
+SELECT to_date('-4294965272,024', 'Y,YYY');
+SELECT to_timestamp('4294969320,024', 'Y,YYY');
+-- malformed Y,YYY (no comma / no years after comma):
+SELECT to_date('2024', 'Y,YYY');
+SELECT to_date('2,', 'Y,YYY');
SELECT to_timestamp('0.-2147483648', 'SS.MS');
SELECT to_timestamp('613566758', 'W');
SELECT to_timestamp('2024 613566758 1', 'YYYY WW D');
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-07-31 11:03 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-07-31 05:57 BUG #19590: to_date/to_timestamp "Y,YYY" accepts out-of-range values PG Bug reporting form <noreply@postgresql.org>
2026-07-31 11:03 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox