Convert JavaScript Array to SQL Multirow Values List

Question

Trying to load a JavaScript array into a SQL Server table. I convert the array to a string, however, its format does not immediately translate into something (I know of) to use as separate records to insert. So, I'm trying to convert the JavaScript array, to a string, and then that string into a multiple row, SQL values list.

JavaScript array:

const jsArray = [
    [1,'Cake','Special characters comma, left bracket ], right bracket ].'],
    [2,'Ice cream','Single \'quote\' with "double" quote.'],
    [3,"Fudge Syrup","Double \"quote\" with 'single' quote."],
]
strArray = JSON.stringify(jsArray)
console.log(strArray)

JavaScript string result:

[
    [1,"Cake","Special characters comma, left bracket ], right bracket ]."],
    [2,"Ice cream","Single 'quote' with \"double\" quote."],
    [3,"Fudge Syrup","Double \"quote\" with 'single' quote."]
]

Desired SQL value list rows:

INSERT INTO [MYTABLE] (
    [FIELD1], [FIELD1], [FIELD3]
)
VALUES
    (1,'Cake','Special characters comma, left bracket ], right bracket ].'),
    (2,'Ice cream','Single ''quote'' with "double" quote.'),
    (3,'Fudge syrup','Double "quote" with ''single'' quote.')
;

As you can see for SQL, each array needs to be surrounded by parathesis, each text field needs single quotes, etc.

I've been using a blunt force approach of a number of "replace" steps, tried for loops, some RegEx, but each seemed the wrong or an over complicated method, or couldn't quite get it right, or screwed up a valid instance of an entered special character (e.g. a double quote that needs to stay as a double quote).

Any ideas?

Mistake #1: not using proper SQL parameterization. Where is this JS running from, is it nodejs? Which DBMS is this, SQL Server, Postgres, something else? Please tag correctly, please show relevant code. — Charlieface
– Charlieface, Commented Mar 4 at 2:02

TheoNeUpKID · Accepted Answer · 2025-03-04 02:36:08Z

1

You can try breaking it up to multiple operations and then bring the values together again. This approach can likely be optimized but, the approach would be to emphasize that the operation(s) can be broken up and then pieced together again.

Hope this helps or at least get you headed in the right direction.

const jsArray = [
  [1, "Cake", "Special characters comma, left bracket ], right bracket ]."],
  [2, "Ice cream", "Single 'quote' with \"double\" quote."],
  [3, "Fudge Syrup", "Double \"quote\" with 'single' quote."],
];

const insertTo = (tableName) => `INSERT INTO [${tableName}](`;

const columnsTb = (colNum) =>
  Array.from(Array(colNum), (_, c) => {
    return `[FIELD${c + 1}]`;
  });

const rows = jsArray.map((r) => {
  return "('" + r.join("','") + "')";
});

console.log(
  insertTo("MYTABLE") + columnsTb(jsArray.length) + ")" + "VALUES" + rows
);

answered Mar 4 at 2:36

TheoNeUpKID

9918 silver badges13 bronze badges

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

1 Comment

siggemannen Mar 4 at 6:40

this is probably susceptible to sql injection

Collectives™ on Stack Overflow

Convert JavaScript Array to SQL Multirow Values List

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related