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

jsonb_path_exists with Ef Core and Npgsql causes sql injection – Postgresql

I have a query and two parameters. uuid and text. NpgsqlParameter is converting values into single quotes, which causes a syntax error. There must be double quotes in jsonb_path_exists.

code:

var idParam = new NpgsqlParameter("id", NpgsqlDbType.Uuid) { Value = id};
var queryParam = new NpgsqlParameter("q", NpgsqlDbType.Text) { Value = q };
var sql = @"SELECT * FROM ""MyTable""
           WHERE jsonb_path_exists(""Value"",
                    '$[*] ? (@.id == @id && @.text like_regex @q flag ""i"" )')";
var rawSql = Context.MyTable.FromSqlRaw(sql, idParam, queryParam);

If I set the type as NpgsqlDbType.JsonPath it works correctly but is vulnerable to sql injection

var condition = $"'$[*] ? (@.id == "{id}"  && @.text like_regex "{q}"  flag "i" )'";
var jsonPathParam = new NpgsqlParameter("jsonPath", NpgsqlDbType.JsonPath) { Value = $"({condition})" };

ef core and npgsql version is 6.0.4

edit: is it possible detected to vulnerable to sql injection of queryParam without run query

Tags:

2

Answers


  1. 0

    It seems there is a bug in Npgsql, that is causing it to escape JSON path parameters incorrectly. I suggest you file this as a bug on their Github repo.

    But you can’t inject column or variable names like that into a jsonpath anyway. You need to use the vars parameter. This requires building a jsonb object of your parameters, you can do this using jsonb_build_object or to_jsonb, or you can pass it in from C#.

    var idParam = new NpgsqlParameter("id", NpgsqlDbType.Uuid) { Value = id};
var queryParam = new NpgsqlParameter("q", NpgsqlDbType.Text) { Value = q };
var sql = @"
SELECT t.*
FROM MyTable AS t
WHERE jsonb_path_exists(
    t.Value,
    '$[*] ? (@.id == $id && @.text like_regex $q flag ""i"")',
    jsonb_build_object('id', @id, 'q', @q)
);
";
var rawSql = Context.MyTable.FromSqlRaw(sql, idParam, queryParam);
    Login or Signup to reply.
  2. 0

    I’m not seeing the SQL injection here: you’re the one doing string interpolation into a string which you then send as a JSONPATH parameter; PostgreSQL then parses and interprets that JSONPATH separately.

    In other words, you’re allowing an arbitrary JSONPATH to be executed, based on the variables being interpolated into the parameter (condition, id, q…). However, in no case should it be possible to "escape" the JSONPATH expression itself and cause arbitrary SQL to be executed.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search