MySQL Build query dynamically from JSON object

Question

Is there a way to dynamically build a MySQL query from a JSON object whose values are likely to be empty.

For example, from an object like this one:

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

create a query like this one ("b" is empty, it should not be taken into account) :

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

or

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

Edit : it's probably duplicate indeed. But I thought there was a more SQL way to do that, for exemple using variables and IF statements.

Edit 2 : I found a way (working in node.js API but it should be similar in other languages) :

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)
`

Of course this code is not usable as it stands, it has to be adapted keeping in mind injection problems.

Answer 1

IMPORTANT: This strategy is open to SQL Injection Attacks. You must escape the values - preferably using prepared queries. Without more knowledge of your database client, it's impossible to direct you how to do so.

ADDITIONALLY: I'd strongly recommend having a whitelist of allowed columns, and only permitting column keys that are in the whitelist to be used in your query. The example below includes a whitelist to demonstrate this.

Here is an MVP that will handle an arbitrary / dynamic object, and build a SQL statement per your request:

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"

NOTES:

1. Providing you ANY form of escaping characters is beyond the scope of this question / answer. This will certainly fail where values contain double-quote characters (eg, ")
2. Providing you a means to "detect" if the value is numeric and NOT wrap it in quotes in the query is also beyond the scope of this question / answer. Note that in my experience, many databases properly handle a numeric value that is enclosed in quotes.