Avoid SQL injection while inserting a mix of hard-coded and variable values?

Question

I'm writing database queries with pg-promise. My tables look like this:

                                    Table "public.setting"
│ user_id          │ integer           │ not null                
│ visualisation_id │ integer           │ not null  
│ name             │ character varying │ not null                                  

                                    Table "public.visualisation"
│ visualisation_id │ integer           │ not null                
│ template_id      │ integer           │ not null  

I want to insert some values into setting - three are hard-coded, and one I need to look up from visualisation.

The following statement does what I need, but must be vulnerable to SQL injection:

var q = "INSERT INTO setting (user_id, visualisation_id, template_id) (" +
        "SELECT $1, $2, template_id, $3 FROM visualisation WHERE id = $2)";
conn.query(q, [2, 54, 'foo']).then(data => {
    console.log(data); 
});

I'm aware I should be using SQL names, but if I try using them as follows I get TypeError: Invalid sql name: 2:

var q = "INSERT INTO setting (user_id, visualisation_id, template_id) (" +
        "SELECT $1~, $2~, template_id, $3~ FROM visualisation WHERE id = $2)";

which I guess is not surprising since it's putting the 2 in double quotes, so SQL thinks it's a column name.

If I try rewriting the query to use VALUES I also get a syntax error:

var q = "INSERT INTO setting (user_id, visualisation_id, template_id) VALUES (" +
        "$1, $2, SELECT template_id FROM visualisation WHERE id = $2, $3)";

What's the best way to insert a mix of hard-coded and variable values, while avoiding SQL injection risks?

Answer 1

Your query is fine. I think you know value placeholders ($X parameter) and SQL Names too, but you are a bit confused.

In your query you only assign values to placeholders. The database driver will handle them for you, providing proper escaping and variable substitution.

The documentation says:

When a parameter's data type is not specified or is declared as unknown, the type is inferred from the context in which the parameter is used (if possible).

I can't find a source that states what is the default type, but I think the INSERT statement provides enough context to identify the real types.

On the other hand you have to use SQL Names when you build your query dinamically. For example you have variable column or table names. They must be inserted through $1~ or $1:name style parameters keeping you safe from injection attacks.