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
