Difference in bytea Column

Difference in bytea Column

Postby chris901 » Mon 14 Nov 2016 21:55

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?
chris901
 
Posts: 64
Joined: Wed 20 Jul 2016 04:21

Re: Difference in bytea Column

Postby Pinturiccio » Thu 17 Nov 2016 11:32

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.
Pinturiccio
Devart Team
 
Posts: 2024
Joined: Wed 02 Nov 2011 09:44

Re: Difference in bytea Column

Postby chris901 » Thu 17 Nov 2016 17:50

I can confirm it works with a simple console application.

However my setup is DevExpress PictureEdit and PgSqlDataTable using DataBindings.

Image

Here is the result of a query:
Image

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.
chris901
 
Posts: 64
Joined: Wed 20 Jul 2016 04:21

Re: Difference in bytea Column

Postby Pinturiccio » Fri 18 Nov 2016 13:01

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.
Pinturiccio
Devart Team
 
Posts: 2024
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for PostgreSQL