Funtions + plpgsql + contrib/pgcrypto = ??

From: "Moran(dot)Michael" <Michael(dot)Moran(at)IGT(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Funtions + plpgsql + contrib/pgcrypto = ??
Date: 2005-03-24 16:41:34
Message-ID: B27C8914860EE24E865D189A3735EA53100945@lasexch03.is.ad.igt.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hello there,

What's the preferred and most efficient way to obtain PGCrypto encrypted
data from a plpgsql function?

1. Imagine the following simple table:
CREATE TABLE crypto (
pid SERIAL PRIMARY KEY,
title VARCHAR(50),
crypted_content BYTEA
);

2. Now insert the following 3 rows of data:

INSERT INTO crypto VALUES (1, 'test1', encrypt( '11112222ABCD', 'password',
'aes'));
INSERT INTO crypto VALUES (2, 'test2', encrypt( '22223333BCDE', 'password',
'aes'));
INSERT INTO crypto VALUES (3, 'test3', encrypt( '33334444CDEF', 'password',
'aes'));

3. Using the psql tool, selecting * from the crypto table yields the
following:

# select * from crypto;
id | title | crypted_content
----+-------+------------------------------------------------
1 | test1 | \307\266xI\235"\210a\363=\201\222\247\2660\215
2 | test2 | D\211\256E\372^Y\0259\341\215\271\030]\234\017
3 | test3 | 4\263\251y\006_N\343\0270I\207;\370\270\266

Pretty straight forward, right?

Now how about doing this in a simple plpgsql Function. That's where we
encounter problems. I want to get DECRYPTED data based on an input ID. So...

4. Imagine the following simple plpgsql function (note I'm trying to decrypt
the table's encrypted BYTEA column into a decrypted VARCHAR for return):

CREATE OR REPLACE FUNCTION selectFromCrypto(INTEGER)
RETURNS VARCHAR
AS '
DECLARE
crypto_cursor CURSOR (input INTEGER) FOR SELECT
encode(decrypt(crypted_content, decode(''password''::text,
''escape''::text), ''aes''::text), ''escape''::text) FROM crypto WHERE id =
input;
crypto crypto.crypted_content%TYPE;
tid ALIAS FOR $1;

BEGIN
OPEN crypto_cursor( tid );
LOOP
FETCH crypto_cursor INTO crypto;
EXIT WHEN NOT FOUND;
END LOOP;
CLOSE crypto_cursor;
RETURN ( encode(crypto, ''escape''::text)::VARCHAR );
END;
' LANGUAGE 'plpgsql';

5. When I use the above function (in the tool, psql) to get the decrypted
contents for ID = 2, it says I get 1 row returned, but the contents are
blank:

# select * from selectFromCrypto(1);
selectfromcrypto1
-------------------

(1 row)

Notice the blank row returned... So what am I doing wrong?

I suspect it has something to do with
converting/encoding/decoding/decrypting the BYTEA column for return... but
what is the problem with the above Function?

I'm using PostgreSQL 7.4.6, on a Fedora Core 1 system.


Best regards and thank you very much in advance,
Michael Moran

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Jim Buttafuoco 2005-03-24 17:14:44 Re: Funtions + plpgsql + contrib/pgcrypto = ??
Previous Message Edmund Bacon 2005-03-24 16:13:38 Re: Self-referencing table question