Обсуждение: 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\2152 | 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
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 -------
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]
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 -------
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]
> 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 -------
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 -------
I figured it out:
Here's how to get an ID from the table below, based on input crypto data
(this essentially is the reverse of Jim's excellent cryto-to-ID solution
below):
--
-- selectFromCrypto.sql
--
-- Purpose: select from crypto table based on input crypto data in VARCHAR
format.
-- Returns the corresponding ID (INTEGER) value for the crypto data.
--
CREATE OR REPLACE FUNCTION selectFromCrypto2(VARCHAR)
RETURNS INTEGER
AS '
DECLARE _crypt_data ALIAS FOR $1; ret INTEGER;
BEGIN SELECT id INTO ret FROM crypto WHERE encode( decrypt(crypted_content,
decode(''password''::text, ''escape''::text), ''aes''::text),
''escape''::text ) = _crypt_data; RETURN ( ret );
END;
' LANGUAGE 'plpgsql';
Thanks, all. This postgresql forum rocks!
Michael Moran
_____
From: Moran.Michael
Sent: Thu 3/24/2005 10:59 AM
To: jim@contactbda.com; pgsql-sql@postgresql.org
Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??
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 <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>
<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 -------
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match