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.

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

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

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!

Your #3 snippet needs quotes around the {userId} parameters. Triple quoting does not alter the case of the string, so there's something else happening in your #2 snippet. — Tim Roberts
– Tim Roberts, Commented Dec 27, 2023 at 20:11
@TimRoberts "Your #3 snippet needs quotes around the" - no it does not AFAIK. It should behave the same as 2nd one (and it basically does) - EF should create parameterized query from the FormattableString accepted by ExecuteSqlInterpolatedAsync. Otherwise it would have all the same SQL injections problems as the first one and there would be no point in introduction of such method. — Guru Stron
– Guru Stron, Commented Dec 27, 2023 at 21:13
@GuruStron No. He's not using Postgres substitution. Notice he's not passing parameters. He's using C# $ string substitution, so it will still have injection problems. — Tim Roberts
– Tim Roberts, Commented Dec 27, 2023 at 21:18
@TimRoberts yes there is substitution, no there is no injection problems if method is correctly used. That is the goal of RelationalDatabaseFacadeExtensions.ExecuteSqlInterpolatedAsync - "Any parameter values you supply will automatically be converted to a DbParameter." — Guru Stron
– Guru Stron, Commented Dec 27, 2023 at 21:20
@TimRoberts see that it accepts FormattableString, not just string. Compiler will not build the string and this allows EF to see all the {} placeholders and their values and turn them into query parameters. — Guru Stron
– Guru Stron, Commented Dec 27, 2023 at 21:23

Charlieface · Accepted Answer · 2023-12-27 21:23:26Z

2

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

answered Dec 27, 2023 at 21:23

Charlieface

78.8k8 gold badges35 silver badges77 bronze badges

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

Comments

Guru Stron · Accepted Answer · 2023-12-27 21:24:33Z

2

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.

edited Dec 27, 2023 at 21:24

answered Dec 27, 2023 at 21:00

Guru Stron

151k11 gold badges187 silver badges233 bronze badges

1 Comment

Fatorin Over a year ago

Thank you for your assistance. It turns out I misunderstood the functionality of the DO statement.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related