Question 89119 in PostgreSQL Question posted in
The official documentation can be found here.

Avoid SQL injection in Postgres sql dynamic query using Dapper – Postgresql

I have a function in postgres SQl which uses dynamic query to search results. I am using parameterized approach for the task to avoid SQL injection. below is the snippet from my function.

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$;

The Problem here is this code

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

During testing I found out that it is vulnerable to SQL injection. If I just pass value like "_Chapter" = "01' or 8519=8519--" it breaks my code. I thought dapper parameterized approach will solve the problem, but dapper does not handle this case. Is it because of dynamic query?

Any help is appreciated.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Turns out Dapper was escaping single quote to handle SQL injection, the problem was dynamic query. When malicious parameter was supplied like this '01'' or 8519=8519--' this statement "_FromSql" := "_FromSql" || ' AND fb."Code" ILIKE '''|| "_Chapter" ||'%'''; was getting converted to AND fb."Code" ILIKE '01' or 8519=8519-- %;.

    To handle this, there is another way of writing dynamic query. Instead of using concatenation operator || we can use substitution using $ operator.

    For e.g. above statement will be converted to "_FromSql" := "_FromSql" || ' AND hs."Code" LIKE $2 ||''%'' '; and you can pass actual parameters while executing

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

    This will make sure to avoid unnecessary string termination


  2. 0

    Why not put the dynamic sql into Dapper then using parameters should protect you from malicious code by throwing an exception (untested code follows):

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

    called in your application like this:

    var results = FilterFooBar(codes, chapter);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search