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?

And of course even if it were possiblestring data needs to be quoted so `name = siri` would have needed to be `name = 'siri'` — RiggsFolly, Jan 06 '21 at 19:41
Why is my question closed? This question doesn't already have answers — siri, Jan 06 '21 at 19:44
You don't necessarily have SQL injection risks just because you use string concatenation. If you concatenate values that cannot be influenced by untrusted content, then it's still safe. But if you don't understand how SQL injection works, perhaps it's best if you refrain from using dynamic SQL. — Bill Karwin, Jan 15 '21 at 02:39

Ringolds · Answer 1 · 2021-01-07T00:03:09.790

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.

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

1 Answers1