如何实现SQLite3 JS中动态字段数量的安全更新？

你的两个方案都存在明显缺陷：

• 方案1：额外增加一次读操作，既降低效率，还可能在高并发场景下出现数据覆盖问题（读取旧值后，其他请求已修改数据，再用旧值覆盖新值）。
• 方案2：多次单字段更新会产生不必要的数据库开销，完全没必要。

最优做法是动态生成仅包含需要更新字段的UPDATE语句，同时严格使用占位符?避免SQL注入，步骤如下：

1. 提取更新对象中的有效字段（过滤表中不存在或不允许更新的字段，比如id、title、userId这类通常无需用户修改的字段）
2. 构建SET子句部分，每个字段对应一个占位符
3. 收集对应的参数值，最后追加主键id
4. 执行生成的SQL语句

代码实现 #

const updateMovie = function(id, update) {
    return new Promise((resolve, reject) => {
        // 定义允许更新的字段，防止非法字段传入
        const allowedFields = ['isFavorite', 'rating', 'watchDate'];
        // 筛选update对象中存在且允许更新的字段
        const updateEntries = Object.entries(update).filter(([key]) => allowedFields.includes(key));
        
        // 处理空更新的情况
        if (updateEntries.length === 0) {
            return reject(new Error('没有需要更新的字段'));
        }
        
        // 构建SET子句：例如 ['isFavorite = ?', 'rating = ?']
        const setClauses = updateEntries.map(([key]) => `${key} = ?`);
        // 拼接完整SQL语句
        const sql = `UPDATE films SET ${setClauses.join(', ')} WHERE id = ?`;
        // 收集参数：先存更新字段的值，最后加上主键id
        const params = [...updateEntries.map(([_, value]) => value), id];
        
        db.run(sql, params, function(err) {
            if (err) {
                return reject(err);
            }
            // this.changes 获取受影响行数，比rows更能反映更新结果
            resolve({ affectedRows: this.changes });
        });
    });
}

// 使用示例
const testUpdate1 = { isFavorite: true, rating: 4 };
updateMovie(3, testUpdate1)
    .then(res => console.log(res))
    .catch(err => console.error(err));

const testUpdate2 = { watchDate: '2024-05-20' };
updateMovie(4, testUpdate2)
    .then(res => console.log(res))
    .catch(err => console.error(err));

关键说明 #

• 安全性：所有变量通过占位符?传入，完全规避SQL注入风险，无任何变量直接拼接SQL的操作
• 效率：仅执行一次UPDATE操作，只更新需要修改的字段
• 健壮性：通过allowedFields限制可更新字段，防止非法字段导致SQL错误；同时处理了空更新的边界情况
• 实用返回值：用this.changes获取受影响行数，可判断是否真有数据被更新（比如传入的id不存在时，changes为0）

内容的提问来源于stack exchange，提问作者Umberto Fontanazza