CODEWITHSUNDEEP
c#postgresqlnpgsql
Mustafa BYKSY
Mustafa BYKSY

Reputation: 93

Npgsql 4.0 does not return dictionary parameter clearly

When I insert values the table with dictionary params and some exceptions has been occurred, the npgsql does not return SQL query clearly. Example:

Psuedo Code:

Dictionary<string, object> params = new Dictionary <string, object>();
string sql: "insert into bla values (:item1)";
params.Add ("item1", 'bla bla');
ExecuteNonQuery (sql);

The sample code block: 

using (NpgsqlCommand cmd = new NpgsqlCommand("insert into foo values (:TEST)",conn))
            {
                cmd.Parameters.Add(new NpgsqlParameter<string>("TEST",
                  NpgsqlDbType.Varchar));
                cmd.Parameters[0].Value = "null \0 null";

                cmd.ExecuteNonQuery();
            }
        }

Then I get an error like this:

Database Exception: 22021: invalid byte sequence for encoding "UTF8": 0x00. Query ==> insert into foo values ($1)

Before using npgsql 2.2.5 I got an error like this:

Database Exception: 22021: invalid byte sequence for encoding "UTF8": 0x00. Query ==> insert into foo values ('null \0 null')

If I get an error for insert statement such as unicode problem, I get an error like;

Database Exception: INSERT INTO bla Values ($1);

When I use npgsql 2.2.5, then I get an error like:

Database Exception: INSERT INTO bla Values ('bla bla');

Why npgsql hide dictionary param value from me? :)

Upvotes: 0

Views: 267

Answers (2)

Shay Rojansky
Shay Rojansky

Reputation: 16722

Npgsql 2.x (which is extremely old) sent your parameters by inlining them into your SQL query ("client-side parameter binding"), except when the command was prepared. Npgsql 3.x switched to always sending parameters in binary format outside of the SQL itself, which is superior in terms of performance, security, etc. This is why your exception messages don't include actual parameter values.

Note that including parameter values in exception messages can be considered not secure in some scenarios.

Upvotes: 1

Vivek Nuna
Vivek Nuna

Reputation: 1

You have to provide the column and value to the query like this.

Run the similar code in for each item in dictionary with foreach

String sql = "INSERT INTO TableName(ColumName) VALUES (:value)";

Upvotes: 0

Related Questions