fix(sequelize.json.fn): use common path extraction for mysql/mariadb/… · sequelize/sequelize@9bd0bc1 · GitHub
Skip to content

Commit 9bd0bc1

Browse files
sushantdhimansushantdhiman
authored
fix(sequelize.json.fn): use common path extraction for mysql/mariadb/sqlite (#11329)
1 parent 83e263b commit 9bd0bc1

7 files changed

Lines changed: 85 additions & 185 deletions

File tree

‎lib/dialects/abstract/query-generator.js‎

Lines changed: 17 additions & 11 deletions

‎lib/dialects/mariadb/query-generator.js‎

Lines changed: 0 additions & 72 deletions
Original file line numberDiff line numberDiff line change
@@ -1,12 +1,8 @@
11
'use strict';
22

3-
const _ = require('lodash');
4-
const Utils = require('../../utils');
53
const MySQLQueryGenerator = require('../mysql/query-generator');
6-
const util = require('util');
74

85
class MariaDBQueryGenerator extends MySQLQueryGenerator {
9-
106
createSchema(schema, options) {
117
options = Object.assign({
128
charset: null,
@@ -31,74 +27,6 @@ class MariaDBQueryGenerator extends MySQLQueryGenerator {
3127
showTablesQuery() {
3228
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\'';
3329
}
34-
35-
handleSequelizeMethod(smth, tableName, factory, options, prepend) {
36-
if (smth instanceof Utils.Json) {
37-
// Parse nested object
38-
if (smth.conditions) {
39-
const conditions = this.parseConditionObject(smth.conditions).map(
40-
condition =>
41-
`json_unquote(json_extract(${this.quoteIdentifier(
42-
condition.path[0])},'$.${_.tail(condition.path).join(
43-
'.')}')) = '${condition.value}'`
44-
);
45-
46-
return conditions.join(' and ');
47-
}
48-
if (smth.path) {
49-
let str;
50-
51-
// Allow specifying conditions using the sqlite json functions
52-
if (this._checkValidJsonStatement(smth.path)) {
53-
str = smth.path;
54-
} else {
55-
// Also support json dot notation
56-
let path = smth.path;
57-
let startWithDot = true;
58-
59-
// Convert .number. to [number].
60-
path = path.replace(/\.(\d+)\./g, '[$1].');
61-
// Convert .number$ to [number]
62-
path = path.replace(/\.(\d+)$/, '[$1]');
63-
64-
path = path.split('.');
65-
66-
let columnName = path.shift();
67-
const match = columnName.match(/\[\d+\]$/);
68-
// If columnName ends with [\d+]
69-
if (match !== null) {
70-
path.unshift(columnName.substr(match.index));
71-
columnName = columnName.substr(0, match.index);
72-
startWithDot = false;
73-
}
74-
75-
str = `json_unquote(json_extract(${this.quoteIdentifier(
76-
columnName)},'$${startWithDot ? '.' : ''}${path.join('.')}'))`;
77-
}
78-
79-
if (smth.value) {
80-
str += util.format(' = %s', this.escape(smth.value));
81-
}
82-
83-
return str;
84-
}
85-
} else if (smth instanceof Utils.Cast) {
86-
const lowType = smth.type.toLowerCase();
87-
if (lowType.includes('timestamp')) {
88-
smth.type = 'datetime';
89-
} else if (smth.json && lowType.includes('boolean')) {
90-
// true or false cannot be casted as booleans within a JSON structure
91-
smth.type = 'char';
92-
} else if (lowType.includes('double precision') || lowType.includes('boolean') || lowType.includes('integer')) {
93-
smth.type = 'decimal';
94-
} else if (lowType.includes('text')) {
95-
smth.type = 'char';
96-
}
97-
}
98-
99-
return super.handleSequelizeMethod(smth, tableName, factory, options, prepend);
100-
}
101-
10230
}
10331

10432
module.exports = MariaDBQueryGenerator;

‎lib/dialects/mysql/query-generator.js‎

Lines changed: 6 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -209,10 +209,10 @@ class MySQLQueryGenerator extends AbstractQueryGenerator {
209209
// Parse nested object
210210
if (smth.conditions) {
211211
const conditions = this.parseConditionObject(smth.conditions).map(condition =>
212-
`${this.quoteIdentifier(condition.path[0])}->>'$.${_.tail(condition.path).join('.')}' = '${condition.value}'`
212+
`${this.jsonPathExtractionQuery(condition.path[0], _.tail(condition.path))} = '${condition.value}'`
213213
);
214214

215-
return conditions.join(' and ');
215+
return conditions.join(' AND ');
216216
}
217217
if (smth.path) {
218218
let str;
@@ -221,27 +221,10 @@ class MySQLQueryGenerator extends AbstractQueryGenerator {
221221
if (this._checkValidJsonStatement(smth.path)) {
222222
str = smth.path;
223223
} else {
224-
// Also support json dot notation
225-
let path = smth.path;
226-
let startWithDot = true;
227-
228-
// Convert .number. to [number].
229-
path = path.replace(/\.(\d+)\./g, '[$1].');
230-
// Convert .number$ to [number]
231-
path = path.replace(/\.(\d+)$/, '[$1]');
232-
233-
path = path.split('.');
234-
235-
let columnName = path.shift();
236-
const match = columnName.match(/\[\d+\]$/);
237-
// If columnName ends with [\d+]
238-
if (match !== null) {
239-
path.unshift(columnName.substr(match.index));
240-
columnName = columnName.substr(0, match.index);
241-
startWithDot = false;
242-
}
243-
244-
str = `${this.quoteIdentifier(columnName)}->>'$${startWithDot ? '.' : ''}${path.join('.')}'`;
224+
// Also support json property accessors
225+
const paths = _.toPath(smth.path);
226+
const column = paths.shift();
227+
str = this.jsonPathExtractionQuery(column, paths);
245228
}
246229

247230
if (smth.value) {

‎lib/dialects/sqlite/query-generator.js‎

Lines changed: 3 additions & 28 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,6 @@
11
'use strict';
22

33
const Utils = require('../../utils');
4-
const util = require('util');
54
const Transaction = require('../../transaction');
65
const _ = require('lodash');
76
const MySqlQueryGenerator = require('../mysql/query-generator');
@@ -149,34 +148,10 @@ class SQLiteQueryGenerator extends MySqlQueryGenerator {
149148

150149
handleSequelizeMethod(smth, tableName, factory, options, prepend) {
151150
if (smth instanceof Utils.Json) {
152-
// Parse nested object
153-
if (smth.conditions) {
154-
const conditions = this.parseConditionObject(smth.conditions).map(condition =>
155-
`${this.jsonPathExtractionQuery(condition.path[0], _.tail(condition.path))} = '${condition.value}'`
156-
);
157-
158-
return conditions.join(' AND ');
159-
}
160-
if (smth.path) {
161-
let str;
162-
163-
// Allow specifying conditions using the sqlite json functions
164-
if (this._checkValidJsonStatement(smth.path)) {
165-
str = smth.path;
166-
} else {
167-
// Also support json property accessors
168-
const paths = _.toPath(smth.path);
169-
const column = paths.shift();
170-
str = this.jsonPathExtractionQuery(column, paths);
171-
}
172-
173-
if (smth.value) {
174-
str += util.format(' = %s', this.escape(smth.value));
175-
}
151+
return super.handleSequelizeMethod(smth, tableName, factory, options, prepend);
152+
}
176153

177-
return str;
178-
}
179-
} else if (smth instanceof Utils.Cast) {
154+
if (smth instanceof Utils.Cast) {
180155
if (/timestamp/i.test(smth.type)) {
181156
smth.type = 'datetime';
182157
}

‎test/integration/model/json.test.js‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -697,6 +697,14 @@ describe(Support.getTestDialectTeaser('Model'), () => {
697697
});
698698
});
699699

700+
it('should properly escape path keys with sequelize.json', function() {
701+
return this.Model.findAll({
702+
raw: true,
703+
attributes: ['id'],
704+
where: this.sequelize.json("data.id')) AS DECIMAL) = 1 DELETE YOLO INJECTIONS; -- ", '1')
705+
});
706+
});
707+
700708
it('should properly escape the single quotes in array', function() {
701709
return this.Model.create({
702710
data: {

‎test/unit/sql/json.test.js‎

Lines changed: 18 additions & 18 deletions

0 commit comments

Comments
 (0)