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

File tree

7 files changed

+85
-185
lines changed

7 files changed

+85
-185
lines changed

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

Lines changed: 17 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1068,24 +1068,30 @@ class QueryGenerator {
10681068

10691069
switch (this.dialect) {
10701070
case 'mysql':
1071+
case 'mariadb':
1072+
case 'sqlite':
10711073
/**
1072-
* Sub paths need to be quoted as ECMAScript identifiers
1074+
* Non digit sub paths need to be quoted as ECMAScript identifiers
10731075
* https://bugs.mysql.com/bug.php?id=81896
10741076
*/
1075-
paths = paths.map(subPath => Utils.addTicks(subPath, '"'));
1076-
pathStr = this.escape(['$'].concat(paths).join('.'));
1077-
return `(${quotedColumn}->>${pathStr})`;
1078-
1079-
case 'mariadb':
1080-
pathStr = this.escape(['$'].concat(paths).join('.'));
1081-
return `json_unquote(json_extract(${quotedColumn},${pathStr}))`;
1077+
if (this.dialect === 'mysql') {
1078+
paths = paths.map(subPath => {
1079+
return /\D/.test(subPath)
1080+
? Utils.addTicks(subPath, '"')
1081+
: subPath;
1082+
});
1083+
}
10821084

1083-
case 'sqlite':
10841085
pathStr = this.escape(['$']
10851086
.concat(paths)
10861087
.join('.')
1087-
.replace(/\.(\d+)(?:(?=\.)|$)/g, (_, digit) => `[${digit}]`));
1088-
return `json_extract(${quotedColumn}, ${pathStr})`;
1088+
.replace(/\.(\d+)(?:(?=\.)|$)/g, (__, digit) => `[${digit}]`));
1089+
1090+
if (this.dialect === 'sqlite') {
1091+
return `json_extract(${quotedColumn},${pathStr})`;
1092+
}
1093+
1094+
return `json_unquote(json_extract(${quotedColumn},${pathStr}))`;
10891095

10901096
case 'postgres':
10911097
pathStr = this.escape(`{${paths.join(',')}}`);

‎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
Original file line numberDiff line numberDiff line change
@@ -82,63 +82,63 @@ if (current.dialect.supports.JSON) {
8282
it('condition object', () => {
8383
expectsql(sql.whereItemQuery(undefined, Sequelize.json({ id: 1 })), {
8484
postgres: '("id"#>>\'{}\') = \'1\'',
85-
sqlite: "json_extract(`id`, '$') = '1'",
86-
mariadb: "json_unquote(json_extract(`id`,'$.')) = '1'",
87-
mysql: "`id`->>'$.' = '1'"
85+
sqlite: "json_extract(`id`,'$') = '1'",
86+
mariadb: "json_unquote(json_extract(`id`,'$')) = '1'",
87+
mysql: "json_unquote(json_extract(`id`,'$')) = '1'"
8888
});
8989
});
9090

9191
it('nested condition object', () => {
9292
expectsql(sql.whereItemQuery(undefined, Sequelize.json({ profile: { id: 1 } })), {
9393
postgres: '("profile"#>>\'{id}\') = \'1\'',
94-
sqlite: "json_extract(`profile`, '$.id') = '1'",
94+
sqlite: "json_extract(`profile`,'$.id') = '1'",
9595
mariadb: "json_unquote(json_extract(`profile`,'$.id')) = '1'",
96-
mysql: "`profile`->>'$.id' = '1'"
96+
mysql: "json_unquote(json_extract(`profile`,'$.\\\"id\\\"')) = '1'"
9797
});
9898
});
9999

100100
it('multiple condition object', () => {
101101
expectsql(sql.whereItemQuery(undefined, Sequelize.json({ property: { value: 1 }, another: { value: 'string' } })), {
102102
postgres: '("property"#>>\'{value}\') = \'1\' AND ("another"#>>\'{value}\') = \'string\'',
103-
sqlite: "json_extract(`property`, '$.value') = '1' AND json_extract(`another`, '$.value') = 'string'",
104-
mariadb: "json_unquote(json_extract(`property`,'$.value')) = '1' and json_unquote(json_extract(`another`,'$.value')) = 'string'",
105-
mysql: "`property`->>'$.value' = '1' and `another`->>'$.value' = 'string'"
103+
sqlite: "json_extract(`property`,'$.value') = '1' AND json_extract(`another`,'$.value') = 'string'",
104+
mariadb: "json_unquote(json_extract(`property`,'$.value')) = '1' AND json_unquote(json_extract(`another`,'$.value')) = 'string'",
105+
mysql: "json_unquote(json_extract(`property`,'$.\\\"value\\\"')) = '1' AND json_unquote(json_extract(`another`,'$.\\\"value\\\"')) = 'string'"
106106
});
107107
});
108108

109109
it('property array object', () => {
110110
expectsql(sql.whereItemQuery(undefined, Sequelize.json({ property: [[4, 6], [8]] })), {
111111
postgres: '("property"#>>\'{0,0}\') = \'4\' AND ("property"#>>\'{0,1}\') = \'6\' AND ("property"#>>\'{1,0}\') = \'8\'',
112-
sqlite: "json_extract(`property`, '$[0][0]') = '4' AND json_extract(`property`, '$[0][1]') = '6' AND json_extract(`property`, '$[1][0]') = '8'",
113-
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'",
114-
mysql: "`property`->>'$.0.0' = '4' and `property`->>'$.0.1' = '6' and `property`->>'$.1.0' = '8'"
112+
sqlite: "json_extract(`property`,'$[0][0]') = '4' AND json_extract(`property`,'$[0][1]') = '6' AND json_extract(`property`,'$[1][0]') = '8'",
113+
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'",
114+
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'"
115115
});
116116
});
117117

118118
it('dot notation', () => {
119119
expectsql(sql.whereItemQuery(Sequelize.json('profile.id'), '1'), {
120120
postgres: '("profile"#>>\'{id}\') = \'1\'',
121-
sqlite: "json_extract(`profile`, '$.id') = '1'",
121+
sqlite: "json_extract(`profile`,'$.id') = '1'",
122122
mariadb: "json_unquote(json_extract(`profile`,'$.id')) = '1'",
123-
mysql: "`profile`->>'$.id' = '1'"
123+
mysql: "json_unquote(json_extract(`profile`,'$.\\\"id\\\"')) = '1'"
124124
});
125125
});
126126

127127
it('item dot notation array', () => {
128128
expectsql(sql.whereItemQuery(Sequelize.json('profile.id.0.1'), '1'), {
129129
postgres: '("profile"#>>\'{id,0,1}\') = \'1\'',
130-
sqlite: "json_extract(`profile`, '$.id[0][1]') = '1'",
130+
sqlite: "json_extract(`profile`,'$.id[0][1]') = '1'",
131131
mariadb: "json_unquote(json_extract(`profile`,'$.id[0][1]')) = '1'",
132-
mysql: "`profile`->>'$.id[0][1]' = '1'"
132+
mysql: "json_unquote(json_extract(`profile`,'$.\\\"id\\\"[0][1]')) = '1'"
133133
});
134134
});
135135

136136
it('column named "json"', () => {
137137
expectsql(sql.whereItemQuery(Sequelize.json('json'), '{}'), {
138138
postgres: '("json"#>>\'{}\') = \'{}\'',
139-
sqlite: "json_extract(`json`, '$') = '{}'",
140-
mariadb: "json_unquote(json_extract(`json`,'$.')) = '{}'",
141-
mysql: "`json`->>'$.' = '{}'"
139+
sqlite: "json_extract(`json`,'$') = '{}'",
140+
mariadb: "json_unquote(json_extract(`json`,'$')) = '{}'",
141+
mysql: "json_unquote(json_extract(`json`,'$')) = '{}'"
142142
});
143143
});
144144
});

0 commit comments

Comments
 (0)