Thread: pgp_sym_decrypt() - error 39000: wrong key or corrupt data

pgp_sym_decrypt() - error 39000: wrong key or corrupt data

From

Moreno Andreo

Date:

21 June 2018, 21:36:23

Hi,
     while playing with pgcrypto I ran into a strange issue (postgresql 
9.5.3 x86 on Windows 7)

Having a table with a field
dateofbirth text

I made the following sequence of SQL commands
update tbl_p set dateofbirth = pgp_sym_encrypt('2018-06-21', 'AES_KEY') 
where codguid = '00000001-0001-0001-0001-000000000001';
OK

select pgp_sym_decrypt(dateofbirth::bytea, 'AES_KEY') as datanasc from 
tbl_p where codguid = '00000001-0001-0001-0001-000000000001'
'2018-06-21'

select * from tab_paz where pgp_sym_decrypt(natoil::bytea, 'AES_KEY') = 
'2018-06-21'
ERROR:  Wrong key or corrupt data
********** Error **********

ERROR: Wrong key or corrupt data
SQL state: 39000

Can't find reference anywhere...
Any help would be appreciated.
Thanks,
Moreno.-

Re: pgp_sym_decrypt() - error 39000: wrong key or corrupt data

From

Adrian Klaver

Date:

22 June 2018, 03:31:16

On 06/21/2018 08:36 AM, Moreno Andreo wrote:
> Hi,
>      while playing with pgcrypto I ran into a strange issue (postgresql 
> 9.5.3 x86 on Windows 7)
> 
> Having a table with a field
> dateofbirth text
> 
> I made the following sequence of SQL commands
> update tbl_p set dateofbirth = pgp_sym_encrypt('2018-06-21', 'AES_KEY') 
> where codguid = '00000001-0001-0001-0001-000000000001';
> OK
> 
> select pgp_sym_decrypt(dateofbirth::bytea, 'AES_KEY') as datanasc from 
> tbl_p where codguid = '00000001-0001-0001-0001-000000000001'
> '2018-06-21'
> 
> select * from tab_paz where pgp_sym_decrypt(natoil::bytea, 'AES_KEY') = 
> '2018-06-21'

You switched gears above.

What is the data type of the natoil field in table tab_paz?

Was the data encrypted in it using the 'AES_KEY'?

I can replicate the below by doing:

select pgp_sym_decrypt(pgp_sym_encrypt('2018-06-21', 'AES_KEY'), 'AES');
ERROR:  Wrong key or corrupt data


> ERROR:  Wrong key or corrupt data
> ********** Error **********
> 
> ERROR: Wrong key or corrupt data
> SQL state: 39000
> 
> Can't find reference anywhere...
> Any help would be appreciated.
> Thanks,
> Moreno.-
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: pgp_sym_decrypt() - error 39000: wrong key or corrupt data

From

Moreno Andreo

Date:

22 June 2018, 14:46:01

Il 21/06/2018 23:31, Adrian Klaver ha scritto:
> On 06/21/2018 08:36 AM, Moreno Andreo wrote:
>> Hi,
>>      while playing with pgcrypto I ran into a strange issue 
>> (postgresql 9.5.3 x86 on Windows 7)
>>
>> Having a table with a field
>> dateofbirth text
>>
>> I made the following sequence of SQL commands
>> update tbl_p set dateofbirth = pgp_sym_encrypt('2018-06-21', 
>> 'AES_KEY') where codguid = '00000001-0001-0001-0001-000000000001';
>> OK
>>
>> select pgp_sym_decrypt(dateofbirth::bytea, 'AES_KEY') as datanasc 
>> from tbl_p where codguid = '00000001-0001-0001-0001-000000000001'
>> '2018-06-21'
>>
>> select * from tab_paz where pgp_sym_decrypt(natoil::bytea, 'AES_KEY') 
>> = '2018-06-21'
>
> You switched gears above.
>
> What is the data type of the natoil field in table tab_paz?
Sorry, just a typo... natoil is, actually dateofbirth, so it's text.
You can read it as
select * from tbl_p where pgp_sym_decrypt(dateofbirth::bytea, 'AES_KEY') 
= '2018-06-21'
>
> Was the data encrypted in it using the 'AES_KEY'?
Yes, the command sequence is exactly reported above.
If I use pgp_sym_decrypt in a SELECT statement it's OK, but if it's in a 
where clause it seems not to be working.

