Error while executing postgres query with npgsql dll v3.1.9

Question

I am trying to execute my Postgres Function from C# web api. Somehow while executing it Postgres is giving syntax error. But when used with 'Select' keyword, it does not give any error. I have used similar method umpteen times with older Npgsql library but never got any error. Now using latest Npgsql 3.1.9 first time. Can anyone help me in this?

code used:

    try
    {
    using (NpgsqlConnection con = new NpgsqlConnection(connectionString))
    {
        con.Open();
        string query = "user_signup(" + _cmpid + "::integer,'" + _email + "','" + _contact + "')";
        using (NpgsqlTransaction tran = con.BeginTransaction())
        {
            using (NpgsqlCommand cmd = new NpgsqlCommand(query, con))
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                NpgsqlDataReader reader = cmd.ExecuteReader();            
                if (reader.Read())
                {
                    if (!reader.IsDBNull(0))
                        Id = reader.GetValue(0).ToString();
                    RStatus = "OK";
                }
                reader.Close();
            }
            tran.Dispose();
        }
        con.Close();
    }
}
catch(Exception ex){}

Query call - user_signup(1::integer,'[email protected]','8965472335')

Exception Occurred - 42601: syntax error at or near "("

Stack Trace -

at Npgsql.NpgsqlConnector.DoReadMessage(DataRowLoadingMode dataRowLoadingMode, Boolean isPrependedMessage)
   at Npgsql.NpgsqlConnector.ReadMessageWithPrepended(DataRowLoadingMode dataRowLoadingMode)
   at Npgsql.NpgsqlConnector.ReadMessage(DataRowLoadingMode dataRowLoadingMode)
   at Npgsql.NpgsqlConnector.ReadExpecting[T]()
   at Npgsql.NpgsqlDataReader.NextResultInternal()
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.Execute(CommandBehavior behavior)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReaderInternal(CommandBehavior behavior)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Npgsql.NpgsqlCommand.ExecuteReader()
   at BusToWorkAPI.DBOperations.DbHandler.SignUpUser(Int32 _cmpid, String _email, String _contact) in d:\DbHandler.cs:line 673

just checked with older dll (v2.1.3), everything works absolutely fine... — Tanmay

Shay Rojansky Shay Rojansky · Accepted Answer · 2017-01-15T13:51:47

Since 3.0, Npgsql expects only the function name in CommandText when executing with CommandType.StoredProcedure. You should add NpgsqlParameters to your command with the correct values (at the very least this would prevent the potential SQL injection you have in your current code).

This is documented in the migration notes.

Error while executing postgres query with npgsql dll v3.1.9

1 Answers