sql - 在插入硬编码值和变量值的混合时避免 SQL 注入(inject)？

Question

我正在使用 pg-promise 编写数据库查询.我的表格如下所示:

                                    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  

我想在 setting 中插入一些值 - 三个是硬编码的，一个我需要从 visualisation 中查找。

以下语句可以满足我的需要，但必须容易受到 SQL 注入(inject)攻击:

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); 
});

我知道我应该使用 SQL names ，但是如果我尝试按如下方式使用它们，我会得到 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)";

我想这并不奇怪，因为它将 2 放在双引号中，所以 SQL 认为它是一个列名。

如果我尝试重写查询以使用 VALUES，我也会收到语法错误:

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

插入硬编码值和变量值的最佳方式是什么，同时避免 SQL 注入(inject)风险？

Answer 1

您的查询很好。我想您也知道值占位符 ($X 参数) 和 SQL 名称，但您有点困惑。

在您的查询中，您只为占位符赋值。数据库驱动程序将为您处理它们，提供适当的转义和变量替换。

documentation说:

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).

我找不到说明什么是默认类型的来源，但我认为 INSERT 语句提供了足够的上下文来识别真实类型。

另一方面，您必须使用 SQL Names当您动态地构建查询时。例如，您有可变的列名或表名。它们必须通过 $1~ 或 $1:name 样式参数插入，以保护您免受注入(inject)攻击。