Re: pgp_sym_decrypt() - error 39000: wrong key or corrupt data

From

Adrian Klaver

Date:

22 June 2018, 19:18:08

On 06/22/2018 01:46 AM, Moreno Andreo wrote:
> Il 21/06/2018 23:31, Adrian Klaver ha scritto:
>> On 06/21/2018 08:36 AM, Moreno Andreo wrote:
>>> Hi,
>>>      while playing with pgcrypto I ran into a strange issue 
>>> (postgresql 9.5.3 x86 on Windows 7)
>>>
>>> Having a table with a field
>>> dateofbirth text
>>>
>>> I made the following sequence of SQL commands
>>> update tbl_p set dateofbirth = pgp_sym_encrypt('2018-06-21', 
>>> 'AES_KEY') where codguid = '00000001-0001-0001-0001-000000000001';
>>> OK
>>>
>>> select pgp_sym_decrypt(dateofbirth::bytea, 'AES_KEY') as datanasc 
>>> from tbl_p where codguid = '00000001-0001-0001-0001-000000000001'
>>> '2018-06-21'
>>>
>>> select * from tab_paz where pgp_sym_decrypt(natoil::bytea, 'AES_KEY') 
>>> = '2018-06-21'
>>
>> You switched gears above.
>>
>> What is the data type of the natoil field in table tab_paz?
> Sorry, just a typo... natoil is, actually dateofbirth, so it's text.
> You can read it as
> select * from tbl_p where pgp_sym_decrypt(dateofbirth::bytea, 'AES_KEY') 
> = '2018-06-21'
>>
>> Was the data encrypted in it using the 'AES_KEY'?
> Yes, the command sequence is exactly reported above.
> If I use pgp_sym_decrypt in a SELECT statement it's OK, but if it's in a 
> where clause it seems not to be working.

Are you sure that the entries where not encrypted with a different key 
because I can't replicate.(More comments below):

create table pgp_test(id integer, fld_1 varchar);

insert  into pgp_test values (1, pgp_sym_encrypt('2018-06-21', 'AES_KEY'))

select * from pgp_test ;

  id | 
         fld_1 


----+------------------------------------------------------------------------------------------------------------------------------------------------------------
   1 | 

\xc30d04070302444e9b2792436e3d7ed23b01cf097f0a6a36298bab63ae4f22f39de54a9b0d8f905d48198ce76089de5f21669c46d96439718b6b0408c541427b6e7c11008bd3d0ebdae0dceb

select * from pgp_test where pgp_sym_decrypt(fld_1::bytea, 'AES_KEY') = 
'2018-06-21';

id | 
       fld_1 


----+------------------------------------------------------------------------------------------------------------------------------------------------------------
   1 | 

\xc30d04070302444e9b2792436e3d7ed23b01cf097f0a6a36298bab63ae4f22f39de54a9b0d8f905d48198ce76089de5f21669c46d96439718b6b0408c541427b6e7c11008bd3d0ebdae0dceb


Have you looked at the entry in its encrypted state to see if it looks 
the same as pgp_sym_encrypt('2018-06-21', 'AES_KEY')?

Can you return decrypted values for other items in the table?


> 
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: pgp_sym_decrypt() - error 39000: wrong key or corrupt data

From

Moreno Andreo

Date:

22 June 2018, 22:50:13

