Skip to content

Commit a72a3f5

Browse files
sushantdhimansushantdhiman
authored
fix(mariadb): properly escape json path key (#11089)
1 parent 3dca9fa commit a72a3f5

File tree

5 files changed

+34
-21
lines changed

5 files changed

+34
-21
lines changed

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

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1053,12 +1053,12 @@ class QueryGenerator {
10531053
* https://bugs.mysql.com/bug.php?id=81896
10541054
*/
10551055
paths = paths.map(subPath => Utils.addTicks(subPath, '"'));
1056-
pathStr = ['$'].concat(paths).join('.');
1057-
return `(${quotedColumn}->>'${pathStr}')`;
1056+
pathStr = this.escape(['$'].concat(paths).join('.'));
1057+
return `(${quotedColumn}->>${pathStr})`;
10581058

10591059
case 'mariadb':
1060-
pathStr = ['$'].concat(paths).join('.');
1061-
return `json_unquote(json_extract(${quotedColumn},'${pathStr}'))`;
1060+
pathStr = this.escape(['$'].concat(paths).join('.'));
1061+
return `json_unquote(json_extract(${quotedColumn},${pathStr}))`;
10621062

10631063
case 'sqlite':
10641064
pathStr = this.escape(['$']

‎package.json‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -72,16 +72,16 @@
7272
"lint-staged": "^8.1.5",
7373
"mariadb": "^2.0.3",
7474
"markdownlint-cli": "^0.14.1",
75-
"mocha": "^6.0.2",
75+
"mocha": "6.0.2",
7676
"mysql2": "^1.6.5",
7777
"nyc": "^13.3.0",
7878
"pg": "^7.8.1",
7979
"pg-hstore": "^2.x",
8080
"pg-types": "^2.0.0",
8181
"rimraf": "^2.6.3",
8282
"semantic-release": "^15.13.3",
83-
"sinon": "^7.2.6",
84-
"sinon-chai": "^3.2.0",
83+
"sinon": "7.2.6",
84+
"sinon-chai": "3.2.0",
8585
"sqlite3": "^4.0.6",
8686
"tedious": "^6.0.0",
8787
"typescript": "^3.3.3333"

‎test/integration/include/findAndCountAll.test.js‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -73,6 +73,7 @@ describe(Support.getTestDialectTeaser('Include'), () => {
7373
});
7474
});
7575
});
76+
7677
it('should be able to include a required model. Result rows should match count', function() {
7778
const User = this.sequelize.define('User', { name: DataTypes.STRING(40) }, { paranoid: true }),
7879
SomeConnection = this.sequelize.define('SomeConnection', {

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

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -685,6 +685,18 @@ describe(Support.getTestDialectTeaser('Model'), () => {
685685
});
686686
});
687687

688+
it('should properly escape path keys', function() {
689+
return this.Model.findAll({
690+
raw: true,
691+
attributes: ['id'],
692+
where: {
693+
data: {
694+
"a')) AS DECIMAL) = 1 DELETE YOLO INJECTIONS; -- ": 1
695+
}
696+
}
697+
});
698+
});
699+
688700
it('should properly escape the single quotes in array', function() {
689701
return this.Model.create({
690702
data: {

‎test/unit/sql/where.test.js‎

Lines changed: 14 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -851,7 +851,7 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
851851
prefix: 'User'
852852
}, {
853853
mariadb: "json_unquote(json_extract(`User`.`data`,'$.nested.attribute')) = 'value'",
854-
mysql: "(`User`.`data`->>'$.\"nested\".\"attribute\"') = 'value'",
854+
mysql: "(`User`.`data`->>'$.\\\"nested\\\".\\\"attribute\\\"') = 'value'",
855855
postgres: "(\"User\".\"data\"#>>'{nested,attribute}') = 'value'",
856856
sqlite: "json_extract(`User`.`data`, '$.nested.attribute') = 'value'"
857857
});
@@ -866,7 +866,7 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
866866
}
867867
}, {
868868
mariadb: "CAST(json_unquote(json_extract(`data`,'$.nested')) AS DECIMAL) IN (1, 2)",
869-
mysql: "CAST((`data`->>'$.\"nested\"') AS DECIMAL) IN (1, 2)",
869+
mysql: "CAST((`data`->>'$.\\\"nested\\\"') AS DECIMAL) IN (1, 2)",
870870
postgres: "CAST((\"data\"#>>'{nested}') AS DOUBLE PRECISION) IN (1, 2)",
871871
sqlite: "CAST(json_extract(`data`, '$.nested') AS DOUBLE PRECISION) IN (1, 2)"
872872
});
@@ -881,7 +881,7 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
881881
}
882882
}, {
883883
mariadb: "CAST(json_unquote(json_extract(`data`,'$.nested')) AS DECIMAL) BETWEEN 1 AND 2",
884-
mysql: "CAST((`data`->>'$.\"nested\"') AS DECIMAL) BETWEEN 1 AND 2",
884+
mysql: "CAST((`data`->>'$.\\\"nested\\\"') AS DECIMAL) BETWEEN 1 AND 2",
885885
postgres: "CAST((\"data\"#>>'{nested}') AS DOUBLE PRECISION) BETWEEN 1 AND 2",
886886
sqlite: "CAST(json_extract(`data`, '$.nested') AS DOUBLE PRECISION) BETWEEN 1 AND 2"
887887
});
@@ -900,7 +900,7 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
900900
prefix: current.literal(sql.quoteTable.call(current.dialect.QueryGenerator, { tableName: 'User' }))
901901
}, {
902902
mariadb: "(json_unquote(json_extract(`User`.`data`,'$.nested.attribute')) = 'value' AND json_unquote(json_extract(`User`.`data`,'$.nested.prop')) != 'None')",
903-
mysql: "((`User`.`data`->>'$.\"nested\".\"attribute\"') = 'value' AND (`User`.`data`->>'$.\"nested\".\"prop\"') != 'None')",
903+
mysql: "((`User`.`data`->>'$.\\\"nested\\\".\\\"attribute\\\"') = 'value' AND (`User`.`data`->>'$.\\\"nested\\\".\\\"prop\\\"') != 'None')",
904904
postgres: "((\"User\".\"data\"#>>'{nested,attribute}') = 'value' AND (\"User\".\"data\"#>>'{nested,prop}') != 'None')",
905905
sqlite: "(json_extract(`User`.`data`, '$.nested.attribute') = 'value' AND json_extract(`User`.`data`, '$.nested.prop') != 'None')"
906906
});
@@ -919,7 +919,7 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
919919
prefix: 'User'
920920
}, {
921921
mariadb: "(json_unquote(json_extract(`User`.`data`,'$.name.last')) = 'Simpson' AND json_unquote(json_extract(`User`.`data`,'$.employment')) != 'None')",
922-
mysql: "((`User`.`data`->>'$.\"name\".\"last\"') = 'Simpson' AND (`User`.`data`->>'$.\"employment\"') != 'None')",
922+
mysql: "((`User`.`data`->>'$.\\\"name\\\".\\\"last\\\"') = 'Simpson' AND (`User`.`data`->>'$.\\\"employment\\\"') != 'None')",
923923
postgres: "((\"User\".\"data\"#>>'{name,last}') = 'Simpson' AND (\"User\".\"data\"#>>'{employment}') != 'None')",
924924
sqlite: "(json_extract(`User`.`data`, '$.name.last') = 'Simpson' AND json_extract(`User`.`data`, '$.employment') != 'None')"
925925
});
@@ -933,7 +933,7 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
933933
}
934934
}, {
935935
mariadb: "(CAST(json_unquote(json_extract(`data`,'$.price')) AS DECIMAL) = 5 AND json_unquote(json_extract(`data`,'$.name')) = 'Product')",
936-
mysql: "(CAST((`data`->>'$.\"price\"') AS DECIMAL) = 5 AND (`data`->>'$.\"name\"') = 'Product')",
936+
mysql: "(CAST((`data`->>'$.\\\"price\\\"') AS DECIMAL) = 5 AND (`data`->>'$.\\\"name\\\"') = 'Product')",
937937
postgres: "(CAST((\"data\"#>>'{price}') AS DOUBLE PRECISION) = 5 AND (\"data\"#>>'{name}') = 'Product')",
938938
sqlite: "(CAST(json_extract(`data`, '$.price') AS DOUBLE PRECISION) = 5 AND json_extract(`data`, '$.name') = 'Product')"
939939
});
@@ -948,7 +948,7 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
948948
}
949949
}, {
950950
mariadb: "json_unquote(json_extract(`data`,'$.nested.attribute')) = 'value'",
951-
mysql: "(`data`->>'$.\"nested\".\"attribute\"') = 'value'",
951+
mysql: "(`data`->>'$.\\\"nested\\\".\\\"attribute\\\"') = 'value'",
952952
postgres: "(\"data\"#>>'{nested,attribute}') = 'value'",
953953
sqlite: "json_extract(`data`, '$.nested.attribute') = 'value'"
954954
});
@@ -963,7 +963,7 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
963963
}
964964
}, {
965965
mariadb: "CAST(json_unquote(json_extract(`data`,'$.nested.attribute')) AS DECIMAL) = 4",
966-
mysql: "CAST((`data`->>'$.\"nested\".\"attribute\"') AS DECIMAL) = 4",
966+
mysql: "CAST((`data`->>'$.\\\"nested\\\".\\\"attribute\\\"') AS DECIMAL) = 4",
967967
postgres: "CAST((\"data\"#>>'{nested,attribute}') AS DOUBLE PRECISION) = 4",
968968
sqlite: "CAST(json_extract(`data`, '$.nested.attribute') AS DOUBLE PRECISION) = 4"
969969
});
@@ -980,7 +980,7 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
980980
}
981981
}, {
982982
mariadb: "CAST(json_unquote(json_extract(`data`,'$.nested.attribute')) AS DECIMAL) IN (3, 7)",
983-
mysql: "CAST((`data`->>'$.\"nested\".\"attribute\"') AS DECIMAL) IN (3, 7)",
983+
mysql: "CAST((`data`->>'$.\\\"nested\\\".\\\"attribute\\\"') AS DECIMAL) IN (3, 7)",
984984
postgres: "CAST((\"data\"#>>'{nested,attribute}') AS DOUBLE PRECISION) IN (3, 7)",
985985
sqlite: "CAST(json_extract(`data`, '$.nested.attribute') AS DOUBLE PRECISION) IN (3, 7)"
986986
});
@@ -997,7 +997,7 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
997997
}
998998
}, {
999999
mariadb: "CAST(json_unquote(json_extract(`data`,'$.nested.attribute')) AS DECIMAL) > 2",
1000-
mysql: "CAST((`data`->>'$.\"nested\".\"attribute\"') AS DECIMAL) > 2",
1000+
mysql: "CAST((`data`->>'$.\\\"nested\\\".\\\"attribute\\\"') AS DECIMAL) > 2",
10011001
postgres: "CAST((\"data\"#>>'{nested,attribute}') AS DOUBLE PRECISION) > 2",
10021002
sqlite: "CAST(json_extract(`data`, '$.nested.attribute') AS DOUBLE PRECISION) > 2"
10031003
});
@@ -1014,7 +1014,7 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
10141014
}
10151015
}, {
10161016
mariadb: "CAST(json_unquote(json_extract(`data`,'$.nested.attribute')) AS DECIMAL) > 2",
1017-
mysql: "CAST((`data`->>'$.\"nested\".\"attribute\"') AS DECIMAL) > 2",
1017+
mysql: "CAST((`data`->>'$.\\\"nested\\\".\\\"attribute\\\"') AS DECIMAL) > 2",
10181018
postgres: "CAST((\"data\"#>>'{nested,attribute}') AS INTEGER) > 2",
10191019
sqlite: "CAST(json_extract(`data`, '$.nested.attribute') AS INTEGER) > 2"
10201020
});
@@ -1032,7 +1032,7 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
10321032
}
10331033
}, {
10341034
mariadb: `CAST(json_unquote(json_extract(\`data\`,'$.nested.attribute')) AS DATETIME) > ${sql.escape(dt)}`,
1035-
mysql: `CAST((\`data\`->>'$."nested"."attribute"') AS DATETIME) > ${sql.escape(dt)}`,
1035+
mysql: `CAST((\`data\`->>'$.\\"nested\\".\\"attribute\\"') AS DATETIME) > ${sql.escape(dt)}`,
10361036
postgres: `CAST(("data"#>>'{nested,attribute}') AS TIMESTAMPTZ) > ${sql.escape(dt)}`,
10371037
sqlite: `json_extract(\`data\`, '$.nested.attribute') > ${sql.escape(dt.toISOString())}`
10381038
});
@@ -1047,7 +1047,7 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
10471047
}
10481048
}, {
10491049
mariadb: "json_unquote(json_extract(`data`,'$.nested.attribute')) = 'true'",
1050-
mysql: "(`data`->>'$.\"nested\".\"attribute\"') = 'true'",
1050+
mysql: "(`data`->>'$.\\\"nested\\\".\\\"attribute\\\"') = 'true'",
10511051
postgres: "CAST((\"data\"#>>'{nested,attribute}') AS BOOLEAN) = true",
10521052
sqlite: "CAST(json_extract(`data`, '$.nested.attribute') AS BOOLEAN) = 1"
10531053
});
@@ -1064,7 +1064,7 @@ describe(Support.getTestDialectTeaser('SQL'), () => {
10641064
}
10651065
}, {
10661066
mariadb: "json_unquote(json_extract(`meta_data`,'$.nested.attribute')) = 'value'",
1067-
mysql: "(`meta_data`->>'$.\"nested\".\"attribute\"') = 'value'",
1067+
mysql: "(`meta_data`->>'$.\\\"nested\\\".\\\"attribute\\\"') = 'value'",
10681068
postgres: "(\"meta_data\"#>>'{nested,attribute}') = 'value'",
10691069
sqlite: "json_extract(`meta_data`, '$.nested.attribute') = 'value'"
10701070
});

0 commit comments

Comments
 (0)