[Reopen] Mismatch date value sql injection checking due to timezone problem in moment.js and moment-timezone.js when using oracle dialect for DataTypes.DATE #18062
Description
/<!--
If you don't follow the issue template, your issue may be closed.
Remember to properly format your code in code blocks.
Please note this is an issue tracker, not a support forum.
For general questions, please use one of these:
- StackOverflow: https://stackoverflow.com/questions/tagged/sequelize.js
- GitHub discussions: https://github.com/sequelize/sequelize/discussions
-->
Issue Creation Checklist
- I understand that my issue will be automatically closed if I don't fill in the requested information
- I have read the contribution guidelines
Bug Description
In the "src/sql-string.js", found the mismatch of using "moment" for formatting date as compare to using "moment-timezone" in "src/dialects/oracle/data-types.js".
In "src/sql-string.js":
...
// Validate the date value using Moment.js with the expected format
const formattedDate = moment(dateValue).format('YYYY-MM-DD HH:mm:ss.SSS Z');
// If the formatted date doesn't match the input date value, throw an error
if (formattedDate !== dateValue) {
throw new Error("Invalid date value for TO_TIMESTAMP_TZ. Expected format: 'YYYY-MM-DD HH:mm:ss.SSS Z'");
...
In "src/dialects/oracle/data-types.js":
...
_stringify(date, options) {
const format = 'YYYY-MM-DD HH24:MI:SS.FFTZH:TZM';
date = this._applyTimezone(date, options);
const formatedDate = date.format('YYYY-MM-DD HH:mm:ss.SSS Z');
return `TO_TIMESTAMP_TZ('${formatedDate}','${format}')`;
}
_applyTimezone(date, options) {
if (options.timezone) {
if (momentTz.tz.zone(options.timezone)) {
date = momentTz(date).tz(options.timezone);
} else {
date = moment(date).utcOffset(options.timezone);
}
} else {
date = momentTz(date);
}
return date;
}
...
The stringify in oracle dialect data type "DATE" would use the "moment-timezone" for applying timezone, while in the "sql-string.js" checking of using "TO_TIMESTAMP_TZ" to prevent sql injection, it use "moment" with the default timezone in the environment.
There is some problem related to applying timezone for "Asia/Hong_Kong", below is the tested code:
import moment from "moment";
import momentTz from "moment-timezone";
const date = new Date("1904-01-01T00:00:00");
const date1 = moment(date).utcOffset("+00:00");
const date2 = momentTz(date).tz("+00:00");
const date3 = momentTz(date);
const date4 = moment(date).format("YYYY-MM-DD HH:mm:ss.SSS Z");
console.log("Date 1: " + date1.format("YYYY-MM-DD HH:mm:ss.SSS Z"));
console.log("Date 2: " + date2.format("YYYY-MM-DD HH:mm:ss.SSS Z"));
console.log("Date 3: " + date3.format("YYYY-MM-DD HH:mm:ss.SSS Z"));
console.log("Date 4: " + date4);
Outputs:
Date 1: 1903-12-31 16:23:18.000 +00:00
Date 2: 1904-01-01 00:00:00.000 +07:36
Date 3: 1904-01-01 00:00:00.000 +07:36
Date 4: 1904-01-01 00:00:00.000 +07:30
The problem lies into that there is 6 minutes difference between "moment" and "moment-timezone" when applying timezone for "Asia/Hong_Kong" for early dates. With these 6 minutes difference, the above "sql-string.js", "formattedDate" and "dateValue" will never match and throw exception.
You may try the above testing code located in the below link:
moment & moment-timezone test code
Reproducible Example
Here is the link to the SSCCE for this issue:
What do you expect to happen?
What is actually happening?
Environment
- Sequelize version: 6.37.7
- Node.js version: 22.16.0
- If TypeScript related: TypeScript version:
- Database & Version: Oracle DB EE 19c 19.18
- Connector library & Version: oracledb (6.10.0)
Would you be willing to resolve this issue by submitting a Pull Request?
- Yes, I have the time and I know how to start.
- Yes, I have the time but I will need guidance.
- No, I don't have the time, but my company or I are supporting Sequelize through donations on OpenCollective.
- No, I don't have the time, and I understand that I will need to wait until someone from the community or maintainers is interested in resolving my issue.
Indicate your interest in the resolution of this issue by adding the 👍 reaction. Comments such as "+1" will be removed.