Il 22/06/2018 15:18, Adrian Klaver ha scritto:
> On 06/22/2018 01:46 AM, Moreno Andreo wrote:
>> Il 21/06/2018 23:31, Adrian Klaver ha scritto:
>>> On 06/21/2018 08:36 AM, Moreno Andreo wrote:
>>>> Hi,
>>>>      while playing with pgcrypto I ran into a strange issue 
>>>> (postgresql 9.5.3 x86 on Windows 7)
>>>>
>>>> Having a table with a field
>>>> dateofbirth text
>>>>
>>>> I made the following sequence of SQL commands
>>>> update tbl_p set dateofbirth = pgp_sym_encrypt('2018-06-21', 
>>>> 'AES_KEY') where codguid = '00000001-0001-0001-0001-000000000001';
>>>> OK
>>>>
>>>> select pgp_sym_decrypt(dateofbirth::bytea, 'AES_KEY') as datanasc 
>>>> from tbl_p where codguid = '00000001-0001-0001-0001-000000000001'
>>>> '2018-06-21'
>>>>
>>>> select * from tab_paz where pgp_sym_decrypt(natoil::bytea, 
>>>> 'AES_KEY') = '2018-06-21'
>>>
>>> You switched gears above.
>>>
>>> What is the data type of the natoil field in table tab_paz?
>> Sorry, just a typo... natoil is, actually dateofbirth, so it's text.
>> You can read it as
>> select * from tbl_p where pgp_sym_decrypt(dateofbirth::bytea, 
>> 'AES_KEY') = '2018-06-21'
>>>
>>> Was the data encrypted in it using the 'AES_KEY'?
>> Yes, the command sequence is exactly reported above.
>> If I use pgp_sym_decrypt in a SELECT statement it's OK, but if it's 
>> in a where clause it seems not to be working.
>
> Are you sure that the entries where not encrypted with a different key 
> because I can't replicate.(More comments below):
(other replies below, inline)
I'm almost sure (you're never absolutely sure :-) ), since I kept all 
commands I entered in PgAdminIII SQL Window, and they're reported above.
On the other side, I tried the same procedure on another field and it 
succeeded.

The only difference between the 2 fields, and I don't know if it can 
make any sense, is that the field I tried now and succeeded was created 
as text, while the other field (dateofbirth) was a timestamp I ALTERed 
with the statement
alter table tbl_p alter column dateofbirth type text using 
to_char(dateofbirth, 'YYYY-MM-DD');

I'm just afraid it can happen in production....

>
> create table pgp_test(id integer, fld_1 varchar);
>
> insert  into pgp_test values (1, pgp_sym_encrypt('2018-06-21', 
> 'AES_KEY'))
>
> select * from pgp_test ;
>
>  id |         fld_1
>
----+------------------------------------------------------------------------------------------------------------------------------------------------------------

>
>   1 | 
>
\xc30d04070302444e9b2792436e3d7ed23b01cf097f0a6a36298bab63ae4f22f39de54a9b0d8f905d48198ce76089de5f21669c46d96439718b6b0408c541427b6e7c11008bd3d0ebdae0dceb
>
> select * from pgp_test where pgp_sym_decrypt(fld_1::bytea, 'AES_KEY') 
> = '2018-06-21';
>
> id |       fld_1
>
----+------------------------------------------------------------------------------------------------------------------------------------------------------------

>
>   1 | 
>
\xc30d04070302444e9b2792436e3d7ed23b01cf097f0a6a36298bab63ae4f22f39de54a9b0d8f905d48198ce76089de5f21669c46d96439718b6b0408c541427b6e7c11008bd3d0ebdae0dceb
>
>
> Have you looked at the entry in its encrypted state to see if it looks 
> the same as pgp_sym_encrypt('2018-06-21', 'AES_KEY')?
Yes, it seems to have the same value

Re: pgp_sym_decrypt() - error 39000: wrong key or corrupt data

From

Adrian Klaver

Date:

22 June 2018, 23:56:28

On 06/22/2018 09:50 AM, Moreno Andreo wrote:
> Il 22/06/2018 15:18, Adrian Klaver ha scritto:

>> Are you sure that the entries where not encrypted with a different key 
>> because I can't replicate.(More comments below):
> (other replies below, inline)
> I'm almost sure (you're never absolutely sure :-) ), since I kept all 
> commands I entered in PgAdminIII SQL Window, and they're reported above.
> On the other side, I tried the same procedure on another field and it 
> succeeded.
> 
> The only difference between the 2 fields, and I don't know if it can 
> make any sense, is that the field I tried now and succeeded was created 
> as text, while the other field (dateofbirth) was a timestamp I ALTERed 
> with the statement
> alter table tbl_p alter column dateofbirth type text using 
> to_char(dateofbirth, 'YYYY-MM-DD');

Assuming the ALTER TABLE was done and then the values where encrypted, 
that does not seem to affect anything here(More below):

test=# create table pgp_alter_test(id integer, birthdate date);
CREATE TABLE
test=# \d pgp_alter_test
             Table "public.pgp_alter_test"
   Column   |  Type   | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
  id        | integer |           |          |
  birthdate | date    |           |          |

test=# insert into pgp_alter_test values (1, '2018-06-21');
INSERT 0 1
test=# select * from pgp_alter_test ;
  id | birthdate
