I want to build a function that builds a prepared statement for a SQlite UPDATE query. My current approach is to simply build a string and send this sql to the database. But you can only do that if you don't care about security at all ;-)

My current function:

const newBrandData = {name: "def"};
const brand = {id: 1, name: "abc", prop1: ""};

update(brand, newBrandData) {
    const keyValues = [];
    Object.keys(newBrandData).forEach(e => {
        keyValues.push(`${e} = "${newBrandData[e]}"`)
    });
        
    const sql = `UPDATE ${this.table} SET ${keyValues.toString()} WHERE id = ?`;
    const params = [brand.id];

    return this.run(sql, params);
}

CodePudding user response：

Not sure if I understood your question, but you can avoid concatenating key values by using placeholders everywhere:

const keyValues = [];
const params = [];
Object.keys(newBrandData).forEach(e => {
  keyValues.push(`${e} = ?`);
  params.push(newBrandData[e]);
});
const sql = `UPDATE ${this.table} SET ${keyValues.toString()} WHERE id = ?`;
params.push(brand.id);

CodePudding user response：

You are definitely on the right track. You only have to change two things:

1. replace keyValues.push(${e} = "${newBrandData[e]}") with keyValues.push(${e} = ?) `.

2. replace const params = [brand.id]; with const params = [...Object.values(newBrandData), brand.id];

update(brand, newBrandData) {
    const keyValues = [];
    Object.keys(newBrandData).forEach(e => {
        keyValues.push(`${e} = ?`)
    });
        
    const sql = `UPDATE ${this.table} SET ${keyValues.toString()} WHERE id = ?`;
    
    const params = [...Object.values(newBrandData), brand.id];
    return this.run(sql, params)
}