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 seems to suggest the same, and a quick look at node's pg documentation showed it using the regular $1 syntax. — 404
– 404, Commented Oct 15, 2019 at 14:42

vitaly-t · Accepted Answer · 2019-10-18 17:04:53Z

2

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

edited Oct 18, 2019 at 17:04

vitaly-t

26.1k17 gold badges129 silver badges151 bronze badges

answered Oct 15, 2019 at 17:01

Sandeep Patel

5,1483 gold badges23 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sandeep Patel Over a year ago

Also check this stackoverflow.com/questions/37300997/…

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related