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

Question

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

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

3. 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!

Answer 1

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