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.

Answer 2

Let us try to create a function that can handle much complex queries

function prepareStmtFromObject(params) {
    const constraints = [];
    const data = [];
    Object.keys(params).forEach((item) => {
        if (!params[item] || params[item] == "") {
            return;
        }
        if (Array.isArray(params[item])) {
            constraints.push(`${item} in (?)`);
            data.push(params[item]);
        } else if (typeof params[item] === "string" && params[item].indexOf(",") > -1) {
            constraints.push(`${item} in (?)`);
            data.push(params[item].split(","));
        } else if (params[item] instanceof RegExp) {
            constraints.push(`${item} REGEXP ?`);
            data.push(params[item]);
        } else if (params[item] && typeof params[item] === "object") {
            Object.keys(params[item]).forEach((value) => {
                if (value === "$gte") {
                    constraints.push(`${item} >= ?`);
                    data.push(params[item][value]);
                } else if (value === "$lte") {
                    constraints.push(`${item} <= ?`);
                    data.push(params[item][value]);
                } else if (value === "$gt") {
                    constraints.push(`${item} > ?`);
                    data.push(params[item][value]);
                } else if (value === "$lt") {
                    constraints.push(`${item} < ?`);
                    data.push(params[item][value]);
                } else if (value === "$like") {
                    if (Array.isArray(params[item][value])) {
                        const localConstraints = [];
                        params[item][value].forEach((likeValues) => {
                            localConstraints.push(`${item} LIKE ?`);
                            data.push(`%${likeValues}%`);
                        });
                        constraints.push(`(${localConstraints.join(" OR ")})`);
                    } else if (typeof params[item][value] === "string" && params[item][value].indexOf(",") > -1) {
                        const localConstraints = [];
                        params[item][value] = params[item][value].split(",");
                        params[item][value].forEach((likeValues) => {
                            localConstraints.push(`${item} LIKE ?`);
                            data.push(`%${likeValues}%`);
                        });
                        constraints.push(`(${localConstraints.join(" OR ")})`);
                    } else {
                        constraints.push(`${item} LIKE ?`);
                        data.push(`%${params[item][value]}%`);
                    }
                }
            });
        } else {
            constraints.push(`${item} = ?`);
            data.push(params[item]);
        }
    });
    return { constraints, data };
}

const data = {
    userId: 1,
    company: ["google", "microsoft"],
    username: { $like: "Test" },
    name: { $like: [ "Test1", "Test2" ] },
    age: { $gt: 10 }
}
const stmt = prepareStmtFromObject(data);
console.log("SELECT * FROM user WHERE ", stmt.constraints.join(" and "));
console.log(stmt.data);

The above function returns a constraints and a query array which can be used for escaping characters. This way you can prevent SQL injection as well. I am assuming you are using mysql package