Difference in bytea Column

[chris901]

Hello,

for my application I need to store images with their SHA-256 hash values in the database as bytea columns.

For example:
Image: http://i.imgur.com/Yt7JuZA.jpg
SHA-256: 98A5EC4F6409B5BAB4DD7F0F13AA3E8404B48B09C6B41FEA6C2EDC331DF8293C

My images table looks like this:

Code: Select all

create table images
(
  image_id            serial,
  image               bytea,
  hash                bytea,

  primary key (image_id)
);

To get the SHA-256 hash I use this: select encode(digest(image, 'SHA256'), 'hex') as StringHash;

I use a WinForms application with PictureEdit (DevExpress) and PgSqlDataTable to let the user choose the image and then save it to the database.

The images get saved in the database and I can view and export them.
However the inserted binary value is different from the original file and therefore I get an incorrect hash.

I noticed that when I use this function to import the image with pgAdmin in the database it works fine: http://dba.stackexchange.com/a/2962/105745

However this function relies on a local file on the database server. By using the PictureEdit and PgSqlDataTable I can choose local files - which is important.

I compare the inserted bytea binary string in the database:

pgAdmin:

Code: Select all

\377\330\377\340\000\020JFIF\000\001\001\002\000\034\000\034\000\000\377\333\000C\000\003\002\002\002\002\002\003\002\002\002\003\003\003\ (...)

PgSqlDataTable:

Code: Select all

\377\330\377\340\000\020JFIF\000\001\001\001\000G\000G\000\000\377\333\000C\000\003\002\002\002\002\002\003\002\002\002\003\003\003\003\004\ (...)

As you can see there is a change in the 10th octet.
My guess is that the image header somehow gets corrupted.

Why is this change happening and how can I stop it?

[Pinturiccio]

We could not reproduce the issue. We use the following code in a simple Console application:

Code: Select all

PgSqlConnection conn = new PgSqlConnection("your connection string");
conn.Open();
PgSqlCommand comm = new PgSqlCommand("select * from images", conn);
PgSqlDataTable dt = new PgSqlDataTable(comm);
byte[] image = File.ReadAllBytes("Yt7JuZA - Imgur.jpg");
dt.Open();
var row = dt.NewRow();
row["image_id"] = 1; //change it to a valid PK
row["image"] = image;
dt.Rows.Add(row);
dt.Update();

Also, we inserted one more record together with pgAdmin using the bytea_import function, link to which you sent:

Code: Select all

insert into images(image) select bytea_import('Yt7JuZA - Imgur.jpg');

As a result, in pgAdmin the similar value was inserted into the image column via PgSqlDataTable and via pgAdmin.
When executing the following query, we received the similar value:

Code: Select all

SELECT md5(image) FROM images;

Try to execute the code above and notify us about the results. Is the issue reproduced with a simple console application?

Also, please provide us with the following information:
1. PostgreSQL version;
2. dotConnect for PostgreSQL version;
3. Connection strings (roughly, without credentials).

If possible, create and send us a small test project for reproducing the issue.

[chris901]

I can confirm it works with a simple console application.

However my setup is DevExpress PictureEdit and PgSqlDataTable using DataBindings.



Here is the result of a query:


1. row used bytea_import.
2. row used PgSqlDataTable console application.
3. row used WinForms and DevExpress PictureEdit.

I suppose PgSqlDataTable gets a modified byte value from the PictureEdit in this case?

I am using latest dotConnect and PostgreSql versions.

[Pinturiccio]

According to your response, the issue is not connected with dotConnect for PostgreSQL. The example with console application shows that PgSqlDataTable transmits the correct set of bytes to database. Therefore, the reason is in PictureEdit. Somehow, it modifies the byte set and transmits the modified set to PgSqlDataTable. Our components inserts the data set that was transmitted to it.

To resolve the issue, please be advised to contact the DevExpress support.