node.js - 为什么我的sql多行插入查询包含语法错误？

Question

我正在 heroku 上使用 nodejs 处理发布请求，并尝试将 json 插入到 postgres 数据库中。

const client = await pool.connect();
    var jsondata = req.body;
    var values = [];
    for(var i=0; i< jsondata.length; i++) {
                         values.push([jsondata[i].id,jsondata[i].title,jsondata[i].imageUrl,jsondata[i].orderPosition]);
                    }
const sqlQuery = 'INSERT INTO showme_gardens_tourMapTableTest (mapId, imageId, title, position) VALUES ?;';
console.log("$%$% " + values);                  
const  resultOfMapsInsert = await client.query(sqlQuery, [values]);

语法错误

2019-10-15T13:08:35.238210+00:00 app[web.1]: $%$% 25,Wind in the willows,http://something.com,4
2019-10-15T13:08:35.244196+00:00 app[web.1]: { error: syntax error at or near "?"
2019-10-15T13:08:35.244199+00:00 app[web.1]: at Connection.parseE (/app/node_modules/pg/lib/connection.js:602:11)
2019-10-15T13:08:35.244202+00:00 app[web.1]: at Connection.parseMessage (/app/node_modules/pg/lib/connection.js:399:19)
2019-10-15T13:08:35.244204+00:00 app[web.1]: at Socket.<anonymous> (/app/node_modules/pg/lib/connection.js:121:22)
2019-10-15T13:08:35.244206+00:00 app[web.1]: at Socket.emit (events.js:198:13)
2019-10-15T13:08:35.244208+00:00 app[web.1]: at addChunk (_stream_readable.js:288:12)
2019-10-15T13:08:35.244210+00:00 app[web.1]: at readableAddChunk (_stream_readable.js:269:11)
2019-10-15T13:08:35.244212+00:00 app[web.1]: at Socket.Readable.push (_stream_readable.js:224:10)
2019-10-15T13:08:35.244214+00:00 app[web.1]: at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
2019-10-15T13:08:35.244216+00:00 app[web.1]: name: 'error',
2019-10-15T13:08:35.244218+00:00 app[web.1]: length: 90,
2019-10-15T13:08:35.244220+00:00 app[web.1]: severity: 'ERROR',
2019-10-15T13:08:35.244222+00:00 app[web.1]: code: '42601',

Answer 1

如果您使用的是 pg 模块，那么可以按如下方式构建查询:

const query = {
  text: 'INSERT INTO tableName (field1, field2) VALUES($1, $2)',
  values: [value1, value2],
}

let result = await client.query(query);

node-pg官方文档说:

参数化查询

如果您将参数传递给查询，您将希望避免将字符串直接连接到查询文本中。这可能(并且经常)导致 SQL 注入(inject)漏洞。 node-postgres 支持参数化查询，将您的查询文本以及您的参数原封不动地传递给 PostgreSQL 服务器，在服务器本身内使用经过实战测试的参数替换代码将参数安全地替换到查询中。

对于多行，我会说使用 pg-promise :

const pgp = require('pg-promise')(/* initialization options */);

// Database connection details;
const cn = {
    host: 'localhost', // 'localhost' is the default;
    port: 5432, // 5432 is the default;
    database: 'myDatabase',
    user: 'myUser',
    password: 'myPassword'
};
const db = pgp(cn);

const colset = new pgp.helpers.ColumnSet(['col1', 'col2'], {table: 'tablename'});

// data input values:
const values = [{col1: value1, col2:value2}, {col1: value3, col2: value4}];

// generating a multi-row insert query:
const query = pgp.helpers.insert(values, colset);
//=> INSERT INTO "tablename"("col1","col2") VALUES(value1,value2),(value3,value4)

await db.none(query); // executing the query