Inserting by passing an array of columns and an array of values in variables with MySQL

Question

I have this query:

q="INSERT INTO customers (?) VALUES (?)"

and I'm trying to pass the first placeholder an array of column names and the second placeholder an array of values, like so:

columns_arr = [
  'stat',
  'idcustomers',
  'parent_id',
  'cust_prio_code',
  'custadd_type_code',
  'customername',
  'EMAIL',
  'priority_id'
]
values_arr = [
  2,
  300,
  900,
  10003999,
  'someType',
  'dave',
  '[email protected]',
  99
]
var inserts = [columns_arr, values_arr]
var format_query = mysql.format(q, inserts)
mydb.query(format_query)

but the query that ends up being executed is:

INSERT INTO `customers` ('stat', 'idcustomers', 'parent_id', 'cust_prio_code', 'custadd_type_code', 'customername', 'EMAIL', 'priority_id') VALUES (2, 300, 900, 10003999, 'someType', 'dave', '[email protected]', 99)

and it gives a syntax error:

(node:18776) UnhandledPromiseRejectionWarning: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''stat', 'idcustomers', 'parent_id', 'cust_prio_code', 'custadd_type_code', 'c...' at line 1

So, column names are being passed as strings. How can I solve it? I get this array dynamically, as it is, and so I can't just manually change it to an array without quotes, and even if I could it wouldn't compile as these are not declared in the code. trying to use replace(/'/g,'') didn't help. How can I pass this array to the query without the single quotes on each column name?

and if it's not the problem then what is?

Thanks a lot

Have you ever tried removing the parenthesis in the query? INSERT INTO customers ? VALUES ? — Cyrille Con Morales
– Cyrille Con Morales, Commented May 25, 2021 at 6:37
1) you cannot used prepared statement parameters to pass table or field names, only values. Use string concatenation with white list. 2) you have to have a parameter for each value you pass. — Shadow
– Shadow, Commented May 25, 2021 at 6:41
@CyrilleConMorales I have... it formats the query without parenthesis and gives a syntax error similar to the original one — MatanyaP
– MatanyaP, Commented May 25, 2021 at 6:41
@Shadow 1) so how can I achieve the same effect? can I use string manipulation? 2) what do you mean by that? a corresponding value for each parameter? I do have it — MatanyaP
– MatanyaP, Commented May 25, 2021 at 6:45

Akina · Accepted Answer · 2021-05-25 06:48:04Z

2

Placeholders ? in prepared statement are wrapped with single quotes. So your column names are treated as string literals, not as column names. And the query fails.

You must use ?? escaping placeholders.

q="INSERT INTO customers (??) VALUES (?)"

See Escaping query identifiers.

answered May 25, 2021 at 6:48

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

Inserting by passing an array of columns and an array of values in variables with MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related