javascript - MySQL 从 JSON 对象动态构建查询

Question

有没有一种方法可以从值可能为空的 JSON 对象动态构建 MySQL 查询。

例如，从这样一个对象:

{
  "a": 1
  "b": ""
  "c": "foo"
}

创建一个这样的查询(“b”为空，不应考虑):

SELECT * FROM db.table
WHERE a = 1
AND c = "foo"

或

SELECT * FROM db.table
WHERE a = 1
AND b = ????
AND c = "foo"

编辑:它可能确实是重复的。但我认为有更多的 SQL 方法可以做到这一点，例如使用变量和 IF 语句。

编辑 2:我找到了一种方法(在 node.js API 中工作，但在其他语言中应该类似):

const jsonObj = {
"a": 1,
"b": "",
"c": "foo"
}

const query = `
SELECT * FROM db.table
WHERE
IF('${jsonObj.a}' != '', a = '${jsonObj.a}', 1=1)
AND
IF('${jsonObj.b}' != '', b = '${jsonObj.b}', 1=1)
AND
IF('${jsonObj.c}' != '', c = '${jsonObj.c}', 1=1)
`

当然，这段代码目前无法使用，必须考虑注入(inject)问题，对其进行调整。

Answer 1

重要:此攻略对SQL Injection Attacks开放.您必须转义这些值 - 最好使用准备好的查询。如果不进一步了解您的数据库客户端，就不可能指导您如何操作。

另外:我强烈建议有一个允许列的白名单，并且只允许在您的查询中使用白名单中的列键。下面的例子包括一个白名单来证明这一点。

这是一个 MVP，它将处理任意/动态对象，并根据您的请求构建 SQL 语句:

const obj = {
  "a": 1,
  "b": "",
  "c": "foo",
  "bad": "disallowed"
}

// example of how to use a whitelist
const whitelist = ['a', 'c'];

// set up an empty array to contain the WHERE conditions
let where = [];

// Iterate over each key / value in the object
Object.keys(obj).forEach(function (key) {
    // if the key is not whitelisted, do not use
    if ( ! whitelist.includes(key) ) {
        return;
    }

    // if the value is an empty string, do not use
	  if ( '' === obj[key] ) {
        return;
    }

    // if we've made it this far, add the clause to the array of conditions
    where.push(`\`${key}\` = "${obj[key]}"`);
});

// convert the where array into a string of AND clauses
where = where.join(' AND ');

// if there IS a WHERE string, prepend with WHERE keyword
if (where) {
    where = `WHERE ${where}`;
}

const sql = `SELECT * FROM table ${where}`;

console.log(sql);
// SELECT * FROM table WHERE `a` = "1" AND `c` = "foo"

注意事项:

1. 为您提供任何形式的转义字符超出了这个问题/答案的范围。如果值包含双引号字符(例如，")
，这肯定会失败
2. 为您提供一种方法来“检测”该值是否为数字并且在查询中不将其用引号引起来也超出了此问题/答案的范围。请注意，根据我的经验，许多数据库都能正确处理用引号引起来的数值。