使用Dapper在Postgres SQL动态查询中避免SQL注入

Question

我有一个功能在postgres SQl中使用动态查询来搜索结果。我使用参数化方法来完成任务，以避免SQL注入。下面是我的函数的代码片段。

CREATE OR REPLACE FUNCTION master."FilterFooBar"(
"_Codes" character varying,
"_Chapter" character varying)
RETURNS TABLE("Foo" integer, "Bar" integer) 
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000
AS $BODY$
DECLARE
"_FromSql" TEXT;
BEGIN
"_FromSql" := ' FROM 
master."FooBar" fb 
WHERE 
1 = 1';

IF "_Codes" IS NOT NULL
THEN
"_FromSql" := "_FromSql" || ' AND fb."Code" IN ('|| "_Codes" ||')';
END IF;

IF "_Chapter" IS NOT NULL
THEN
"_FromSql" := "_FromSql" || ' AND fb."Code" ILIKE '''|| "_Chapter" ||'%''';
END IF;

RETURN QUERY 
EXECUTE
' SELECT fb."Foo",'|| ' fb."Bar",' || "_FromSql";
END
$BODY$;

问题是这个代码

IF "_Chapter" IS NOT NULL
THEN
"_FromSql" := "_FromSql" || ' AND fb."Code" ILIKE '''|| "_Chapter" ||'%''';
END IF;

在测试期间，我发现它很容易受到SQL注入。如果我只是传递像"_Chapter" = "01' or 8519=8519--"这样的值，它会破坏我的代码。我认为dapper参数化的方法可以解决这个问题，但是dapper并没有处理这种情况。是因为动态查询吗?

任何帮助都是感激的。

Answer 1

原来Dapper是在转义单引号来处理SQL注入，问题是动态查询。当提供这样的恶意参数'01'' or 8519=8519--'时，语句"_FromSql" := "_FromSql" || ' AND fb."Code" ILIKE '''|| "_Chapter" ||'%''';将被转换为AND fb."Code" ILIKE '01' or 8519=8519-- %;。

要处理这个问题，还有另一种编写动态查询的方法。除了使用连接操作符||，我们可以使用$ operator进行替换。 例如，上面的语句将转换为"_FromSql" := "_FromSql" || ' AND hs."Code" LIKE $2 ||''%'' ';，您可以在执行 时传递实际参数。

RETURN QUERY 
EXECUTE
' SELECT fb."Foo",'|| ' fb."Bar",' || "_FromSql" 
USING
"_Codes", --$1
"_Chapter"; --$2;

这将确保避免不必要的字符串终止

Answer 2

为什么不把动态sql放到Dapper中，然后使用参数可以通过抛出异常(下面是未经测试的代码)来保护您免受恶意代码的攻击:

public IEnumerable<dynamic> FilterFooBar(string codes, string chapter)
{
using (var connection = new NpgsqlConnection(connectionString))
{
var parameters = new DynamicParameters();
parameters.Add("_Codes", codes);
parameters.Add("_Chapter", chapter);
var sql = @"SELECT fb.""Foo"", fb.""Bar""
FROM master.""FooBar"" fb
WHERE 1 = 1";
if (!string.IsNullOrEmpty(codes))
{
sql += " AND fb.""Code"" IN (@_Codes)";
}
if (!string.IsNullOrEmpty(chapter))
{
sql += " AND fb.""Code"" ILIKE @_Chapter || '%'";
}
return connection.Query(sql, parameters);
}
}

在你的应用程序中像这样调用

var results = FilterFooBar(codes, chapter);