CVE-2019-10752: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') (CWE-89)
value: Sequelize, all versions prior to version 4.44.3 and 5.15.1, is vulnerable to SQL Injection due to sequelize.json() helper function not escaping values properly when formatting sub paths for JSON queries for MySQL, MariaDB and SQLite.
CWE description: The product constructs all or part of an SQL command using externally-influenced input from an upstream component, but it does not neutralize or incorrectly neutralizes special elements that could modify the intended SQL command when it is sent to a downstream component. Without sufficient removal or quoting of SQL syntax in user-controllable inputs, the generated SQL query can cause those inputs to be interpreted as SQL instead of ordinary user data.
CVSS v3 base score: 9.8
Published: 2019-10-17T19:15Z
Repository: https://github.com/sequelize/sequelize
Language: JavaScript
Files changed: lib/dialects/abstract/query-generator.js, lib/dialects/mariadb/query-generator.js, lib/dialects/mysql/query-generator.js, lib/dialects/sqlite/query-generator.js, test/integration/model/json.test.js, test/unit/sql/json.test.js, test/unit/sql/where.test.js
Commit date: 2019-08-18 17:32:02 +0530
Commit message: fix(sequelize.json.fn): use common path extraction for mysql/mariadb/sqlite (#11329)


Diff: diff --git a/lib/dialects/abstract/query-generator.js b/lib/dialects/abstract/query-generator.js
index 67e5d9695..88a0f662d 100755
--- a/lib/dialects/abstract/query-generator.js
+++ b/lib/dialects/abstract/query-generator.js
@@ -1066,28 +1066,34 @@ class QueryGenerator {
       ? column
       : this.quoteIdentifier(column);
 
     switch (this.dialect) {
       case 'mysql':
+      case 'mariadb':
+      case 'sqlite':
         /**
-         * Sub paths need to be quoted as ECMAScript identifiers
+         * Non digit sub paths need to be quoted as ECMAScript identifiers
          * https://bugs.mysql.com/bug.php?id=81896
          */
-        paths = paths.map(subPath => Utils.addTicks(subPath, '"'));
-        pathStr = this.escape(['$'].concat(paths).join('.'));
-        return `(${quotedColumn}->>${pathStr})`;
-
-      case 'mariadb':
-        pathStr = this.escape(['$'].concat(paths).join('.'));
-        return `json_unquote(json_extract(${quotedColumn},${pathStr}))`;
+        if (this.dialect === 'mysql') {
+          paths = paths.map(subPath => {
+            return /\D/.test(subPath)
+              ? Utils.addTicks(subPath, '"')
+              : subPath;
+          });
+        }
 
-      case 'sqlite':
         pathStr = this.escape(['$']
           .concat(paths)
           .join('.')
-          .replace(/\.(\d+)(?:(?=\.)|$)/g, (_, digit) => `[${digit}]`));
-        return `json_extract(${quotedColumn}, ${pathStr})`;
+          .replace(/\.(\d+)(?:(?=\.)|$)/g, (__, digit) => `[${digit}]`));
+
+        if (this.dialect === 'sqlite') {
+          return `json_extract(${quotedColumn},${pathStr})`;
+        }
+
+        return `json_unquote(json_extract(${quotedColumn},${pathStr}))`;
 
       case 'postgres':
         pathStr = this.escape(`{${paths.join(',')}}`);
         return `(${quotedColumn}#>>${pathStr})`;
 
diff --git a/lib/dialects/mariadb/query-generator.js b/lib/dialects/mariadb/query-generator.js
index 8f0ce5f6d..f6d6324e6 100644
--- a/lib/dialects/mariadb/query-generator.js
+++ b/lib/dialects/mariadb/query-generator.js
@@ -1,14 +1,10 @@
 'use strict';
 
-const _ = require('lodash');
-const Utils = require('../../utils');
 const MySQLQueryGenerator = require('../mysql/query-generator');
-const util = require('util');
 
 class MariaDBQueryGenerator extends MySQLQueryGenerator {
-
   createSchema(schema, options) {
     options = Object.assign({
       charset: null,
       collate: null
     }, options || {});
@@ -29,76 +25,8 @@ class MariaDBQueryGenerator extends MySQLQueryGenerator {
   }
 
   showTablesQuery() {
     return 'SELECT TABLE_NAME, TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN (\'MYSQL\', \'INFORMATION_SCHEMA\', \'PERFORMANCE_SCHEMA\') AND TABLE_TYPE = \'BASE TABLE\'';
   }
-
-  handleSequelizeMethod(smth, tableName, factory, options, prepend) {
-    if (smth instanceof Utils.Json) {
-      // Parse nested object
-      if (smth.conditions) {
-        const conditions = this.parseConditionObject(smth.conditions).map(
-          condition =>
-            `json_unquote(json_extract(${this.quoteIdentifier(
-              condition.path[0])},'$.${_.tail(condition.path).join(
-              '.')}')) = '${condition.value}'`
-        );
-
-        return conditions.join(' and ');
-      }
-      if (smth.path) {
-        let str;
-
-        // Allow specifying conditions using the sqlite json functions
-        if (this._checkValidJsonStatement(smth.path)) {
-          str = smth.path;
-        } else {
-          // Also support json dot notation
-          let path = smth.path;
-          let startWithDot = true;
-
-          // Convert .number. to [number].
-          path = path.replace(/\.(\d+)\./g, '[$1].');
-          // Convert .number$ to [number]
-          path = path.replace(/\.(\d+)$/, '[$1]');
-
-          path = path.split('.');
-
-          let columnName = path.shift();
-          const match = columnName.match(/\[\d+\]$/);
-          // If columnName ends with [\d+]
-          if (match !== null) {
-            path.unshift(columnName.substr(match.index));
-            columnName = columnName.substr(0, match.index);
-            startWithDot = false;
-          }
-
-          str = `json_unquote(json_extract(${this.quoteIdentifier(
-            columnName)},'$${startWithDot ? '.' : ''}${path.join('.')}'))`;
-        }
-
-        if (smth.value) {
-          str += util.format(' = %s', this.escape(smth.value));
-        }
-
-        return str;
-      }
-    } else if (smth instanceof Utils.Cast) {
-      const lowType = smth.type.toLowerCase();
-      if (lowType.includes('timestamp')) {
-        smth.type = 'datetime';
-      } else if (smth.json && lowType.includes('boolean')) {
-        // true or false cannot be casted as booleans within a JSON structure
-        smth.type = 'char';
-      } else if (lowType.includes('double precision') || lowType.includes('boolean') || lowType.includes('integer')) {
-        smth.type = 'decimal';
-      } else if (lowType.includes('text')) {
-        smth.type = 'char';
-      }
-    }
-
-    return super.handleSequelizeMethod(smth, tableName, factory, options, prepend);
-  }
-
 }
 
 module.exports = MariaDBQueryGenerator;
diff --git a/lib/dialects/mysql/query-generator.js b/lib/dialects/mysql/query-generator.js
index 91458ef38..80edd8f1b 100644
--- a/lib/dialects/mysql/query-generator.js
+++ b/lib/dialects/mysql/query-generator.js
@@ -207,43 +207,26 @@ class MySQLQueryGenerator extends AbstractQueryGenerator {
   handleSequelizeMethod(smth, tableName, factory, options, prepend) {
     if (smth instanceof Utils.Json) {
       // Parse nested object
       if (smth.conditions) {
         const conditions = this.parseConditionObject(smth.conditions).map(condition =>
-          `${this.quoteIdentifier(condition.path[0])}->>'$.${_.tail(condition.path).join('.')}' = '${condition.value}'`
+          `${this.jsonPathExtractionQuery(condition.path[0], _.tail(condition.path))} = '${condition.value}'`
         );
 
-        return conditions.join(' and ');
+        return conditions.join(' AND ');
       }
       if (smth.path) {
         let str;
 
         // Allow specifying conditions using the sqlite json functions
         if (this._checkValidJsonStatement(smth.path)) {
           str = smth.path;
         } else {
-          // Also support json dot notation
-          let path = smth.path;
-          let startWithDot = true;
-
-          // Convert .number. to [number].
-          path = path.replace(/\.(\d+)\./g, '[$1].');
-          // Convert .number$ to [number]
-          path = path.replace(/\.(\d+)$/, '[$1]');
-
-          path = path.split('.');
-
-          let columnName = path.shift();
-          const match = columnName.match(/\[\d+\]$/);
-          // If columnName ends with [\d+]
-          if (match !== null) {
-            path.unshift(columnName.substr(match.index));
-            columnName = columnName.substr(0, match.index);
-            startWithDot = false;
-          }
-
-          str = `${this.quoteIdentifier(columnName)}->>'$${startWithDot ? '.' : ''}${path.join('.')}'`;
+          // Also support json property accessors
+          const paths = _.toPath(smth.path);
+          const column = paths.shift();
+          str = this.jsonPathExtractionQuery(column, paths);
         }
 
         if (smth.value) {
           str += util.format(' = %s', this.escape(smth.value));
         }
diff --git a/lib/dialects/sqlite/query-generator.js b/lib/dialects/sqlite/query-generator.js
index 66e416520..a4f734934 100644
--- a/lib/dialects/sqlite/query-generator.js
+++ b/lib/dialects/sqlite/query-generator.js
@@ -1,9 +1,8 @@
 'use strict';
 
 const Utils = require('../../utils');
-const util = require('util');
 const Transaction = require('../../transaction');
 const _ = require('lodash');
 const MySqlQueryGenerator = require('../mysql/query-generator');
 const AbstractQueryGenerator = require('../abstract/query-generator');
 
@@ -147,38 +146,14 @@ class SQLiteQueryGenerator extends MySqlQueryGenerator {
   }
 
 
   handleSequelizeMethod(smth, tableName, factory, options, prepend) {
     if (smth instanceof Utils.Json) {
-      // Parse nested object
-      if (smth.conditions) {
-        const conditions = this.parseConditionObject(smth.conditions).map(condition =>
-          `${this.jsonPathExtractionQuery(condition.path[0], _.tail(condition.path))} = '${condition.value}'`
-        );
-
-        return conditions.join(' AND ');
-      }
-      if (smth.path) {
-        let str;
-
-        // Allow specifying conditions using the sqlite json functions
-        if (this._checkValidJsonStatement(smth.path)) {
-          str = smth.path;
-        } else {
-          // Also support json property accessors
-          const paths = _.toPath(smth.path);
-          const column = paths.shift();
-          str = this.jsonPathExtractionQuery(column, paths);
-        }
-
-        if (smth.value) {
-          str += util.format(' = %s', this.escape(smth.value));
-        }
+      return super.handleSequelizeMethod(smth, tableName, factory, options, prepend);
+    }
 
-        return str;
-      }
-    } else if (smth instanceof Utils.Cast) {
+    if (smth instanceof Utils.Cast) {
       if (/timestamp/i.test(smth.type)) {
         smth.type = 'datetime';
       }
     }
 
diff --git a/test/integration/model/json.test.js b/test/integration/model/json.test.js
index 196c76df8..e1fa8ef9c 100644
--- a/test/integration/model/json.test.js
+++ b/test/integration/model/json.test.js
@@ -695,10 +695,18 @@ describe(Support.getTestDialectTeaser('Model'), () => {
               }
             }
           });
         });
 
+        it('should properly escape path keys with sequelize.json', function() {
+          return this.Model.findAll({
+            raw: true,
+            attributes: ['id'],
+            where: this.sequelize.json("data.id')) AS DECIMAL) = 1 DELETE YOLO INJECTIONS; -- ", '1')
+          });
+        });
+
         it('should properly escape the single quotes in array', function() {
           return this.Model.create({
             data: {
               type: 'Point',
               coordinates: [39.807222, "'); DELETE YOLO INJECTIONS; --"]
diff --git a/test/unit/sql/json.test.js b/test/unit/sql/json.test.js
index dcb206a0e..3b01548c7 100644
--- a/test/unit/sql/json.test.js
+++ b/test/unit/sql/json.test.js
@@ -80,67 +80,67 @@ if (current.dialect.supports.JSON) {
 
       describe('path extraction', () => {
         it('condition object', () => {
           expectsql(sql.whereItemQuery(undefined, Sequelize.json({ id: 1 })), {
             postgres: '("id"#>>\'{}\') = \'1\'',
-            sqlite: "json_extract(`id`, '$') = '1'",
-            mariadb: "json_unquote(json_extract(`id`,'$.')) = '1'",
-            mysql: "`id`->>'$.' = '1'"
+            sqlite: "json_extract(`id`,'$') = '1'",
+            mariadb: "json_unquote(json_extract(`id`,'$')) = '1'",
+            mysql: "json_unquote(json_extract(`id`,'$')) = '1'"
           });
         });
 
         it('nested condition object', () => {
           expectsql(sql.whereItemQuery(undefined, Sequelize.json({ profile: { id: 1 } })), {
             postgres: '("profile"#>>\'{id}\') = \'1\'',
-            sqlite: "json_extract(`profile`, '$.id') = '1'",
+            sqlite: "json_extract(`profile`,'$.id') = '1'",
             mariadb: "json_unquote(json_extract(`profile`,'$.id')) = '1'",
-            mysql: "`profile`->>'$.id' = '1'"
+            mysql: "json_unquote(json_extract(`profile`,'$.\\\"id\\\"')) = '1'"
           });
         });
 
         it('multiple condition object', () => {
           expectsql(sql.whereItemQuery(undefined, Sequelize.json({ property: { value: 1 }, another: { value: 'string' } })), {
             postgres: '("property"#>>\'{value}\') = \'1\' AND ("another"#>>\'{value}\') = \'string\'',
-            sqlite: "json_extract(`property`, '$.value') = '1' AND json_extract(`another`, '$.value') = 'string'",
-            mariadb: "json_unquote(json_extract(`property`,'$.value')) = '1' and json_unquote(json_extract(`another`,'$.value')) = 'string'",
-            mysql: "`property`->>'$.value' = '1' and `another`->>'$.value' = 'string'"
+            sqlite: "json_extract(`property`,'$.value') = '1' AND json_extract(`another`,'$.value') = 'string'",
+            mariadb: "json_unquote(json_extract(`property`,'$.value')) = '1' AND json_unquote(json_extract(`another`,'$.value')) = 'string'",
+            mysql: "json_unquote(json_extract(`property`,'$.\\\"value\\\"')) = '1' AND json_unquote(json_extract(`another`,'$.\\\"value\\\"')) = 'string'"
           });
         });
 
         it('property array object', () => {
           expectsql(sql.whereItemQuery(undefined, Sequelize.json({ property: [[4, 6], [8]] })), {
             postgres: '("property"#>>\'{0,0}\') = \'4\' AND ("property"#>>\'{0,1}\') = \'6\' AND ("property"#>>\'{1,0}\') = \'8\'',
-            sqlite: "json_extract(`property`, '$[0][0]') = '4' AND json_extract(`property`, '$[0][1]') = '6' AND json_extract(`property`, '$[1][0]') = '8'",
-            mariadb: "json_unquote(json_extract(`property`,'$.0.0')) = '4' and json_unquote(json_extract(`property`,'$.0.1')) = '6' and json_unquote(json_extract(`property`,'$.1.0')) = '8'",
-            mysql: "`property`->>'$.0.0' = '4' and `property`->>'$.0.1' = '6' and `property`->>'$.1.0' = '8'"
+            sqlite: "json_extract(`property`,'$[0][0]') = '4' AND json_extract(`property`,'$[0][1]') = '6' AND json_extract(`property`,'$[1][0]') = '8'",
+            mariadb: "json_unquote(json_extract(`property`,'$[0][0]')) = '4' AND json_unquote(json_extract(`property`,'$[0][1]')) = '6' AND json_unquote(json_extract(`property`,'$[1][0]')) = '8'",
+            mysql: "json_unquote(json_extract(`property`,'$[0][0]')) = '4' AND json_unquote(json_extract(`property`,'$[0][1]')) = '6' AND json_unquote(json_extract(`property`,'$[1][0]')) = '8'"
           });
         });
 
         it('dot notation', () => {
           expectsql(sql.whereItemQuery(Sequelize.json('profile.id'), '1'), {
             postgres: '("profile"#>>\'{id}\') = \'1\'',
-            sqlite: "json_extract(`profile`, '$.id') = '1'",
+            sqlite: "json_extract(`profile`,'$.id') = '1'",
             mariadb: "json_unquote(json_extract(`profile`,'$.id')) = '1'",
-            mysql: "`profile`->>'$.id' = '1'"
+            mysql: "json_unquote(json_extract(`profile`,'$.\\\"id\\\"')) = '1'"
           });
         });
 
         it('item dot notation array', () => {
           expectsql(sql.whereItemQuery(Sequelize.json('profile.id.0.1'), '1'), {
             postgres: '("profile"#>>\'{id,0,1}\') = \'1\'',
-            sqlite: "json_extract(`profile`, '$.id[0][1]') = '1'",
+            sqlite: "json_extract(`profile`,'$.id[0][1]') = '1'",
             mariadb: "json_unquote(json_extract(`profile`,'$.id[0][1]')) = '1'",
-            mysql: "`profile`->>'$.id[0][1]' = '1'"
+            mysql: "json_unquote(json_extract(`profile`,'$.\\\"id\\\"[0][1]')) = '1'"
           });
         });
 
         it('column named "json"', () => {
           expectsql(sql.whereItemQuery(Sequelize.json('json'), '{}'), {
             postgres: '("json"#>>\'{}\') = \'{}\'',
-            sqlite: "json_extract(`json`, '$') = '{}'",
-            mariadb: "json_unquote(json_extract(`json`,'$.')) = '{}'",
-            mysql: "`json`->>'$.' = '{}'"
+            sqlite: "json_extract(`json`,'$') = '{}'",
+            mariadb: "json_unquote(json_extract(`json`,'$')) = '{}'",
+            mysql: "json_unquote(json_extract(`json`,'$')) = '{}'"
           });
         });
       });
 
       describe('raw json query', () => {
diff --git a/test/unit/sql/where.test.js b/test/unit/sql/where.test.js
index 130368d22..34fd0b913 100755
--- a/test/unit/sql/where.test.js
+++ b/test/unit/sql/where.test.js
@@ -823,22 +823,22 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
     if (current.dialect.supports.JSON) {
       describe('JSON', () => {
         it('sequelize.json("profile.id"), sequelize.cast(2, \'text\')")', function() {
           expectsql(sql.whereItemQuery(undefined, this.sequelize.json('profile.id', this.sequelize.cast('12346-78912', 'text'))), {
             postgres: "(\"profile\"#>>'{id}') = CAST('12346-78912' AS TEXT)",
-            sqlite: "json_extract(`profile`, '$.id') = CAST('12346-78912' AS TEXT)",
+            sqlite: "json_extract(`profile`,'$.id') = CAST('12346-78912' AS TEXT)",
             mariadb: "json_unquote(json_extract(`profile`,'$.id')) = CAST('12346-78912' AS CHAR)",
-            mysql: "`profile`->>'$.id' = CAST('12346-78912' AS CHAR)"
+            mysql: "json_unquote(json_extract(`profile`,'$.\\\"id\\\"')) = CAST('12346-78912' AS CHAR)"
           });
         });
 
         it('sequelize.json({profile: {id: "12346-78912", name: "test"}})', function() {
           expectsql(sql.whereItemQuery(undefined, this.sequelize.json({ profile: { id: '12346-78912', name: 'test' } })), {
             postgres: "(\"profile\"#>>'{id}') = '12346-78912' AND (\"profile\"#>>'{name}') = 'test'",
-            sqlite: "json_extract(`profile`, '$.id') = '12346-78912' AND json_extract(`profile`, '$.name') = 'test'",
-            mariadb: "json_unquote(json_extract(`profile`,'$.id')) = '12346-78912' and json_unquote(json_extract(`profile`,'$.name')) = 'test'",
-            mysql: "`profile`->>'$.id' = '12346-78912' and `profile`->>'$.name' = 'test'"
+            sqlite: "json_extract(`profile`,'$.id') = '12346-78912' AND json_extract(`profile`,'$.name') = 'test'",
+            mariadb: "json_unquote(json_extract(`profile`,'$.id')) = '12346-78912' AND json_unquote(json_extract(`profile`,'$.name')) = 'test'",
+            mysql: "json_unquote(json_extract(`profile`,'$.\\\"id\\\"')) = '12346-78912' AND json_unquote(json_extract(`profile`,'$.\\\"name\\\"')) = 'test'"
           });
         });
 
         testsql('data', {
           nested: {
@@ -849,13 +849,13 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
             type: new DataTypes.JSONB()
           },
           prefix: 'User'
         }, {
           mariadb: "json_unquote(json_extract(`User`.`data`,'$.nested.attribute')) = 'value'",
-          mysql: "(`User`.`data`->>'$.\\\"nested\\\".\\\"attribute\\\"') = 'value'",
+          mysql: "json_unquote(json_extract(`User`.`data`,'$.\\\"nested\\\".\\\"attribute\\\"')) = 'value'",
           postgres: "(\"User\".\"data\"#>>'{nested,attribute}') = 'value'",
-          sqlite: "json_extract(`User`.`data`, '$.nested.attribute') = 'value'"
+          sqlite: "json_extract(`User`.`data`,'$.nested.attribute') = 'value'"
         });
 
         testsql('data', {
           nested: {
             [Op.in]: [1, 2]
@@ -864,13 +864,13 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
           field: {
             type: new DataTypes.JSONB()
           }
         }, {
           mariadb: "CAST(json_unquote(json_extract(`data`,'$.nested')) AS DECIMAL) IN (1, 2)",
-          mysql: "CAST((`data`->>'$.\\\"nested\\\"') AS DECIMAL) IN (1, 2)",
+          mysql: "CAST(json_unquote(json_extract(`data`,'$.\\\"nested\\\"')) AS DECIMAL) IN (1, 2)",
           postgres: "CAST((\"data\"#>>'{nested}') AS DOUBLE PRECISION) IN (1, 2)",
-          sqlite: "CAST(json_extract(`data`, '$.nested') AS DOUBLE PRECISION) IN (1, 2)"
+          sqlite: "CAST(json_extract(`data`,'$.nested') AS DOUBLE PRECISION) IN (1, 2)"
         });
 
         testsql('data', {
           nested: {
             [Op.between]: [1, 2]
@@ -879,13 +879,13 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
           field: {
             type: new DataTypes.JSONB()
           }
         }, {
           mariadb: "CAST(json_unquote(json_extract(`data`,'$.nested')) AS DECIMAL) BETWEEN 1 AND 2",
-          mysql: "CAST((`data`->>'$.\\\"nested\\\"') AS DECIMAL) BETWEEN 1 AND 2",
+          mysql: "CAST(json_unquote(json_extract(`data`,'$.\\\"nested\\\"')) AS DECIMAL) BETWEEN 1 AND 2",
           postgres: "CAST((\"data\"#>>'{nested}') AS DOUBLE PRECISION) BETWEEN 1 AND 2",
-          sqlite: "CAST(json_extract(`data`, '$.nested') AS DOUBLE PRECISION) BETWEEN 1 AND 2"
+          sqlite: "CAST(json_extract(`data`,'$.nested') AS DOUBLE PRECISION) BETWEEN 1 AND 2"
         });
 
         testsql('data', {
           nested: {
             attribute: 'value',
@@ -898,13 +898,13 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
             type: new DataTypes.JSONB()
           },
           prefix: current.literal(sql.quoteTable.call(current.dialect.QueryGenerator, { tableName: 'User' }))
         }, {
           mariadb: "(json_unquote(json_extract(`User`.`data`,'$.nested.attribute')) = 'value' AND json_unquote(json_extract(`User`.`data`,'$.nested.prop')) != 'None')",
-          mysql: "((`User`.`data`->>'$.\\\"nested\\\".\\\"attribute\\\"') = 'value' AND (`User`.`data`->>'$.\\\"nested\\\".\\\"prop\\\"') != 'None')",
+          mysql: "(json_unquote(json_extract(`User`.`data`,'$.\\\"nested\\\".\\\"attribute\\\"')) = 'value' AND json_unquote(json_extract(`User`.`data`,'$.\\\"nested\\\".\\\"prop\\\"')) != 'None')",
           postgres: "((\"User\".\"data\"#>>'{nested,attribute}') = 'value' AND (\"User\".\"data\"#>>'{nested,prop}') != 'None')",
-          sqlite: "(json_extract(`User`.`data`, '$.nested.attribute') = 'value' AND json_extract(`User`.`data`, '$.nested.prop') != 'None')"
+          sqlite: "(json_extract(`User`.`data`,'$.nested.attribute') = 'value' AND json_extract(`User`.`data`,'$.nested.prop') != 'None')"
         });
 
         testsql('data', {
           name: {
             last: 'Simpson'
@@ -917,13 +917,13 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
             type: new DataTypes.JSONB()
           },
           prefix: 'User'
         }, {
           mariadb: "(json_unquote(json_extract(`User`.`data`,'$.name.last')) = 'Simpson' AND json_unquote(json_extract(`User`.`data`,'$.employment')) != 'None')",
-          mysql: "((`User`.`data`->>'$.\\\"name\\\".\\\"last\\\"') = 'Simpson' AND (`User`.`data`->>'$.\\\"employment\\\"') != 'None')",
+          mysql: "(json_unquote(json_extract(`User`.`data`,'$.\\\"name\\\".\\\"last\\\"')) = 'Simpson' AND json_unquote(json_extract(`User`.`data`,'$.\\\"employment\\\"')) != 'None')",
           postgres: "((\"User\".\"data\"#>>'{name,last}') = 'Simpson' AND (\"User\".\"data\"#>>'{employment}') != 'None')",
-          sqlite: "(json_extract(`User`.`data`, '$.name.last') = 'Simpson' AND json_extract(`User`.`data`, '$.employment') != 'None')"
+          sqlite: "(json_extract(`User`.`data`,'$.name.last') = 'Simpson' AND json_extract(`User`.`data`,'$.employment') != 'None')"
         });
 
         testsql('data', {
           price: 5,
           name: 'Product'
@@ -931,13 +931,13 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
           field: {
             type: new DataTypes.JSONB()
           }
         }, {
           mariadb: "(CAST(json_unquote(json_extract(`data`,'$.price')) AS DECIMAL) = 5 AND json_unquote(json_extract(`data`,'$.name')) = 'Product')",
-          mysql: "(CAST((`data`->>'$.\\\"price\\\"') AS DECIMAL) = 5 AND (`data`->>'$.\\\"name\\\"') = 'Product')",
+          mysql: "(CAST(json_unquote(json_extract(`data`,'$.\\\"price\\\"')) AS DECIMAL) = 5 AND json_unquote(json_extract(`data`,'$.\\\"name\\\"')) = 'Product')",
           postgres: "(CAST((\"data\"#>>'{price}') AS DOUBLE PRECISION) = 5 AND (\"data\"#>>'{name}') = 'Product')",
-          sqlite: "(CAST(json_extract(`data`, '$.price') AS DOUBLE PRECISION) = 5 AND json_extract(`data`, '$.name') = 'Product')"
+          sqlite: "(CAST(json_extract(`data`,'$.price') AS DOUBLE PRECISION) = 5 AND json_extract(`data`,'$.name') = 'Product')"
         });
 
         testsql('data.nested.attribute', 'value', {
           model: {
             rawAttributes: {
@@ -946,13 +946,13 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
               }
             }
           }
         }, {
           mariadb: "json_unquote(json_extract(`data`,'$.nested.attribute')) = 'value'",
-          mysql: "(`data`->>'$.\\\"nested\\\".\\\"attribute\\\"') = 'value'",
+          mysql: "json_unquote(json_extract(`data`,'$.\\\"nested\\\".\\\"attribute\\\"')) = 'value'",
           postgres: "(\"data\"#>>'{nested,attribute}') = 'value'",
-          sqlite: "json_extract(`data`, '$.nested.attribute') = 'value'"
+          sqlite: "json_extract(`data`,'$.nested.attribute') = 'value'"
         });
 
         testsql('data.nested.attribute', 4, {
           model: {
             rawAttributes: {
@@ -961,13 +961,13 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
               }
             }
           }
         }, {
           mariadb: "CAST(json_unquote(json_extract(`data`,'$.nested.attribute')) AS DECIMAL) = 4",
-          mysql: "CAST((`data`->>'$.\\\"nested\\\".\\\"attribute\\\"') AS DECIMAL) = 4",
+          mysql: "CAST(json_unquote(json_extract(`data`,'$.\\\"nested\\\".\\\"attribute\\\"')) AS DECIMAL) = 4",
           postgres: "CAST((\"data\"#>>'{nested,attribute}') AS DOUBLE PRECISION) = 4",
-          sqlite: "CAST(json_extract(`data`, '$.nested.attribute') AS DOUBLE PRECISION) = 4"
+          sqlite: "CAST(json_extract(`data`,'$.nested.attribute') AS DOUBLE PRECISION) = 4"
         });
 
         testsql('data.nested.attribute', {
           [Op.in]: [3, 7]
         }, {
@@ -978,13 +978,13 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
               }
             }
           }
         }, {
           mariadb: "CAST(json_unquote(json_extract(`data`,'$.nested.attribute')) AS DECIMAL) IN (3, 7)",
-          mysql: "CAST((`data`->>'$.\\\"nested\\\".\\\"attribute\\\"') AS DECIMAL) IN (3, 7)",
+          mysql: "CAST(json_unquote(json_extract(`data`,'$.\\\"nested\\\".\\\"attribute\\\"')) AS DECIMAL) IN (3, 7)",
           postgres: "CAST((\"data\"#>>'{nested,attribute}') AS DOUBLE PRECISION) IN (3, 7)",
-          sqlite: "CAST(json_extract(`data`, '$.nested.attribute') AS DOUBLE PRECISION) IN (3, 7)"
+          sqlite: "CAST(json_extract(`data`,'$.nested.attribute') AS DOUBLE PRECISION) IN (3, 7)"
         });
 
         testsql('data', {
           nested: {
             attribute: {
@@ -995,13 +995,13 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
           field: {
             type: new DataTypes.JSONB()
           }
         }, {
           mariadb: "CAST(json_unquote(json_extract(`data`,'$.nested.attribute')) AS DECIMAL) > 2",
-          mysql: "CAST((`data`->>'$.\\\"nested\\\".\\\"attribute\\\"') AS DECIMAL) > 2",
+          mysql: "CAST(json_unquote(json_extract(`data`,'$.\\\"nested\\\".\\\"attribute\\\"')) AS DECIMAL) > 2",
           postgres: "CAST((\"data\"#>>'{nested,attribute}') AS DOUBLE PRECISION) > 2",
-          sqlite: "CAST(json_extract(`data`, '$.nested.attribute') AS DOUBLE PRECISION) > 2"
+          sqlite: "CAST(json_extract(`data`,'$.nested.attribute') AS DOUBLE PRECISION) > 2"
         });
 
         testsql('data', {
           nested: {
             'attribute::integer': {
@@ -1012,13 +1012,13 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
           field: {
             type: new DataTypes.JSONB()
           }
         }, {
           mariadb: "CAST(json_unquote(json_extract(`data`,'$.nested.attribute')) AS DECIMAL) > 2",
-          mysql: "CAST((`data`->>'$.\\\"nested\\\".\\\"attribute\\\"') AS DECIMAL) > 2",
+          mysql: "CAST(json_unquote(json_extract(`data`,'$.\\\"nested\\\".\\\"attribute\\\"')) AS DECIMAL) > 2",
           postgres: "CAST((\"data\"#>>'{nested,attribute}') AS INTEGER) > 2",
-          sqlite: "CAST(json_extract(`data`, '$.nested.attribute') AS INTEGER) > 2"
+          sqlite: "CAST(json_extract(`data`,'$.nested.attribute') AS INTEGER) > 2"
         });
 
         const dt = new Date();
         testsql('data', {
           nested: {
@@ -1030,13 +1030,13 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
           field: {
             type: new DataTypes.JSONB()
           }
         }, {
           mariadb: `CAST(json_unquote(json_extract(\`data\`,'$.nested.attribute')) AS DATETIME) > ${sql.escape(dt)}`,
-          mysql: `CAST((\`data\`->>'$.\\"nested\\".\\"attribute\\"') AS DATETIME) > ${sql.escape(dt)}`,
+          mysql: `CAST(json_unquote(json_extract(\`data\`,'$.\\"nested\\".\\"attribute\\"')) AS DATETIME) > ${sql.escape(dt)}`,
           postgres: `CAST(("data"#>>'{nested,attribute}') AS TIMESTAMPTZ) > ${sql.escape(dt)}`,
-          sqlite: `json_extract(\`data\`, '$.nested.attribute') > ${sql.escape(dt.toISOString())}`
+          sqlite: `json_extract(\`data\`,'$.nested.attribute') > ${sql.escape(dt.toISOString())}`
         });
 
         testsql('data', {
           nested: {
             attribute: true
@@ -1045,13 +1045,13 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
           field: {
             type: new DataTypes.JSONB()
           }
         }, {
           mariadb: "json_unquote(json_extract(`data`,'$.nested.attribute')) = 'true'",
-          mysql: "(`data`->>'$.\\\"nested\\\".\\\"attribute\\\"') = 'true'",
+          mysql: "json_unquote(json_extract(`data`,'$.\\\"nested\\\".\\\"attribute\\\"')) = 'true'",
           postgres: "CAST((\"data\"#>>'{nested,attribute}') AS BOOLEAN) = true",
-          sqlite: "CAST(json_extract(`data`, '$.nested.attribute') AS BOOLEAN) = 1"
+          sqlite: "CAST(json_extract(`data`,'$.nested.attribute') AS BOOLEAN) = 1"
         });
 
         testsql('metaData.nested.attribute', 'value', {
           model: {
             rawAttributes: {
@@ -1062,13 +1062,13 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
               }
             }
           }
         }, {
           mariadb: "json_unquote(json_extract(`meta_data`,'$.nested.attribute')) = 'value'",
-          mysql: "(`meta_data`->>'$.\\\"nested\\\".\\\"attribute\\\"') = 'value'",
+          mysql: "json_unquote(json_extract(`meta_data`,'$.\\\"nested\\\".\\\"attribute\\\"')) = 'value'",
           postgres: "(\"meta_data\"#>>'{nested,attribute}') = 'value'",
-          sqlite: "json_extract(`meta_data`, '$.nested.attribute') = 'value'"
+          sqlite: "json_extract(`meta_data`,'$.nested.attribute') = 'value'"
         });
       });
     }
 
     if (current.dialect.supports.JSONB) {

Vulnerable code:          * Sub paths need to be quoted as ECMAScript identifiers
        paths = paths.map(subPath => Utils.addTicks(subPath, '"'));
        pathStr = this.escape(['$'].concat(paths).join('.'));
        return `(${quotedColumn}->>${pathStr})`;

      case 'mariadb':
        pathStr = this.escape(['$'].concat(paths).join('.'));
        return `json_unquote(json_extract(${quotedColumn},${pathStr}))`;
      case 'sqlite':
          .replace(/\.(\d+)(?:(?=\.)|$)/g, (_, digit) => `[${digit}]`));
        return `json_extract(${quotedColumn}, ${pathStr})`;
const _ = require('lodash');
const Utils = require('../../utils');
const util = require('util');


  handleSequelizeMethod(smth, tableName, factory, options, prepend) {
    if (smth instanceof Utils.Json) {
      // Parse nested object
      if (smth.conditions) {
        const conditions = this.parseConditionObject(smth.conditions).map(
          condition =>
            `json_unquote(json_extract(${this.quoteIdentifier(
              condition.path[0])},'$.${_.tail(condition.path).join(
              '.')}')) = '${condition.value}'`
        );

        return conditions.join(' and ');
      }
      if (smth.path) {
        let str;

        // Allow specifying conditions using the sqlite json functions
        if (this._checkValidJsonStatement(smth.path)) {
          str = smth.path;
        } else {
          // Also support json dot notation
          let path = smth.path;
          let startWithDot = true;

          // Convert .number. to [number].
          path = path.replace(/\.(\d+)\./g, '[$1].');
          // Convert .number$ to [number]
          path = path.replace(/\.(\d+)$/, '[$1]');

          path = path.split('.');

          let columnName = path.shift();
          const match = columnName.match(/\[\d+\]$/);
          // If columnName ends with [\d+]
          if (match !== null) {
            path.unshift(columnName.substr(match.index));
            columnName = columnName.substr(0, match.index);
            startWithDot = false;
          }

          str = `json_unquote(json_extract(${this.quoteIdentifier(
            columnName)},'$${startWithDot ? '.' : ''}${path.join('.')}'))`;
        }

        if (smth.value) {
          str += util.format(' = %s', this.escape(smth.value));
        }

        return str;
      }
    } else if (smth instanceof Utils.Cast) {
      const lowType = smth.type.toLowerCase();
      if (lowType.includes('timestamp')) {
        smth.type = 'datetime';
      } else if (smth.json && lowType.includes('boolean')) {
        // true or false cannot be casted as booleans within a JSON structure
        smth.type = 'char';
      } else if (lowType.includes('double precision') || lowType.includes('boolean') || lowType.includes('integer')) {
        smth.type = 'decimal';
      } else if (lowType.includes('text')) {
        smth.type = 'char';
      }
    }

    return super.handleSequelizeMethod(smth, tableName, factory, options, prepend);
  }

          `${this.quoteIdentifier(condition.path[0])}->>'$.${_.tail(condition.path).join('.')}' = '${condition.value}'`
        return conditions.join(' and ');
          // Also support json dot notation
          let path = smth.path;
          let startWithDot = true;

          // Convert .number. to [number].
          path = path.replace(/\.(\d+)\./g, '[$1].');
          // Convert .number$ to [number]
          path = path.replace(/\.(\d+)$/, '[$1]');

          path = path.split('.');

          let columnName = path.shift();
          const match = columnName.match(/\[\d+\]$/);
          // If columnName ends with [\d+]
          if (match !== null) {
            path.unshift(columnName.substr(match.index));
            columnName = columnName.substr(0, match.index);
            startWithDot = false;
          }

          str = `${this.quoteIdentifier(columnName)}->>'$${startWithDot ? '.' : ''}${path.join('.')}'`;
const util = require('util');
      // Parse nested object
      if (smth.conditions) {
        const conditions = this.parseConditionObject(smth.conditions).map(condition =>
          `${this.jsonPathExtractionQuery(condition.path[0], _.tail(condition.path))} = '${condition.value}'`
        );

        return conditions.join(' AND ');
      }
      if (smth.path) {
        let str;

        // Allow specifying conditions using the sqlite json functions
        if (this._checkValidJsonStatement(smth.path)) {
          str = smth.path;
        } else {
          // Also support json property accessors
          const paths = _.toPath(smth.path);
          const column = paths.shift();
          str = this.jsonPathExtractionQuery(column, paths);
        }

        if (smth.value) {
          str += util.format(' = %s', this.escape(smth.value));
        }
        return str;
      }
    } else if (smth instanceof Utils.Cast) {
            sqlite: "json_extract(`id`, '$') = '1'",
            mariadb: "json_unquote(json_extract(`id`,'$.')) = '1'",
            mysql: "`id`->>'$.' = '1'"
            sqlite: "json_extract(`profile`, '$.id') = '1'",
            mysql: "`profile`->>'$.id' = '1'"
            sqlite: "json_extract(`property`, '$.value') = '1' AND json_extract(`another`, '$.value') = 'string'",
            mariadb: "json_unquote(json_extract(`property`,'$.value')) = '1' and json_unquote(json_extract(`another`,'$.value')) = 'string'",
            mysql: "`property`->>'$.value' = '1' and `another`->>'$.value' = 'string'"
            sqlite: "json_extract(`property`, '$[0][0]') = '4' AND json_extract(`property`, '$[0][1]') = '6' AND json_extract(`property`, '$[1][0]') = '8'",
            mariadb: "json_unquote(json_extract(`property`,'$.0.0')) = '4' and json_unquote(json_extract(`property`,'$.0.1')) = '6' and json_unquote(json_extract(`property`,'$.1.0')) = '8'",
            mysql: "`property`->>'$.0.0' = '4' and `property`->>'$.0.1' = '6' and `property`->>'$.1.0' = '8'"
            sqlite: "json_extract(`profile`, '$.id') = '1'",
            mysql: "`profile`->>'$.id' = '1'"
            sqlite: "json_extract(`profile`, '$.id[0][1]') = '1'",
            mysql: "`profile`->>'$.id[0][1]' = '1'"
            sqlite: "json_extract(`json`, '$') = '{}'",
            mariadb: "json_unquote(json_extract(`json`,'$.')) = '{}'",
            mysql: "`json`->>'$.' = '{}'"
            sqlite: "json_extract(`profile`, '$.id') = CAST('12346-78912' AS TEXT)",
            mysql: "`profile`->>'$.id' = CAST('12346-78912' AS CHAR)"
            sqlite: "json_extract(`profile`, '$.id') = '12346-78912' AND json_extract(`profile`, '$.name') = 'test'",
            mariadb: "json_unquote(json_extract(`profile`,'$.id')) = '12346-78912' and json_unquote(json_extract(`profile`,'$.name')) = 'test'",
            mysql: "`profile`->>'$.id' = '12346-78912' and `profile`->>'$.name' = 'test'"
          mysql: "(`User`.`data`->>'$.\\\"nested\\\".\\\"attribute\\\"') = 'value'",
          sqlite: "json_extract(`User`.`data`, '$.nested.attribute') = 'value'"
          mysql: "CAST((`data`->>'$.\\\"nested\\\"') AS DECIMAL) IN (1, 2)",
          sqlite: "CAST(json_extract(`data`, '$.nested') AS DOUBLE PRECISION) IN (1, 2)"
          mysql: "CAST((`data`->>'$.\\\"nested\\\"') AS DECIMAL) BETWEEN 1 AND 2",
          sqlite: "CAST(json_extract(`data`, '$.nested') AS DOUBLE PRECISION) BETWEEN 1 AND 2"
          mysql: "((`User`.`data`->>'$.\\\"nested\\\".\\\"attribute\\\"') = 'value' AND (`User`.`data`->>'$.\\\"nested\\\".\\\"prop\\\"') != 'None')",
          sqlite: "(json_extract(`User`.`data`, '$.nested.attribute') = 'value' AND json_extract(`User`.`data`, '$.nested.prop') != 'None')"
          mysql: "((`User`.`data`->>'$.\\\"name\\\".\\\"last\\\"') = 'Simpson' AND (`User`.`data`->>'$.\\\"employment\\\"') != 'None')",
          sqlite: "(json_extract(`User`.`data`, '$.name.last') = 'Simpson' AND json_extract(`User`.`data`, '$.employment') != 'None')"
          mysql: "(CAST((`data`->>'$.\\\"price\\\"') AS DECIMAL) = 5 AND (`data`->>'$.\\\"name\\\"') = 'Product')",
          sqlite: "(CAST(json_extract(`data`, '$.price') AS DOUBLE PRECISION) = 5 AND json_extract(`data`, '$.name') = 'Product')"
          mysql: "(`data`->>'$.\\\"nested\\\".\\\"attribute\\\"') = 'value'",
          sqlite: "json_extract(`data`, '$.nested.attribute') = 'value'"
          mysql: "CAST((`data`->>'$.\\\"nested\\\".\\\"attribute\\\"') AS DECIMAL) = 4",
          sqlite: "CAST(json_extract(`data`, '$.nested.attribute') AS DOUBLE PRECISION) = 4"
          mysql: "CAST((`data`->>'$.\\\"nested\\\".\\\"attribute\\\"') AS DECIMAL) IN (3, 7)",
          sqlite: "CAST(json_extract(`data`, '$.nested.attribute') AS DOUBLE PRECISION) IN (3, 7)"
          mysql: "CAST((`data`->>'$.\\\"nested\\\".\\\"attribute\\\"') AS DECIMAL) > 2",
          sqlite: "CAST(json_extract(`data`, '$.nested.attribute') AS DOUBLE PRECISION) > 2"
          mysql: "CAST((`data`->>'$.\\\"nested\\\".\\\"attribute\\\"') AS DECIMAL) > 2",
          sqlite: "CAST(json_extract(`data`, '$.nested.attribute') AS INTEGER) > 2"
          mysql: `CAST((\`data\`->>'$.\\"nested\\".\\"attribute\\"') AS DATETIME) > ${sql.escape(dt)}`,
          sqlite: `json_extract(\`data\`, '$.nested.attribute') > ${sql.escape(dt.toISOString())}`
          mysql: "(`data`->>'$.\\\"nested\\\".\\\"attribute\\\"') = 'true'",
          sqlite: "CAST(json_extract(`data`, '$.nested.attribute') AS BOOLEAN) = 1"
          mysql: "(`meta_data`->>'$.\\\"nested\\\".\\\"attribute\\\"') = 'value'",
          sqlite: "json_extract(`meta_data`, '$.nested.attribute') = 'value'"
Fixed code:       case 'mariadb':
      case 'sqlite':
         * Non digit sub paths need to be quoted as ECMAScript identifiers
        if (this.dialect === 'mysql') {
          paths = paths.map(subPath => {
            return /\D/.test(subPath)
              ? Utils.addTicks(subPath, '"')
              : subPath;
          });
        }
          .replace(/\.(\d+)(?:(?=\.)|$)/g, (__, digit) => `[${digit}]`));

        if (this.dialect === 'sqlite') {
          return `json_extract(${quotedColumn},${pathStr})`;
        }

        return `json_unquote(json_extract(${quotedColumn},${pathStr}))`;
          `${this.jsonPathExtractionQuery(condition.path[0], _.tail(condition.path))} = '${condition.value}'`
        return conditions.join(' AND ');
          // Also support json property accessors
          const paths = _.toPath(smth.path);
          const column = paths.shift();
          str = this.jsonPathExtractionQuery(column, paths);
      return super.handleSequelizeMethod(smth, tableName, factory, options, prepend);
    }
    if (smth instanceof Utils.Cast) {
        it('should properly escape path keys with sequelize.json', function() {
          return this.Model.findAll({
            raw: true,
            attributes: ['id'],
            where: this.sequelize.json("data.id')) AS DECIMAL) = 1 DELETE YOLO INJECTIONS; -- ", '1')
          });
        });

            sqlite: "json_extract(`id`,'$') = '1'",
            mariadb: "json_unquote(json_extract(`id`,'$')) = '1'",
            mysql: "json_unquote(json_extract(`id`,'$')) = '1'"
            sqlite: "json_extract(`profile`,'$.id') = '1'",
            mysql: "json_unquote(json_extract(`profile`,'$.\\\"id\\\"')) = '1'"
            sqlite: "json_extract(`property`,'$.value') = '1' AND json_extract(`another`,'$.value') = 'string'",
            mariadb: "json_unquote(json_extract(`property`,'$.value')) = '1' AND json_unquote(json_extract(`another`,'$.value')) = 'string'",
            mysql: "json_unquote(json_extract(`property`,'$.\\\"value\\\"')) = '1' AND json_unquote(json_extract(`another`,'$.\\\"value\\\"')) = 'string'"
            sqlite: "json_extract(`property`,'$[0][0]') = '4' AND json_extract(`property`,'$[0][1]') = '6' AND json_extract(`property`,'$[1][0]') = '8'",
            mariadb: "json_unquote(json_extract(`property`,'$[0][0]')) = '4' AND json_unquote(json_extract(`property`,'$[0][1]')) = '6' AND json_unquote(json_extract(`property`,'$[1][0]')) = '8'",
            mysql: "json_unquote(json_extract(`property`,'$[0][0]')) = '4' AND json_unquote(json_extract(`property`,'$[0][1]')) = '6' AND json_unquote(json_extract(`property`,'$[1][0]')) = '8'"
            sqlite: "json_extract(`profile`,'$.id') = '1'",
            mysql: "json_unquote(json_extract(`profile`,'$.\\\"id\\\"')) = '1'"
            sqlite: "json_extract(`profile`,'$.id[0][1]') = '1'",
            mysql: "json_unquote(json_extract(`profile`,'$.\\\"id\\\"[0][1]')) = '1'"
            sqlite: "json_extract(`json`,'$') = '{}'",
            mariadb: "json_unquote(json_extract(`json`,'$')) = '{}'",
            mysql: "json_unquote(json_extract(`json`,'$')) = '{}'"
            sqlite: "json_extract(`profile`,'$.id') = CAST('12346-78912' AS TEXT)",
            mysql: "json_unquote(json_extract(`profile`,'$.\\\"id\\\"')) = CAST('12346-78912' AS CHAR)"
            sqlite: "json_extract(`profile`,'$.id') = '12346-78912' AND json_extract(`profile`,'$.name') = 'test'",
            mariadb: "json_unquote(json_extract(`profile`,'$.id')) = '12346-78912' AND json_unquote(json_extract(`profile`,'$.name')) = 'test'",
            mysql: "json_unquote(json_extract(`profile`,'$.\\\"id\\\"')) = '12346-78912' AND json_unquote(json_extract(`profile`,'$.\\\"name\\\"')) = 'test'"
          mysql: "json_unquote(json_extract(`User`.`data`,'$.\\\"nested\\\".\\\"attribute\\\"')) = 'value'",
          sqlite: "json_extract(`User`.`data`,'$.nested.attribute') = 'value'"
          mysql: "CAST(json_unquote(json_extract(`data`,'$.\\\"nested\\\"')) AS DECIMAL) IN (1, 2)",
          sqlite: "CAST(json_extract(`data`,'$.nested') AS DOUBLE PRECISION) IN (1, 2)"
          mysql: "CAST(json_unquote(json_extract(`data`,'$.\\\"nested\\\"')) AS DECIMAL) BETWEEN 1 AND 2",
          sqlite: "CAST(json_extract(`data`,'$.nested') AS DOUBLE PRECISION) BETWEEN 1 AND 2"
          mysql: "(json_unquote(json_extract(`User`.`data`,'$.\\\"nested\\\".\\\"attribute\\\"')) = 'value' AND json_unquote(json_extract(`User`.`data`,'$.\\\"nested\\\".\\\"prop\\\"')) != 'None')",
          sqlite: "(json_extract(`User`.`data`,'$.nested.attribute') = 'value' AND json_extract(`User`.`data`,'$.nested.prop') != 'None')"
          mysql: "(json_unquote(json_extract(`User`.`data`,'$.\\\"name\\\".\\\"last\\\"')) = 'Simpson' AND json_unquote(json_extract(`User`.`data`,'$.\\\"employment\\\"')) != 'None')",
          sqlite: "(json_extract(`User`.`data`,'$.name.last') = 'Simpson' AND json_extract(`User`.`data`,'$.employment') != 'None')"
          mysql: "(CAST(json_unquote(json_extract(`data`,'$.\\\"price\\\"')) AS DECIMAL) = 5 AND json_unquote(json_extract(`data`,'$.\\\"name\\\"')) = 'Product')",
          sqlite: "(CAST(json_extract(`data`,'$.price') AS DOUBLE PRECISION) = 5 AND json_extract(`data`,'$.name') = 'Product')"
          mysql: "json_unquote(json_extract(`data`,'$.\\\"nested\\\".\\\"attribute\\\"')) = 'value'",
          sqlite: "json_extract(`data`,'$.nested.attribute') = 'value'"
          mysql: "CAST(json_unquote(json_extract(`data`,'$.\\\"nested\\\".\\\"attribute\\\"')) AS DECIMAL) = 4",
          sqlite: "CAST(json_extract(`data`,'$.nested.attribute') AS DOUBLE PRECISION) = 4"
          mysql: "CAST(json_unquote(json_extract(`data`,'$.\\\"nested\\\".\\\"attribute\\\"')) AS DECIMAL) IN (3, 7)",
          sqlite: "CAST(json_extract(`data`,'$.nested.attribute') AS DOUBLE PRECISION) IN (3, 7)"
          mysql: "CAST(json_unquote(json_extract(`data`,'$.\\\"nested\\\".\\\"attribute\\\"')) AS DECIMAL) > 2",
          sqlite: "CAST(json_extract(`data`,'$.nested.attribute') AS DOUBLE PRECISION) > 2"
          mysql: "CAST(json_unquote(json_extract(`data`,'$.\\\"nested\\\".\\\"attribute\\\"')) AS DECIMAL) > 2",
          sqlite: "CAST(json_extract(`data`,'$.nested.attribute') AS INTEGER) > 2"
          mysql: `CAST(json_unquote(json_extract(\`data\`,'$.\\"nested\\".\\"attribute\\"')) AS DATETIME) > ${sql.escape(dt)}`,
          sqlite: `json_extract(\`data\`,'$.nested.attribute') > ${sql.escape(dt.toISOString())}`
          mysql: "json_unquote(json_extract(`data`,'$.\\\"nested\\\".\\\"attribute\\\"')) = 'true'",
          sqlite: "CAST(json_extract(`data`,'$.nested.attribute') AS BOOLEAN) = 1"
          mysql: "json_unquote(json_extract(`meta_data`,'$.\\\"nested\\\".\\\"attribute\\\"')) = 'value'",
          sqlite: "json_extract(`meta_data`,'$.nested.attribute') = 'value'"