Raw Queries with multiple Bind Parameters and in wrong order not working properly - (tested on Oracle)

[tfiliano]

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

I noticed that Sequelize transforms the parameters into an array and doesn't care about the order they are being used within the query which can result in not having any results when doing a SELECT.

It was not tested in other circumstances like INSERT or UPDATE.

Reproducible Example

It is as simple as doing a raw query in any Oracle table using more than one condition and passing them in the wrong order like:

const { QueryTypes } = require('sequelize');

await sequelize.query(
  'SELECT * FROM projects WHERE status = :status and field2 = :field2 ',
  {
    bind: {  field2: 'something', status: 'project_status' },
    type: QueryTypes.SELECT,
  },
);

What do you expect to happen?

Insite the oracle/query.js when call the execute, is supposed to send:

  await this.connection.execute('SELECT * FROM projects WHERE status = :status and field2 = :field2 ', ['project_status', 'something'], { autoCommit: this.autoCommit });

What is actually happening?

  await this.connection.execute('SELECT * FROM projects WHERE status = :status and field2 = :field2 ', ['something', 'project_status'], { autoCommit: this.autoCommit });

Because the variable this.bindParameters is being populated with a simple forEach, it is getting the wrong result or nothing.

//approx line 122 on the query.js file

Object.values(parameters).forEach((value) => {
   bindParameters.push(value);
});

I suggest fixing it by adding the matcher before pushing the parameter to the parameter list as long as we need them, also if we were using one parameter multiple times, it would cover it.

//approx line 122 on the query.js file
let matcher = new RegExp(/(:)(([^\d])\w+)/gm)
        while ( result = matcher.exec(sql) ) {
          bindParameters.push(parameters[result[2]])
        }

Environment

• Sequelize version: sequelize@6.37.1
• Node.js version: v18.18.2
• If TypeScript related: TypeScript version:
• Database & Version: Oracle 11gR2
• Connector library & Version: oracledb@6.4.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.