Node-mysql2: Multiple query with escaped data

[HulioEglesias]

How can I use multiple queries and escape data? Now I'm using queries like a
db.query('INSERT INTO users (name, lastname) VALUES (?, ?)', [username, lastname])
But that will insert one row. How I can insert many rows by one query?

[sidorares]

first, you don't need multiple statements to insert multiple rows, see https://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql answer

afaik the only special thing to do to use multiple statements is to set {multipleStatements: true} flag in connection config. ? placeholders are used same way

What have you tried and what problems do you have @HulioEglesias ?

[HulioEglesias]

I have object

user: {
  characters: [{name: 'Char1', level: 12}, {name: 'char2', level: 54}];
}

Count of characters is different. I need insert all array of objects to table 'characters'
What a string I should have? @sidorares

[sidorares]

try this: db.query('INSERT INTO users (name, lastname) VALUES ?', [['Char1', 12], ['Char2', 54]])

see https://github.com/mysqljs/mysql#escaping-query-values:

Nested arrays are turned into grouped lists (for bulk inserts), e.g. [['a', 'b'], ['c', 'd']] turns into ('a', 'b'), ('c', 'd')

Note that this is client-side escape ( e.i it'll build query like INSERT INTO users (name, lastname) VALUES ('Char1', 12), ('Char2', 54) on the client before sending it to server

If you want to use prepared statements ( aka .execute()) you must use same number of placeholders as number of parameters you pass

[ecappa]

try this: db.query('INSERT INTO users (name, lastname) VALUES ?', [['Char1', 12], ['Char2', 54]])

see https://github.com/mysqljs/mysql#escaping-query-values:

Nested arrays are turned into grouped lists (for bulk inserts), e.g. [['a', 'b'], ['c', 'd']] turns into ('a', 'b'), ('c', 'd')

Note that this is client-side escape ( e.i it'll build query like INSERT INTO users (name, lastname) VALUES ('Char1', 12), ('Char2', 54) on the client before sending it to server

If you want to use prepared statements ( aka .execute()) you must use same number of placeholders as number of parameters you pass

It works... but the array has to be included in another array... I don't know if I am clear :
[
['Char1', 12],
['Char2', 54],
]
won't work.
But :
[ <---- 'extra' array dimension ?
[
['Char1', 12],
['Char2', 54],
]
]
will work...