On Mon, Dec 12, 2022 at 10:55 AM Joseph Koshakow <kosh...@gmail.com> wrote:
>
> I just found another class of this bug that the submitted patch does
> not fix. If the units are at the beginning of the string, then they are
> also ignored. For example, `date 'm d y2020m11d3'` is also valid. I
> think the fix here is to check and make sure that ptype is 0 before
> reassigning the value to a non-zero number. I'll send an updated patch
> with this tonight.
Attached is the described patch.
- Joe Koshakow
From af72736bb4149afa629281e27da2141635a93cac Mon Sep 17 00:00:00 2001
From: Joseph Koshakow <kosh...@gmail.com>
Date: Sat, 10 Dec 2022 18:59:26 -0500
Subject: [PATCH] Handle dangling units in date-time input
DecodeDateTime and DecodeTimeOnly allowed dangling unit types at the
beginning and end of inputs without returning an error. For example,
`date '1995-08-06 m y d'` and
`timestamp 'y m s d y2001m02d04 h04mm17s34'` were considered a valid
date and the dangling units were ignored. This commit fixes this issue
so an error is returned instead.
---
src/backend/utils/adt/datetime.c | 21 ++++++++++++++++++++-
src/test/regress/expected/date.out | 10 ++++++++++
src/test/regress/expected/time.out | 5 +++++
src/test/regress/expected/timestamp.out | 10 ++++++++++
src/test/regress/expected/timestamptz.out | 10 ++++++++++
src/test/regress/expected/timetz.out | 5 +++++
src/test/regress/sql/date.sql | 5 +++++
src/test/regress/sql/time.sql | 3 +++
src/test/regress/sql/timestamp.sql | 5 +++++
src/test/regress/sql/timestamptz.sql | 5 +++++
src/test/regress/sql/timetz.sql | 3 +++
11 files changed, 81 insertions(+), 1 deletion(-)
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index b5b117a8ca..ebd7caff08 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -1509,6 +1509,9 @@ DecodeDateTime(char **field, int *ftype, int nf,
case UNITS:
tmask = 0;
+ /* prevent consecutive unhandled units */
+ if (ptype != 0)
+ return DTERR_BAD_FORMAT;
ptype = val;
break;
@@ -1535,7 +1538,9 @@ DecodeDateTime(char **field, int *ftype, int nf,
ftype[i + 1] != DTK_TIME &&
ftype[i + 1] != DTK_DATE))
return DTERR_BAD_FORMAT;
-
+ /* prevent consecutive unhandled units */
+ if (ptype != 0)
+ return DTERR_BAD_FORMAT;
ptype = val;
break;
@@ -1566,6 +1571,10 @@ DecodeDateTime(char **field, int *ftype, int nf,
fmask |= tmask;
} /* end loop over fields */
+ /* prefix type was dangling and never handled */
+ if (ptype != 0)
+ return DTERR_BAD_FORMAT;
+
/* do final checking/adjustment of Y/M/D fields */
dterr = ValidateDate(fmask, isjulian, is2digits, bc, tm);
if (dterr)
@@ -2367,6 +2376,9 @@ DecodeTimeOnly(char **field, int *ftype, int nf,
case UNITS:
tmask = 0;
+ /* prevent consecutive unhandled units */
+ if (ptype != 0)
+ return DTERR_BAD_FORMAT;
ptype = val;
break;
@@ -2385,6 +2397,9 @@ DecodeTimeOnly(char **field, int *ftype, int nf,
ftype[i + 1] != DTK_DATE))
return DTERR_BAD_FORMAT;
+ /* prevent consecutive unhandled units */
+ if (ptype != 0)
+ return DTERR_BAD_FORMAT;
ptype = val;
break;
@@ -2415,6 +2430,10 @@ DecodeTimeOnly(char **field, int *ftype, int nf,
fmask |= tmask;
} /* end loop over fields */
+ /* prefix type was dangling and never handled */
+ if (ptype != 0)
+ return DTERR_BAD_FORMAT;
+
/* do final checking/adjustment of Y/M/D fields */
dterr = ValidateDate(fmask, isjulian, is2digits, bc, tm);
if (dterr)
diff --git a/src/test/regress/expected/date.out b/src/test/regress/expected/date.out
index f8f83e40e9..f4239a5402 100644
--- a/src/test/regress/expected/date.out
+++ b/src/test/regress/expected/date.out
@@ -1526,3 +1526,13 @@ select make_time(10, 55, 100.1);
ERROR: time field value out of range: 10:55:100.1
select make_time(24, 0, 2.1);
ERROR: time field value out of range: 24:00:2.1
+-- test error on dangling units
+SELECT date '1995-08-06 m';
+ERROR: invalid input syntax for type date: "1995-08-06 m"
+LINE 1: SELECT date '1995-08-06 m';
+ ^
+SET datestyle = ISO;
+SELECT date 'y m s d y2001m02d04';
+ERROR: invalid input syntax for type date: "y m s d y2001m02d04"
+LINE 1: SELECT date 'y m s d y2001m02d04';
+ ^
diff --git a/src/test/regress/expected/time.out b/src/test/regress/expected/time.out
index a44caededd..5f7058eca8 100644
--- a/src/test/regress/expected/time.out
+++ b/src/test/regress/expected/time.out
@@ -229,3 +229,8 @@ SELECT date_part('epoch', TIME '2020-05-26 13:30:25.575401');
48625.575401
(1 row)
+-- test error on dangling units
+SELECT time '12:30:15 d';
+ERROR: invalid input syntax for type time: "12:30:15 d"
+LINE 1: SELECT time '12:30:15 d';
+ ^
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index be66274738..e859b9d214 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -2110,3 +2110,13 @@ select * from generate_series('2020-01-01 00:00'::timestamp,
'2020-01-02 03:00'::timestamp,
'0 hour'::interval);
ERROR: step size cannot equal zero
+-- test error on dangling units
+SELECT timestamp '1995-08-06 12:30:15 y';
+ERROR: invalid input syntax for type timestamp: "1995-08-06 12:30:15 y"
+LINE 1: SELECT timestamp '1995-08-06 12:30:15 y';
+ ^
+SET datestyle = ISO;
+SELECT timestamp 'y m s d y2001m02d04 h04mm17s34';
+ERROR: invalid input syntax for type timestamp: "y m s d y2001m02d04 h04mm17s34"
+LINE 1: SELECT timestamp 'y m s d y2001m02d04 h04mm17s34';
+ ^
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index fb06acbccc..33a0dab4fd 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -3085,3 +3085,13 @@ select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
Tue Jan 17 16:00:00 2017 PST
(1 row)
+-- test error on dangling units
+SELECT timestamptz '1995-08-06 12:30:15 m';
+ERROR: invalid input syntax for type timestamp with time zone: "1995-08-06 12:30:15 m"
+LINE 1: SELECT timestamptz '1995-08-06 12:30:15 m';
+ ^
+SET datestyle = ISO;
+SELECT timestamptz 'y m s d y2001m02d04 h04mm17s34';
+ERROR: invalid input syntax for type timestamp with time zone: "y m s d y2001m02d04 h04mm17s34"
+LINE 1: SELECT timestamptz 'y m s d y2001m02d04 h04mm17s34';
+ ^
diff --git a/src/test/regress/expected/timetz.out b/src/test/regress/expected/timetz.out
index 984285663b..2ddf5e43ba 100644
--- a/src/test/regress/expected/timetz.out
+++ b/src/test/regress/expected/timetz.out
@@ -262,3 +262,8 @@ SELECT date_part('epoch', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-
63025.575401
(1 row)
+-- test error on dangling units
+SELECT timetz '12:30:15 d';
+ERROR: invalid input syntax for type time with time zone: "12:30:15 d"
+LINE 1: SELECT timetz '12:30:15 d';
+ ^
diff --git a/src/test/regress/sql/date.sql b/src/test/regress/sql/date.sql
index 9fd15be5f9..f5c05c8f77 100644
--- a/src/test/regress/sql/date.sql
+++ b/src/test/regress/sql/date.sql
@@ -371,3 +371,8 @@ select make_date(2013, 13, 1);
select make_date(2013, 11, -1);
select make_time(10, 55, 100.1);
select make_time(24, 0, 2.1);
+
+-- test error on dangling units
+SELECT date '1995-08-06 m';
+SET datestyle = ISO;
+SELECT date 'y m s d y2001m02d04';
diff --git a/src/test/regress/sql/time.sql b/src/test/regress/sql/time.sql
index b439cd6b41..4f1c14226c 100644
--- a/src/test/regress/sql/time.sql
+++ b/src/test/regress/sql/time.sql
@@ -77,3 +77,6 @@ SELECT date_part('microsecond', TIME '2020-05-26 13:30:25.575401');
SELECT date_part('millisecond', TIME '2020-05-26 13:30:25.575401');
SELECT date_part('second', TIME '2020-05-26 13:30:25.575401');
SELECT date_part('epoch', TIME '2020-05-26 13:30:25.575401');
+
+-- test error on dangling units
+SELECT time '12:30:15 d';
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index e1175b12ce..d0594711b1 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -391,3 +391,8 @@ select generate_series('2022-01-01 00:00'::timestamp,
select * from generate_series('2020-01-01 00:00'::timestamp,
'2020-01-02 03:00'::timestamp,
'0 hour'::interval);
+
+-- test error on dangling units
+SELECT timestamp '1995-08-06 12:30:15 y';
+SET datestyle = ISO;
+SELECT timestamp 'y m s d y2001m02d04 h04mm17s34';
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index 27263b3e0b..04c1a00c69 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -594,3 +594,8 @@ insert into tmptz values ('2017-01-18 00:00+00');
explain (costs off)
select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
+
+-- test error on dangling units
+SELECT timestamptz '1995-08-06 12:30:15 m';
+SET datestyle = ISO;
+SELECT timestamptz 'y m s d y2001m02d04 h04mm17s34';
diff --git a/src/test/regress/sql/timetz.sql b/src/test/regress/sql/timetz.sql
index b62aa3fe05..ccbfc31d64 100644
--- a/src/test/regress/sql/timetz.sql
+++ b/src/test/regress/sql/timetz.sql
@@ -84,3 +84,6 @@ SELECT date_part('microsecond', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-
SELECT date_part('millisecond', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
SELECT date_part('second', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
SELECT date_part('epoch', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
+
+-- test error on dangling units
+SELECT timetz '12:30:15 d';
--
2.34.1
