Re: Funtions + plpgsql + contrib/pgcrypto = ?? - Mailing list pgsql-sql

Jim,
I was thinking... In your excellent solution below, we select/decrypt and
return the crypto column based on an input Integer ID. This is good. But
what if we wanted to do the reverse? 
That is, what if I want to select an ID based on input crypto data?
i.e., given my original table data way below:
1. Pass into the function the decrypted string ''22223333BCDE' 
2. The function looks up the ID that matchines the encrypted value of
'22223333BCDE' ...
3. Finally: the matching ID of 2 is returned.
How would that change your solution below?

Thanks again in advance.
-Michael Moran
 _____  

From: Jim Buttafuoco [mailto:jim@contactbda.com]
Sent: Thu 3/24/2005 9:58 AM
To: Moran.Michael; Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto
= ??




I would change the return type to TEXT, I believe your original example had
it as a varchar and I didn't change it. 
Also, I believe that "under the hood" text does equal varchar. 

Glad I could help 
Jim 


---------- Original Message ----------- 
From: "Moran.Michael" <Michael.Moran@IGT.com> 
To: jim@contactbda.com, pgsql-sql@postgresql.org 
Sent: Thu, 24 Mar 2005 09:43:18 -0800 
Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ?? 

> Thank you, Jim. You rock! 
> 
> This worked.... although I'm a bit confused: 
> 
> Your function below says it returns VARCHAR, yet the variable that holds
the 
> contents of my SELECT which we ultimately return is of type TEXT. 
> 
> When I return the TEXT variable, it works. Does this mean TEXT = VARCHAR
in 
> plpgsql Functions? 
> 
> Yet if you cast the TEXT to VARCHAR in your RETURN line below (because, it

> seems logical to try to match the declared return type)... it fails 
> 
> So, this works:    return c; 
> This doesn't:      return c::VARCHAR; 
> 
> I always thought matching my return type to my funtion's RETURN
declaration 
> is logical... but now I know that if want a VARCHAR, I gotta manipulate it

> as a TEXT within my function when using PGCrypto. Any idea why? 
> 
> Thank again, Jim! 
> 
>   _____ 
> 
> From: Jim Buttafuoco [mailto:jim@contactbda.com
<mailto:jim@contactbda.com> ] 
> Sent: Thu 3/24/2005 9:14 AM 
> To: Moran.Michael; pgsql-sql@postgresql.org 
> Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ?? 
> 
> give this a try 
> 
> CREATE OR REPLACE FUNCTION selectFromCrypto(INTEGER) 
> RETURNS VARCHAR 
> AS ' 
> DECLARE 
>    _pid             ALIAS FOR $1; 
>    c          text; 
> 
> BEGIN 
> 
> SELECT decrypt(crypted_content, decode(''password''::text, 
> ''escape''::text), ''aes''::text) into c 
> FROM crypto 
> WHERE pid = _pid; 
> 
>    RETURN c; 
> END; 
> ' LANGUAGE 'plpgsql'; 
> 
> ---------- Original Message ----------- 
> From: "Moran.Michael" <Michael.Moran@IGT.com> 
> To: pgsql-sql@postgresql.org 
> Sent: Thu, 24 Mar 2005 08:41:34 -0800 
> Subject: [SQL] Funtions + plpgsql + contrib/pgcrypto = ?? 
> 
> > 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 
> > 
> > ---------------------------(end of broadcast)---------------------------

> > TIP 4: Don't 'kill -9' the postmaster 
> ------- End of Original Message ------- 
> 
> ---------------------------(end of broadcast)--------------------------- 
> TIP 3: if posting/reading through Usenet, please send an appropriate 
>       subscribe-nomail command to majordomo@postgresql.org so that your 
>       message can get through to the mailing list cleanly 
------- End of Original Message -------