mysql - How to build a dynamic SQL "WHERE" statement using a JSON input

Question

I am trying to dynamically build my SQL statement using node. The where clause will be completely different for each of my cases.

const sql = `select columnName from tableName where ?`;
const whereClause = { "name": "Siri", "Age":20}
connection.query(sql, whereClause, (err, rows) { ... });

However, I keep getting SQL syntax error. The query node builds is select columnName from tableName where name = 'siri', age = 20. I figured the reason I get SQL syntax error is because the SQL statement is missing the AND part. I want to be able to construct the query by giving the JSON object for the where clause.

I don't want to build the query using string concatenation due to SQL injection risks. So, is there another way that I can build my SQL statement without manually adding the AND part?

question from:https://stackoverflow.com/questions/65602350/how-to-build-a-dynamic-sql-where-statement-using-a-json-input

Answer 1

I'm pretty sure you can't process column names like that. Write a helper function that processes the json object and escapes values.

function processValue(value) {
    if(!isNaN(value)) {
        return value;
    }
    if(typeof value === "string") {
        return `"${mysql.escape(value)}"`;
    }
    throw new Error("Unsupported value type!");
}

function where(obj) {
    return Object.entries(obj).reduce(function(statement, [key, value]) {
        return statement.concat(["AND", key, "=", processValue(value)]);
    }, []).slice(1).join(" ");
}

Your query now looks like this:

const sql = `select columnName from tableName where ?`;
connection.query(sql, where({ "name": "Siri", "Age":20 }), (err, rows) { ... });

On another note, just use an ORM or a query builder like Knex so that you don't have to do all this manually.