SQL多态函数的注入安全调用 [英] SQL Injection-safe call of polymorphic function

问题描述

多次我发现自己重构了Web应用程序代码，最终想要做这样的事情（在这种情况下为Groovy，但可以是任何东西）：

Several times I've found myself refactoring web application code and end up wanting to do something like this (Groovy in this case, but could be anything):

Map getData(String relationName, Integer rowId) {
    def sql = Sql.newInstance([...])
    def result = sql.firstRow('SELECT getRelationRow(?,?)', relationName, rowId)
    sql.close()
    return new HashMap(result)
}

其中存储过程 getRelationRow（relname text，rowid integer）执行动态sql以检索指定 rowid 。我看到的这样一个函数的最好的例子是这个多态函数使用 anyelement type，并调用为

where the stored procedure getRelationRow(relname text, rowid integer) executes dynamic sql to retrieve the row of the specified rowid in the requested relation. The best example I've seen of such a function is this polymorphic function using anyelement type, and is called as

SELECT * FROM data_of(NULL::pcdmet, 17);

但是，在上述代码中调用此方法需要

However to call this in the above code would require

def result = sql.firstRow("SELECT * FROM data_of(NULL::${relationName},?)",  rowId)

也就是说，它需要将关系名称粘贴到查询中，这会对SQL注入造成风险。因此，有没有保持存储过程的多态性，但允许使用通用关系名调用它？

that is, it required the relation name to be pasted into the query, which risks SQL Injection. So is there away to keep the polymorphic goodness of the stored procedure but allow it to be called with generic relation names?

推荐答案

不认为可以这样做。我假设Groovy在这里使用准备的语句，这要求在准备时输入和返回类型是已知的，而我的函数从多态输入类型导出返回类型。

I don't think it can be done this way. I assume Groovy is using prepared statements here, which requires that input and return types are known at prepare time, while my function derives the return type from the polymorphic input type.

我很确定你需要字符串连接。但不要担心，有像 pg_escape（）的函数清理表名，使SQLi不可能。

I am pretty sure you need string concatenation. But don't fret, there are functions like pg_escape() to sanitize table names and make SQLi impossible. Don't know Groovy, but it should have that, too.

基于在此相关答案的结尾处的函数 data_of（..）：

Based on the function data_of(..) at the end of this related answer:

• 重构PL / pgSQL函数返回各种SELECT查询的输出

• Refactor a PL/pgSQL function to return the output of various SELECT queries

使用 PREPARE 我可以通过声明返回类型显式：

With PREPARE I can make this work by declaring the return type explicitly:

PREPARE fooplan ("relationName") AS  -- table name works as row type
SELECT * FROM data_of($1, $2);

然后我可以输入 NULL 从准备的上下文转换为relationName

Then I can hand in NULL, which is cast to "relationName" from the prepared context:

EXECUTE fooplan(NULL, 1);

因此，如果您的界面支持这一点，这可能会奏效。但是，您仍然必须将表名连接为返回数据类型（因此防御SQLi）。抓住22我猜。

So this might work after all, if your interface supports this. But you still have to concatenate the table name as return data type (and therefore defend against SQLi). Catch 22 I guess.

这篇关于SQL多态函数的注入安全调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！