JavaScript中PostgreSQL种子数据PATCH请求报错修复

问题场景 #

我维护一个鸟类数据库，使用node-postgres的Pool对象管理连接池，尝试通过PATCH请求修改观鸟者的邮箱地址。GET和POST请求已验证正常，但发起PATCH请求时终端报错：

error: column "jamessun877" does not exist
    at /home/username/Programs/programs/projects/birdwatchersrepo/node_modules/pg-pool/index.js:45:11
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  length: 111,
  severity: 'ERROR',
  code: '42703',
  detail: undefined,
  hint: undefined,
  position: '49',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '3638',
  routine: 'errorMissingColumn'
}

Node.js v20.7.0
[nodemon] app crashed - waiting for file changes before starting...

相关代码片段：

app.js #

const {modifyBirdWatcherEmail} = require('./controllers/bwatchers.controller.js')

// 其他端点和HTTP请求方法（GET和POST已验证可正常工作）
const express = require('express');
const app = express();
app.use(express.json())
app.patch('/api/birdwatchers/:bw_id',modifyBirdWatcherEmail)
module.exports = app

bwatchers.controller.js #

const {updateBirdWatcherEmail} = require('../models/b_watchers.models.js')
exports.modifyBirdWatcherEmail = (req,res) =>{
    let {bw_id} = req.params
    const {email_address} = req.body
    bw_id *= 1;
    updateBirdWatcherEmail(bw_id,email_address).then((updatedEmail)=>{
        return res.status(200).send({updatedEmail})
    })
}

错误的模型文件代码（b_watchers.models.js） #

exports.updateBirdWatcherEmail = (bw_id,email_address) =>{
  const queryStr = `UPDATE birdwatchers
        SET email_address = ${email_address}
        WHERE bw_id = $1
        RETURNING 
        *;
        `
  return db.query(queryStr,[bw_id]).then((result)=>result.rows[0])
}

错误原因 #

直接在SQL语句中用${email_address}拼接字符串变量，导致PostgreSQL将邮箱值（比如jamessun877@aol.com）解析为SQL语法的一部分——它会把jamessun877当成列名，自然找不到对应的列，从而抛出42703错误。

修复方案 #

使用node-postgres推荐的参数化查询，通过占位符$n替代直接变量拼接，所有变量统一放到db.query的参数数组中：

修改后的b_watchers.models.js #

exports.updateBirdWatcherEmail = (bw_id, email_address) => {
  const queryStr = `UPDATE birdwatchers
        SET email_address = $2
        WHERE bw_id = $1
        RETURNING *;`
  return db.query(queryStr, [bw_id, email_address]).then((result) => result.rows[0])
}

这样做不仅能让PostgreSQL正确识别字符串类型的邮箱值，还能避免SQL注入风险，是操作数据库的标准安全写法。

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