----+------------
   1 | 2018-06-21
(1 row)

test=# alter table pgp_alter_test alter column birthdate type text using 
to_char(birthdate, 'YYYY-MM-DD');
ALTER TABLE

test=# \d pgp_alter_test
             Table "public.pgp_alter_test"
   Column   |  Type   | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
  id        | integer |           |          |
  birthdate | text    |

test=# select * from pgp_alter_test ;
  id | birthdate 
 

----+------------ 
 

   1 | 2018-06-21 
 

(1 row) 
 

 
 

test=# update pgp_alter_test set birthdate = pgp_sym_encrypt(birthdate, 
'AES_KEY') where id = 1; 

UPDATE 1 
 

test=# select * from pgp_alter_test ; 

  id | 
       birthdate 


----+------------------------------------------------------------------------------------------------------------------------------------------------------------


   1 | 

\xc30d04070302b3f55c80f9ab657c68d23b010cd9d4d82631f89c786394a8bceb35529db07c708e5a0c4f04cf91aed24b5ff397dd99c678ec4f5bb769e148cfae3cdfc5453daaeb34ddd2737c


(1 row) 
 

 
 
 

                                                              ^ 
 

test=# select * from pgp_alter_test where 
pgp_sym_decrypt(birthdate::bytea, 'AES_KEY') = '2018-06-21'; 

  id | 
       birthdate 


----+------------------------------------------------------------------------------------------------------------------------------------------------------------


   1 | 

\xc30d04070302b3f55c80f9ab657c68d23b010cd9d4d82631f89c786394a8bceb35529db07c708e5a0c4f04cf91aed24b5ff397dd99c678ec4f5bb769e148cfae3cdfc5453daaeb34ddd2737c


(1 row)

I am at a loss now. The only thing I can think of is that data itself is 
actually corrupted. Maybe some sort of language encoding/collation 
issue. Just not sure how to test that at the moment.
 

> 
> I'm just afraid it can happen in production....
> 
>>
>> create table pgp_test(id integer, fld_1 varchar);
>>
>> insert  into pgp_test values (1, pgp_sym_encrypt('2018-06-21', 
>> 'AES_KEY'))
>>

>> Have you looked at the entry in its encrypted state to see if it looks 
>> the same as pgp_sym_encrypt('2018-06-21', 'AES_KEY')?
> Yes, it seems to have the same value

So
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: pgp_sym_decrypt() - error 39000: wrong key or corrupt data

From

Moreno Andreo

Date:

27 June 2018, 22:55:15

Il 22/06/2018 19:56, Adrian Klaver ha scritto:
> On 06/22/2018 09:50 AM, Moreno Andreo wrote:
>> Il 22/06/2018 15:18, Adrian Klaver ha scritto:
>
>>> Are you sure that the entries where not encrypted with a different 
>>> key because I can't replicate.(More comments below):
>> (other replies below, inline)
>> I'm almost sure (you're never absolutely sure :-) ), since I kept all 
>> commands I entered in PgAdminIII SQL Window, and they're reported above.
>> On the other side, I tried the same procedure on another field and it 
>> succeeded.
>>
>> The only difference between the 2 fields, and I don't know if it can 
>> make any sense, is that the field I tried now and succeeded was 
>> created as text, while the other field (dateofbirth) was a timestamp 
>> I ALTERed with the statement
>> alter table tbl_p alter column dateofbirth type text using 
>> to_char(dateofbirth, 'YYYY-MM-DD');
>
> Assuming the ALTER TABLE was done and then the values where encrypted, 
> that does not seem to affect anything here(More below):
>
> test=# create table pgp_alter_test(id integer, birthdate date);
> CREATE TABLE
> test=# \d pgp_alter_test
>             Table "public.pgp_alter_test"
>   Column   |  Type   | Collation | Nullable | Default
> -----------+---------+-----------+----------+---------
>  id        | integer |           |          |
>  birthdate | date    |           |          |
>
> test=# insert into pgp_alter_test values (1, '2018-06-21');
> INSERT 0 1
> test=# select * from pgp_alter_test ;
>  id | birthdate
> ----+------------
>   1 | 2018-06-21
> (1 row)
>
> test=# alter table pgp_alter_test alter column birthdate type text 
> using to_char(birthdate, 'YYYY-MM-DD');
> ALTER TABLE
>
> test=# \d pgp_alter_test
>             Table "public.pgp_alter_test"
>   Column   |  Type   | Collation | Nullable | Default
> -----------+---------+-----------+----------+---------
>  id        | integer |           |          |
>  birthdate | text    |
>
> test=# select * from pgp_alter_test ;
>  id | birthdate
>
> ----+------------
>
>   1 | 2018-06-21
>
> (1 row)
>
>
>
>
> test=# update pgp_alter_test set birthdate = 
> pgp_sym_encrypt(birthdate, 'AES_KEY') where id = 1;
> UPDATE 1
>
> test=# select * from pgp_alter_test ;
>  id |       birthdate
>
----+------------------------------------------------------------------------------------------------------------------------------------------------------------

