postgresqldapper

Insert parameters when sending notify event

I want to send some data to PostgreSQL using a NOTIFY event and I have the following code to do so

await using var conn = new NpgsqlConnection(connectionString);
await conn.OpenAsync();
await conn.ExecuteAsync(
    $"NOTIFY my_event, '{JsonConvert.SerializeObject(data)}';"
);

However, this causes some issues if my data contains any strings that involve a ' (and is generally susceptible to SQL injection) so I was looking at passing these values down via parameters, so I tried

await using var conn = new NpgsqlConnection(connectionString);
await conn.OpenAsync();
await conn.ExecuteAsync(
    "NOTIFY my_event, @Data;", new { Data = JsonConvert.SerializeObject(data) }
);

However, this doesn't seem to work as expected and throws an error

Unhandled exception. Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "$1"

POSITION: 18
   at Npgsql.Internal.NpgsqlConnector.ReadMessageLong(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder`1.StateMachineBox`1.System.Threading.Tasks.Sources.IValueTaskSource<TResult>.GetResult(Int16 token)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at Dapper.SqlMapper.ExecuteImplAsync(IDbConnection cnn, CommandDefinition command, Object param) in /_/Dapper/SqlMapper.Async.cs:line 662
   at Program.<Main>$(String[] args) in /home/chris/RiderProjects/PostgresExample/PostgresNotifier/Program.cs:line 17
   at Program.<Main>$(String[] args) in /home/chris/RiderProjects/PostgresExample/PostgresNotifier/Program.cs:line 20
   at Program.<Main>(String[] args)
  Exception data:
    Severity: ERROR
    SqlState: 42601
    MessageText: syntax error at or near "$1"
    Position: 18
    File: scan.l
    Line: 1242
    Routine: scanner_yyerror

Looking online it looked like most people were just hard coding this data and not passing it down by parameters. Is this possible?

Solution

  • There is a function pg_notify which does the same thing as NOTIFY, but handles its parameters in the usual way that functions do. I don't now what dapper is, but presumably it would be done something like:

    select pg_notify($1,$2)