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

C# 11 raw string literals with ExecuteSqlRawAsync on PostgreSQL have case sensitive problem

I encountered an issue while attempting to modify my SQL query to address warnings about potential SQL injection. I aimed to parameterize the query and use raw string literals, but I received an error message instead.

It seems the error is related to the case sensitivity of my column names, and unfortunately, I can’t alter the entire database to lowercase them.

I’ve experimented with various other approaches, but they either resulted in errors or failed to correctly retrieve the UserId.

Below is the code I tried.

  1. Original Version (SQL injection problem)
await dbContext.Database.ExecuteSqlRawAsync(
$@"
    DO $$ 
    BEGIN 
        IF (SELECT COUNT(*) FROM ""Notification"" WHERE ""UserId"" = '{userId}') > 20 THEN
            DELETE FROM ""Notification""
            WHERE ""UserId"" = '{userId}' AND ""IsReceived"" = 'TRUE' AND ""ContentId"" NOT IN (
                SELECT ""ContentId"" FROM ""Notification""
                WHERE ""UserId"" = '{userId}'
                ORDER BY ""CreatedAt"" DESC
                LIMIT 20
            );
        END IF;
    END $$"
);
  1. Raw string literals Version

(error: Npgsql.PostgresException : 42703: column "userid" does not exist)

var param = new NpgsqlParameter("@UserId", userId);
await dbContext.Database.ExecuteSqlRawAsync(
"""
    DO $$ 
    BEGIN 
        IF (SELECT COUNT(*) FROM "Notification" WHERE "UserId" = @UserId) > 20 THEN
            DELETE FROM "Notification"
            WHERE "UserId" = @UserId AND "IsReceived" = 'TRUE' AND "ContentId" NOT IN (
                SELECT "ContentId" FROM "Notification"
                WHERE "UserId" = @UserId
                ORDER BY "CreatedAt" DESC
                LIMIT 20
            );
        END IF;
    END $$
""", param);
  1. Raw string literals Version with ExecuteSqlInterpolatedAsync

(error: Npgsql.PostgresException : 42703: column "p0" does not exist)

await dbContext.Database.ExecuteSqlInterpolatedAsync(
$"""
    DO $$ 
    BEGIN 
        IF (SELECT COUNT(*) FROM "Notification" WHERE "UserId" = {userId}) > 20 THEN
            DELETE FROM "Notification"
            WHERE "UserId" = {userId} AND "IsReceived" = 'TRUE' AND "ContentId" NOT IN (
                SELECT "ContentId" FROM "Notification"
                WHERE "UserId" = {userId}
                ORDER BY "CreatedAt" DESC
                LIMIT 20
            );
        END IF;
    END $$
""");

I’d appreciate guidance on the best way to proceed with modifications.

Thank you for your assistance!

Tags:

2

Answers


  1. 0

    AFAIK you can’t use parameters with DO. From the docs:

    DO executes an anonymous code block, or in other words a transient anonymous function in a procedural language.
    The code block is treated as though it were the body of a function with no parameters, returning void. It is parsed and executed a single time.

    So it treats the parameter names as column names (and obviously fails).

    So just rewrite the query so no DO is needed or create a database function and use EF to invoke it with parameters.

    Also see this answer.

    Login or Signup to reply.
  2. 0

    The IF isn’t actually necessary, so you could just use a normal SqlInterpolated block (ie fully parameterized).

    await dbContext.Database.ExecuteSqlInterpolatedAsync(
$"""
    DELETE FROM "Notification"
    WHERE "UserId" = {userId}
      AND "IsReceived" = TRUE
      AND "ContentId" NOT IN (
          SELECT n2."ContentId"
          FROM "Notification" n2
          WHERE n2."UserId" = {userId}
          ORDER BY n2."CreatedAt" DESC
          LIMIT 20
      );
""");
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search