This PR is still WIP but I'm opening it to describe the changes as I go.

Fixes in v7: #13817
Closes #14273
Closes #12523

This (v7) PR fixes how bind & replacements are handled in query interface & findAll methods.

Before this PR

Let's take the following query:

User.findAll({
  where: and(
    literal('soundex("firstName") = soundex(:firstName)'),
    { lastName: lastName },
  ),
  replacements: { firstName },
})

Before this PR, Sequelize would first generate the SQl for the where parameter:

SELECT * FROM users WHERE soundex("firstName") = soundex(:firstName) AND "lastName" = 'Doe'

Then pass that query to Sequelize#query, which would process the replacements option (in this case, the :firstName parameter):

SELECT * FROM users WHERE soundex("firstName") = soundex('John') AND "lastName" = 'Doe'

Because of this two-step processing, a malicious input can result in SQL injection. For instance with these two user inputs:

const lastName = ':firstName'; // <- notice how it includes the syntax for a replacement parameter
const firstName = '; DROP TABLE users;'

With the above input, QueryGenerator will first generate this:

SELECT * FROM users WHERE soundex("firstName") = soundex(:firstName) AND "lastName" = ':firstName'

Then Sequelize#query will process :firstName, resulting in this:

SELECT * FROM users WHERE soundex("firstName") = soundex('; DROP TABLE users;') AND "lastName" = ''; DROP TABLE users;''

Resulting in the injection of DROP TABLE users;, interpreted as actual SQL.

After this PR

Replacements are now handled by QueryGenerator instead. This allows QueryGenerator to only process replacements in literal() and nowhere else.

QueryGenerator will generate this:

SELECT * FROM users WHERE soundex("firstName") = soundex('; DROP TABLE users;') AND "lastName" = ':firstName'

Then the query will be passed to a new method, Sequelize#queryRaw, which does not process replacements at all. So the query is executed as-is.

Sequelize#query will still process replacements, but as long as you don't put replacements inside strings there, and don't inline user input yourself, you'll be fine.

Escaping $params

It is not necessary to escape $bind parameters in string anymore. This solution was a horrible band aid that caused more issues than it solved.

Only bind parameters outside of strings and comments will be treated as bind parameters by Sequelize.

Example 1 (mysql):

const result = await this.sequelize.query(`select * from users WHERE id = '$$one'`);

-- before this PR, it was executed as:
SELECT * FROM users WHERE id = '$one';

-- after this PR, it is executed as:
SELECT * FROM users WHERE id = '$$one';

Example 1 (mysql):

const result = await this.sequelize.query(`select * from users WHERE id = '$one'`);

-- before this PR, it was executed as:
SELECT * FROM users WHERE id = '?';

-- after this PR, it is executed as:
SELECT * FROM users WHERE id = '$one';

Other changes

• The bind & replacement options can now be used at the same time! This is because handling of bind parameters is done by a new parser that knows whether $name is a bind parameter, or just text inside a string.
• It's now possible to use bind parameters in update, and insert methods. It was not previously possible because sequelize generates its own bind parameters in these methods, which would overwrite the ones provided by the user. They are now safely merged.
• ::cast is not interpreted as a replacement anymore, it's a cast.

TODO

• Test the new bind replacer against comments
• Finish updating the tests

TODO (future PR)

• Replace replacements using the same string-aware replacer as bind. It's not included in this PR as it's going to be a breaking change for a few scenarios that depend on Support specifying the type of a bind param / replacement #14410 as a clean alternative.