Why does my sql multi-row insertion query contain a syntax error?

Question

I am processing a post request with nodejs on heroku and trying to insert the json into a postgres db.

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]);

I get a syntax error

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',

You sure "?" is right here? I don't know the pg module, but it's certainly not valid in postgres. This question [here](https://stackoverflow.com/questions/38697600/how-to-convert-mysql-style-question-mark-bound-parameters-to-postgres-style) seems to suggest the same, and a quick look at node's pg documentation showed it using the regular `$1` syntax. — 404, Oct 15 '19 at 14:42

score 2 · Accepted Answer · edited Oct 18 '19 at 17:04

If you are using pg module then the query can be constructed as below:

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

let result = await client.query(query);

node-pg official documentation says:

Parameterized query

If you are passing parameters to your queries you will want to avoid string concatenating parameters into the query text directly. This can (and often does) lead to SQL injection vulnerabilities. node-postgres supports parameterized queries, passing your query text unaltered as well as your parameters to the PostgreSQL server where the parameters are safely substituted into the query with battle-tested parameter substitution code within the server itself.

For Multi-row I would say to use 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

Also check this https://stackoverflow.com/questions/37300997/multi-row-insert-with-pg-promise — Sandeep Patel, Oct 15 '19 at 19:42

Why does my sql multi-row insertion query contain a syntax error?

1 Answers1