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

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

mysqlmaven-3


Solution 1:[1]

I don't think there is currently a better solution than your workaround.

  • Maven Central / Sonartype does not provide a way to update the POM files for a previously uploaded artifact. So someone who publishes an artifact cannot update it to say that it is deprecated or end-of-life or ... anything else. (There are really good reasons why artifacts are immutable.)

  • Neither Maven Central / Sonartype or any 3rd party maintains a register of deprecated or end-of-life artifacts.

So ... is such a thing possible?

Technically yes. All artifacts uploaded to Maven Central are required to have a PGP signature with a published PGP public key. So a third party site could check that an EOL notice is signed with the same key pair as was used to sign the published artifact. Then they could create a database with a web API for querying the status of an artifact, and a Maven plugin to do the work. (There could be some interesting scaling problems ... but nothing that is beyond solving.)

In practice, one would need to convince enough developers (consumers of artifacts) that automated checks for EOL artifacts was a good idea, and enough vendors (suppliers of artifacts) that publishing EOL notices was a good idea. And someone would need to stump up the money to pay for the infrastructure for the register and the people to build and run it.

Given recent events (e.g. the log4shell and related vulnerabilities), it might actually happen.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Stephen C

Related Questions