Avoid SQL injection in Postgres sql dynamic query using Dapper

Question

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.

see: stackoverflow.com/a/49014648/2067753

Paul Maxwell
– Paul Maxwell

2023-04-03 05:57:55 +00:00
Commented Apr 3, 2023 at 5:57 — Paul Maxwell
– Paul Maxwell, Commented Apr 3, 2023 at 5:57

Faraaz · Accepted Answer · 2023-04-05 09:54:23Z

1

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

answered Apr 5, 2023 at 9:54

Faraaz

458 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paul Maxwell · Accepted Answer · 2023-04-03 06:20:19Z

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

answered Apr 3, 2023 at 6:20

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

2 Comments

Faraaz Over a year ago

I cannot do that because our application uses functions only, so I have to write whole code in function only.

Paul Maxwell Over a year ago

Your function is pure postgres, so if you pass in malicious code you will execute malicious code. You are not taking advantage of dapper at all. In a comment beneath the question I provided a url. Try that.

Collectives™ on Stack Overflow

Avoid SQL injection in Postgres sql dynamic query using Dapper

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related