Избегайте внедрения SQL в динамический запрос Postgres sql с помощью Dapper
У меня есть функция в 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 не справляется с этим случаем. Это из-за динамического запроса?
Любая помощь приветствуется.
Ответы 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);
Я не могу этого сделать, потому что наше приложение использует только функции, поэтому я должен писать весь код только в функции.
Ваша функция — чистый postgres, поэтому, если вы передадите вредоносный код, вы выполните вредоносный код. Вы совсем не пользуетесь щегольством. В комментарии под вопросом я указал URL. Попробуй это.
Оказывается, Dapper избегал одинарных кавычек для обработки SQL-инъекций, проблема заключалась в динамическом запросе. Когда вредоносный параметр был предоставлен таким образом '01'' or 8519=8519--', это выражение "_FromSql" := "_FromSql" || ' AND fb."Code" ILIKE '''|| "_Chapter" ||'%'''; преобразовывалось в AND fb."Code" ILIKE '01' or 8519=8519-- %;.
Чтобы справиться с этим, есть другой способ написания динамического запроса. Вместо использования оператора конкатенации || мы можем использовать замену с помощью оператора $.
Например, приведенный выше оператор будет преобразован в "_FromSql" := "_FromSql" || ' AND hs."Code" LIKE $2 ||''%'' ';, и вы можете передавать фактические параметры во время выполнения
RETURN QUERY
EXECUTE
' SELECT fb."Foo",'|| ' fb."Bar",' || "_FromSql"
USING
"_Codes", --$1
"_Chapter"; --$2;
Это позволит избежать ненужного завершения строки
см.: stackoverflow.com/a/49014648/2067753