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

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(dot)klaver(at)aklaver(dot)com