How to convert the output format of decrypt_iv() function to text in postgres?

Question

I am using the following definition for the postgres functions:

DROP FUNCTION IF EXISTS EncryptStringWithIV(text);
create or replace function EncryptStringWithIV(email text) returns bytea as '
declare
  Key text;
  IV text;
  value bytea;
begin
  select sStringValue into Key from XtkOption where sName=''Key''; 
  select sStringValue into IV from XtkOption where sName=''IV'';
  value = encrypt_iv($1::bytea, Key::bytea, IV::bytea, ''aes'');
  return value;
end;
' language plpgsql
;

DROP FUNCTION IF EXISTS DecryptStringWithIV(bytea);
create or replace function DecryptStringWithIV(email bytea) returns bytea as '
declare
  Key text;
  IV text;
value bytea;
begin
  select sStringValue into Key from XtkOption where sName=''Key''; 
  select sStringValue into IV from XtkOption where sName=''IV'';
  value = $1;
  return decrypt_iv(value, Key::bytea, IV::bytea, ''aes'');
end;
' language plpgsql
;

This is my output when the fn. is called :

EncryptStringWithIV('123') - \x8dd75f487a7b45e73fbe365545f0506a DecryptStringWithIV('\\x8dd75f487a7b45e73fbe365545f0506a') - \x313233

Should I be converting the output format to get back the exact text value (123)? I am not able to figure out where exactly I am going wrong. any help would be greatly appreciated. Thanks.

Your functions return a bytea and the default way of display such a "blob" is hex encoded in Postgres. If you want a proper number, then change your code to return a proper number, not some binary value — user330315
– user330315, Commented Jul 13, 2020 at 14:33
Yes, I tried encoding the encrypted value as base64 to get a text o/p and then decoding it back in the decrypt fn from base64 before decryption. It worked fine then. Thanks. — Tweety
– Tweety, Commented Jul 14, 2020 at 6:28

Mike Organek · Accepted Answer · 2020-07-13 14:56:18Z

1

You need to encode to get it from bytea to text.

encode(DecryptStringWithIV('\\x8dd75f487a7b45e73fbe365545f0506a'), 'escape')

Or change your decrypt function:

create or replace function DecryptStringWithIV(email bytea) returns text as '
declare
  Key text;
  IV text;
value bytea;
begin
  select sStringValue into Key from XtkOption where sName=''Key''; 
  select sStringValue into IV from XtkOption where sName=''IV'';
  value = $1;
  return encode(decrypt_iv(value, Key::bytea, IV::bytea, ''aes''), ''escape'');
end;
' language plpgsql
;

edited Jul 13, 2020 at 14:56

answered Jul 13, 2020 at 14:34

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tweety Over a year ago

Hi Mike, Thanks, for your answer. I used dec = decrypt_iv(value, oppoKey::bytea, oppoIV::bytea, ''aes''); return encode(dec, ''escape''); and it gave the correct output for DecryptStringWithIV(EncryptStringWithIV('123')) as 123, but for DecryptStringWithIV('\\x8dd75f487a7b45e73fbe365545f0506a') it still gives \x313233.

Mike Organek Over a year ago

@Tweety Can you check to see if you have more than one DecryptStringWithIV() function defined with a different parameter type?

Tweety Over a year ago

I tried this encrypt with encode(encrypt_iv($1::bytea, Key::bytea, IV::bytea, ''aes''),''base64'') and decrypt with

value = decode($1,''base64'');   dec = decrypt_iv(value::bytea, oppoKey::bytea, oppoIV::bytea, ''aes'');   return encode(dec,''escape'');

And it seems to work fine now. Thanks for your guidance.

Collectives™ on Stack Overflow

How to convert the output format of decrypt_iv() function to text in postgres?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related