node.js SQL Insert Into with array

Question

I'm new to Node.js and I've hit a roadblock I can't figure out. I'm trying to add an array to a SQL table using an INSERT INTO query. Following the example [here][1] I see that I add a question mark as a placeholder for my values array, and the code takes care of the rest - but that doesn't seem to be working.

Here's the code:

var temp = [1,3,2];
conn2string= "INSERT INTO " + process.env.DB_TABLE + '(`0`, `1`, `2`) VALUES ? ';

let query = connection.query(conn2string, temp, function (err, result) { // this will insert in to data base  //[vals2[0]]

And the resulting SQL connection string is:

'INSERT INTO test(`0`, `1`, `2`) VALUES 1 `

What I'm expecting Is:

'INSERT INTO test(`0`, `1`, `2`) VALUES (1, 3, 2); `

Any suggestions what I'm doing wrong?

I suggest you to use some kind of query-builder, like knexjs.org — felixmosh
– felixmosh, Commented Jan 12, 2021 at 10:52

cbr · Accepted Answer · 2021-01-12 11:03:06Z

3

To do this with the mysql package, you need to wrap the temp variable inside an array. Remember to add the parentheses around the ? of the VALUES.

var temp = [1, 3, 2];
conn2string =
  "INSERT INTO " + process.env.DB_TABLE + "(`0`, `1`, `2`) VALUES (?) ";

let query = connection.query(conn2string, [temp], function (err, result) {
  
});

This is because the parameter after the SQL string expects an array whose elements match each ? or ?? in the SQL string. By passing the temp array directly, you're essentially telling it that the first ? is 1, the second ? is 3, the third ? is 2. However, when you wrap the temp array in another array, you're essentially telling it that the value of the first ? is the temp array, and it formats it accordingly.

answered Jan 12, 2021 at 11:03

cbr

13.8k3 gold badges43 silver badges69 bronze badges

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

1 Comment

G.H. Over a year ago

Hey, great explanation - even I could understand it! That worked well too, so thanks for your help.

crystalbit · Accepted Answer · 2021-01-12 10:55:28Z

0

you can make a string value from array:

let query = connection.query(conn2string, '(' + temp.join(',') + ')', function (err, result) {

or, better, use full template:

var temp = [1,3,2];
conn2string= "INSERT INTO " + process.env.DB_TABLE + '(`0`, `1`, `2`) VALUES (?, ?, ?) ';

and to pass temp[0], temp[1], etc

or not to use a template:

conn2string= "INSERT INTO " + process.env.DB_TABLE + '(`0`, `1`, `2`) VALUES (' + temp[0] + ', ' + temp[1] + ', ' + temp[2] + ')';

answered Jan 12, 2021 at 10:55

crystalbit

7042 gold badges9 silver badges20 bronze badges

3 Comments

G.H. Over a year ago

Thanks for the reply. I was hoping to be able to make the single question mark replaced with an array method work. The actual query I have is many many columns long, so embedding the data one way or another is not an option. Plan B is to build a query string, but the method of just using a single question mark was too simple to ignore.

cbr Over a year ago

Warning: you're exposing yourself to SQL injection attacks by building the query without escaping the values or using bound parameters or a parameterized query.

crystalbit Over a year ago

of course you should care about it. I just gave options with predefined number values

AlTheLazyMonkey · Accepted Answer · 2021-01-12 10:56:25Z

0

you can try embedded the data into sql string directly using this way:

conn2string= `INSERT INTO ${process.env.DB_TABLE} (`0`, `1`, `2`) VALUES (${temp.join(',')})`;

or I think you must insert n "?" like the numbert of element in array

answered Jan 12, 2021 at 10:56

AlTheLazyMonkey

1,2421 gold badge10 silver badges23 bronze badges

1 Comment

cbr Over a year ago

Warning: you're exposing yourself to SQL injection attacks by building the query without escaping the values or using bound parameters or a parameterized query.

Collectives™ on Stack Overflow

node.js SQL Insert Into with array

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related