Repository: knex/knex
Pull request #5396 (2022-11-29 09:06:21)

Bump tsd from 0.24.1 to 0.25.0
Bumps [tsd](https://github.com/SamVerschueren/tsd) from 0.24.1 to 0.25.0.
<details>
<summary>Release notes</summary>
<p><em>Sourced from <a href="https://github.com/SamVerschueren/tsd/releases">tsd's releases</a>.</em></p>
<blockquote>
<h2>v0.25.0</h2>
<ul>
<li>Upgrade to TypeScript 4.9  5a7ee7b</li>
</ul>
<p><a href="https://github.com/SamVerschueren/tsd/compare/v0.24.1...v0.25.0">https://github.com/SamVerschueren/tsd/compare/v0.24.1...v0.25.0</a></p>
</blockquote>
</details>
<details>
<summary>Commits</summary>
<ul>
<li><a href="https://github.com/SamVerschueren/tsd/commit/2519573e4187c5afcc1714d74f3c8b466ac3820b"><code>2519573</code></a> 0.25.0</li>
<li><a href="https://github.com/SamVerschueren/tsd/commit/5a7ee7bf2c85a2680c1d55fef63185b30fc6d8e8"><code>5a7ee7b</code></a> Upgrade to TypeScript 4.9</li>
<li><a href="https://github.com/SamVerschueren/tsd/commit/09c0d17365c7585dd8d77f4c0d454d63d0c58e72"><code>09c0d17</code></a> Add regression test for <a href="https://github-redirect.dependabot.com/SamVerschueren/tsd/issues/161">#161</a> (<a href="https://github-redirect.dependabot.com/SamVerschueren/tsd/issues/164">#164</a>)</li>
<li>See full diff in <a href="https://github.com/SamVerschueren/tsd/compare/v0.24.1...v0.25.0">compare view</a></li>
</ul>
</details>
<br />


[![Dependabot compatibility score](https://dependabot-badges.githubapp.com/badges/compatibility_score?dependency-name=tsd&package-manager=npm_and_yarn&previous-version=0.24.1&new-version=0.25.0)](https://docs.github.com/en/github/managing-security-vulnerabilities/about-dependabot-security-updates#about-compatibility-scores)

Dependabot will resolve any conflicts with this PR as long as you don't alter it yourself. You can also trigger a rebase manually by commenting `@dependabot rebase`.

[//]: # (dependabot-automerge-start)
[//]: # (dependabot-automerge-end)

---

<details>
<summary>Dependabot commands and options</summary>
<br />

You can trigger Dependabot actions by commenting on this PR:
- `@dependabot rebase` will rebase this PR
- `@dependabot recreate` will recreate this PR, overwriting any edits that have been made to it
- `@dependabot merge` will merge this PR after your CI passes on it
- `@dependabot squash and merge` will squash and merge this PR after your CI passes on it
- `@dependabot cancel merge` will cancel a previously requested merge and block automerging
- `@dependabot reopen` will reopen this PR if it is closed
- `@dependabot close` will close this PR and stop Dependabot recreating it. You can achieve the same result by closing it manually
- `@dependabot ignore this major version` will close this PR and stop Dependabot creating any more for this major version (unless you reopen the PR or upgrade to it yourself)
- `@dependabot ignore this minor version` will close this PR and stop Dependabot creating any more for this minor version (unless you reopen the PR or upgrade to it yourself)
- `@dependabot ignore this dependency` will close this PR and stop Dependabot creating any more for this dependency (unless you reopen the PR or upgrade to it yourself)


</details>

coveralls: 
[![Coverage Status](https://coveralls.io/builds/54649124/badge)](https://coveralls.io/builds/54649124)

Coverage remained the same at 92.37% when pulling **a48d7b55eb02a4dd4c00141c85f24e3fdedcb58b on dependabot/npm_and_yarn/tsd-0.25.0** into **ab45314e70bc55a98de08d9c2bd7005bac146702 on master**.


Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 63358372..f9a59b37 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -1,5 +1,8 @@
 # Master (Unreleased)
 
+### Bug fixes
+- Add assertion for basic where clause not to be object or array #1227
+
 # 2.3.0 - 31 August, 2022
 
 ### New features:
diff --git a/lib/dialects/mysql/query/mysql-querycompiler.js b/lib/dialects/mysql/query/mysql-querycompiler.js
index a235c64f..1c1a1c09 100644
--- a/lib/dialects/mysql/query/mysql-querycompiler.js
+++ b/lib/dialects/mysql/query/mysql-querycompiler.js
@@ -1,6 +1,9 @@
 // MySQL Query Compiler
 // ------
+const assert = require('assert');
 const identity = require('lodash/identity');
+const isPlainObject = require('lodash/isPlainObject');
+const isEmpty = require('lodash/isEmpty');
 const QueryCompiler = require('../../../query/querycompiler');
 const { wrapAsIdentifier } = require('../../../formatter/formatterUtils');
 const {
@@ -8,6 +11,9 @@ const {
   wrap: wrap_,
 } = require('../../../formatter/wrappingFormatter');
 
+const isPlainObjectOrArray = (value) =>
+  isPlainObject(value) || Array.isArray(value);
+
 class QueryCompiler_MySQL extends QueryCompiler {
   constructor(client, builder, formatter) {
     super(client, builder, formatter);
@@ -131,7 +137,8 @@ class QueryCompiler_MySQL extends QueryCompiler {
       output(resp) {
         const out = resp.reduce(function (columns, val) {
           columns[val.COLUMN_NAME] = {
-            defaultValue: val.COLUMN_DEFAULT === 'NULL' ? null : val.COLUMN_DEFAULT,
+            defaultValue:
+              val.COLUMN_DEFAULT === 'NULL' ? null : val.COLUMN_DEFAULT,
             type: val.DATA_TYPE,
             maxLength: val.CHARACTER_MAXIMUM_LENGTH,
             nullable: val.IS_NULLABLE === 'YES',
@@ -156,6 +163,25 @@ class QueryCompiler_MySQL extends QueryCompiler {
     return `limit ${limit}`;
   }
 
+  whereBasic(statement) {
+    assert(
+      !isPlainObjectOrArray(statement.value),
+      'The values in where clause must not be object or array.'
+    );
+
+    return super.whereBasic(statement);
+  }
+
+  whereRaw(statement) {
+    assert(
+      isEmpty(statement.value.bindings) ||
+        !Object.values(statement.value.bindings).some(isPlainObjectOrArray),
+      'The values in where clause must not be object or array.'
+    );
+
+    return super.whereRaw(statement);
+  }
+
   whereLike(statement) {
     return `${this._columnClause(statement)} ${this._not(
       statement,
diff --git a/lib/query/querybuilder.js b/lib/query/querybuilder.js
index 947d6c40..096e35ee 100644
--- a/lib/query/querybuilder.js
+++ b/lib/query/querybuilder.js
@@ -527,6 +527,7 @@ class Builder extends EventEmitter {
   // Adds a raw `where` clause to the query.
   whereRaw(sql, bindings) {
     const raw = sql.isRawInstance ? sql : this.client.raw(sql, bindings);
+
     this._statements.push({
       grouping: 'where',
       type: 'whereRaw',
diff --git a/package.json b/package.json
index cad0bb17..94bf7f6d 100644
--- a/package.json
+++ b/package.json
@@ -132,7 +132,7 @@
     "pg-query-stream": "^4.2.4",
     "prettier": "2.6.2",
     "rimraf": "^3.0.2",
-    "sinon": "^14.0.0",
+    "sinon": "^15.0.1",
     "sinon-chai": "^3.7.0",
     "source-map-support": "^0.5.21",
     "sqlite3": "^5.0.11",
@@ -141,7 +141,7 @@
     "tedious": "^14.4.0",
     "toxiproxy-node-client": "^2.0.6",
     "ts-node": "^10.9.1",
-    "tsd": "^0.24.1",
+    "tsd": "^0.25.0",
     "typescript": "4.8.3"
   },
   "buildDependencies": [


Test patch:
diff --git a/test/db-less-test-suite.js b/test/db-less-test-suite.js
index 5a5b4003..8cac5955 100644
--- a/test/db-less-test-suite.js
+++ b/test/db-less-test-suite.js
@@ -46,6 +46,10 @@ if (config.postgres) {
   require('./unit/dialects/postgres');
 }
 
+if (config.mysql) {
+  require('./unit/dialects/mysql');
+}
+
 describe('CLI tests', function () {
   this.timeout(process.env.KNEX_TEST_TIMEOUT || 5000);
   require('./cli/help.spec');
diff --git a/test/unit/dialects/mysql.js b/test/unit/dialects/mysql.js
new file mode 100644
index 00000000..f8afe833
--- /dev/null
+++ b/test/unit/dialects/mysql.js
@@ -0,0 +1,91 @@
+'use strict';
+const expect = require('chai').expect;
+const knex = require('../../../knex');
+
+describe('MySQL unit tests', () => {
+  const qb = knex({
+    client: 'mysql',
+    connection: {
+      port: 1433,
+      host: '127.0.0.1',
+      password: 'yourStrong(!)Password',
+      user: 'sa',
+    },
+  });
+
+  it('should pass with plain values or with emtpy raw bindings', () => {
+    expect(qb('users').select('*').where('id', '=', 1).toString()).to.equal(
+      'select * from `users` where `id` = 1'
+    );
+    expect(qb('users').select('*').where({ id: 1 }).toString()).to.equal(
+      'select * from `users` where `id` = 1'
+    );
+    expect(qb('users').select('*').whereRaw('?? = ?').toString()).to.equal(
+      'select * from `users` where ?? = ?'
+    );
+  });
+
+  it('should fail if provided value is array in basic where query #1227', () => {
+    try {
+      qb('users').select('*').where('id', '=', [0]).toString();
+      throw new Error('Should not reach this point');
+    } catch (error) {
+      expect(error.message).to.equal(
+        'The values in where clause must not be object or array.'
+      );
+    }
+  });
+
+  it('should fail if provided value is object in basic where query #1227', () => {
+    try {
+      qb('users').select('*').where('id', '=', { test: 'test ' }).toString();
+      throw new Error('Should not reach this point');
+    } catch (error) {
+      expect(error.message).to.equal(
+        'The values in where clause must not be object or array.'
+      );
+    }
+  });
+
+  it('should fail if provided raw query with array value in bindings #1227', () => {
+    try {
+      qb('users')
+        .select('*')
+        .where(qb.raw('?? = ?', ['id', [0]]))
+        .toString();
+      throw new Error('Should not reach this point');
+    } catch (error) {
+      expect(error.message).to.equal(
+        'The values in where clause must not be object or array.'
+      );
+    }
+  });
+
+  it('should fail if provided raw query with object value in bindings #1227', () => {
+    try {
+      qb('users')
+        .select('*')
+        .where(qb.raw('?? = ?', ['id', { a: 1 }]))
+        .toString();
+      throw new Error('Should not reach this point');
+    } catch (error) {
+      expect(error.message).to.equal(
+        'The values in where clause must not be object or array.'
+      );
+    }
+  });
+
+  it('should fail if value in bindings is object in whereRaw #1227', () => {
+    try {
+      qb('users')
+        .select('*')
+        .whereRaw('?? = ?', ['id', { test: 'test' }])
+        .toString();
+      throw new Error('Should not reach this point');
+    } catch (error) {
+      expect(error.message).to.equal(
+        'The values in where clause must not be object or array.'
+      );
+    }
+  });
+});
diff --git a/test/unit/query/builder.js b/test/unit/query/builder.js
index db02e2a9..712c00ae 100644
--- a/test/unit/query/builder.js
+++ b/test/unit/query/builder.js
@@ -9304,18 +9304,27 @@ describe('QueryBuilder', () => {
           .from('accounts')
           .where({ Login: ['1', '2', '3', void 0] }),
         undefinedColumns: ['Login'],
+        clientError: {
+          mysql: 'The values in where clause must not be object or array.',
+        },
       },
       {
         builder: qb()
           .from('accounts')
           .where({ Login: { Test: '123', Value: void 0 } }),
         undefinedColumns: ['Login'],
+        clientError: {
+          mysql: 'The values in where clause must not be object or array.',
+        },
       },
       {
         builder: qb()
           .from('accounts')
           .where({ Login: ['1', ['2', [void 0]]] }),
         undefinedColumns: ['Login'],
+        clientError: {
+          mysql: 'The values in where clause must not be object or array.',
+        },
       },
       {
         builder: qb()
@@ -9329,10 +9338,6 @@ describe('QueryBuilder', () => {
       try {
         //Must be present, but makes no difference since it throws.
         testsql(builder, {
-          mysql: {
-            sql: '',
-            bindings: [],
-          },
           oracledb: {
             sql: '',
             bindings: [],
@@ -9350,10 +9355,7 @@ describe('QueryBuilder', () => {
             bindings: [],
           },
         });
-        expect(true).to.equal(
-          false,
-          'Expected to throw error in compilation about undefined bindings.'
-        );
+        throw new Error('Should not reach this point');
       } catch (error) {
         expect(error.message).to.contain(
           'Undefined binding(s) detected when compiling ' +
@@ -9362,6 +9364,28 @@ describe('QueryBuilder', () => {
         ); //This test is not for asserting correct queries
       }
     });
+    qbuilders.forEach(({ builder, undefinedColumns, clientError }) => {
+      try {
+        //Must be present, but makes no difference since it throws.
+        testsql(builder, {
+          mysql: {
+            sql: '',
+            bindings: [],
+          },
+        });
+        throw new Error('Should not reach this point');
+      } catch (error) {
+        if (clientError && clientError.mysql) {
+          expect(error.message).to.contain(clientError.mysql);
+        } else {
+          expect(error.message).to.contain(
+            'Undefined binding(s) detected when compiling ' +
+              builder._method.toUpperCase() +
+              `. Undefined column(s): [${undefinedColumns.join(', ')}] query:`
+          ); //This test is not for asserting correct queries
+        }
+      }
+    });
   });
 
   it('Any undefined binding in a RAW query should throw an error', () => {