search

mysqljs / sqlstring

Simple SQL escape and format for MySQL
MIT License
403 stars 78 forks source link

Formatting a SELECT list for INSERT INTO SELECT usage #42

Closed ThomasLobker closed 2 years ago

ThomasLobker commented 5 years ago

If you have complex queries and you want to INSERT from a SELECT query, then you need to be able to set a SELECT list like this:

INSERT INTO `table`
SELECT
  NULL `id`,
  'Thomas' `name`,
  34 `age`
WHERE 1

What is currently the best way to create such a SELECT list from an object? The following statement does not work in this scenario and creates a key = value list:

sqlstring.format('INSERT INTO `table` SELECT ?? WHERE 1', { id: null, name: "Thomas", age: 34 });
dougwilson commented 2 years ago

This is unfortunately not really supported out of the box, and due to recent highlights of the insecurity of magical formatting based on the input type, it is unlikely to be added to this module. I would recommend looking in to a full-feature SQL builder module like https://hiddentao.github.io/squel/v4/index.html