>
>   1 | 
>
\xc30d04070302b3f55c80f9ab657c68d23b010cd9d4d82631f89c786394a8bceb35529db07c708e5a0c4f04cf91aed24b5ff397dd99c678ec4f5bb769e148cfae3cdfc5453daaeb34ddd2737c

>
> (1 row)
>
>
>
>
>
>                                                              ^
>
> test=# select * from pgp_alter_test where 
> pgp_sym_decrypt(birthdate::bytea, 'AES_KEY') = '2018-06-21';
>  id |       birthdate
>
----+------------------------------------------------------------------------------------------------------------------------------------------------------------

>
>   1 | 
>
\xc30d04070302b3f55c80f9ab657c68d23b010cd9d4d82631f89c786394a8bceb35529db07c708e5a0c4f04cf91aed24b5ff397dd99c678ec4f5bb769e148cfae3cdfc5453daaeb34ddd2737c

>
> (1 row)
>
> I am at a loss now. The only thing I can think of is that data itself 
> is actually corrupted. Maybe some sort of language encoding/collation 
> issue. Just not sure how to test that at the moment.

Actually, I tried it in a bunch of other fields with varying data types 
and everything went fine.
I don't know if it's as you say and I mismatched keys (and I need 
another pair of glasses) or something else. Just hoping (but being 
confident) it won't happen again.

Now trying to speed up a little some queries involving SELECTing among 
these encrypted fields, if I'm stuck I'll open a new thread.

Thanks,
Moreno.-

>
>
>>
>> I'm just afraid it can happen in production....
>>
>>>
>>> create table pgp_test(id integer, fld_1 varchar);
>>>
>>> insert  into pgp_test values (1, pgp_sym_encrypt('2018-06-21', 
>>> 'AES_KEY'))
>>>
>
>>> Have you looked at the entry in its encrypted state to see if it 
>>> looks the same as pgp_sym_encrypt('2018-06-21', 'AES_KEY')?
>> Yes, it seems to have the same value
>
> So
>>
>>
>>
>
>

Re: pgp_sym_decrypt() - error 39000: wrong key or corrupt data

From

Adrian Klaver

Date:

28 June 2018, 04:29:29

On 06/27/2018 09:55 AM, Moreno Andreo wrote:
> Il 22/06/2018 19:56, Adrian Klaver ha scritto:
>> On 06/22/2018 09:50 AM, Moreno Andreo wrote:
>>> Il 22/06/2018 15:18, Adrian Klaver ha scritto:
>>

>>
>> I am at a loss now. The only thing I can think of is that data itself 
>> is actually corrupted. Maybe some sort of language encoding/collation 
>> issue. Just not sure how to test that at the moment.
> 
> Actually, I tried it in a bunch of other fields with varying data types 
> and everything went fine.
> I don't know if it's as you say and I mismatched keys (and I need 
> another pair of glasses) or something else. Just hoping (but being 
> confident) it won't happen again.

You might try emailing the pgcrypto author
https://www.postgresql.org/docs/10/static/pgcrypto.html#id-1.11.7.35.11

In the source code I noticed that there are many:

px_debug('Some text')

that map to:

{PXE_PGP_CORRUPT_DATA, "Wrong key or corrupt data"}.

I tried running with messages set to DEBUG to see if I could get at the 
more specific messages. That did not work, so you might ask the author 
if there is a way to get at them.

> 
> Now trying to speed up a little some queries involving SELECTing among 
> these encrypted fields, if I'm stuck I'll open a new thread.
> 
> Thanks,
> Moreno.-
> 
>>
>>



-- 
Adrian Klaver
adrian.klaver@aklaver.com