Re: [SQL] How to find entries missing in 2nd table?

2006-07-16 Thread Nate Teller 








I have
received good performance with the following:

 

select c.controller

   from control c

   left
outer join datapack d on d.controller_id = c.controller_id

 where d.controller_id is null

 

Nate
Teller

 

-Original
Message-
From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Aaron Bono
Sent: Thursday, July 13, 2006
11:46 AM
To: Exner, Peter
Cc: Richard Broersma Jr; SQL
Postgresql List
Subject: Re: [SQL] How to find
entries missing in 2nd table?

 

On 7/12/06, Exner, Peter <[EMAIL PROTECTED]> wrote:



Hi,

what about

SELECT controller_id FROM control
WHERE controller_id NOT IN
(SELECT DISTINCT controller_id FROM datapack);




The DISTINCT is not necessary.  I have heard with Oracle that DISTINCT is
a huge performance problem.  Is that true on PostgreSQL also? 

==
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
==

[SQL] Storing encrypted data?

2006-07-16 Thread John Tregea 




Hi, 

Does anyone have experience with storing encrypted data into pgSQL? I
have a pgSQL database which uses UTF8 encoding. I am encrypting plain
text in my (GUI) application and sending it to a field (with data
type 'text') in my database. 

I get an error back saying "invalid byte
sequence for encoding UTF8; oxd733"

I am using blowfish at 128bit to encrypt the string.

The encrypted string comes out looking like this

Salted__ë±9r™LZPÃOG¤u–¨ékTz~6Ëpi™|O
+LÏ°‹r°ô9•mµ�á%€©õP{:‚Àªd
îX@ûñÙÁl£ðŒ…¢53P9×ð“á6kUÆ¢÷ÍìMNÉA•—ä![ˆj”Ø|¨\X–ÛÜu�ªÅÜ-ÎáBæºñȦbF>\Žv/ÞÎ.&ó«[=bný�Kr°óË2U×ZI©âVc°sn=¤Õg«^q0Ã8ý<Êh�;”¨"�[Šç+u[£   
–è—=‘±ú3³�ì¯,[EMAIL PROTECTED]‘T¸œÖgœËPÛ<…H—8ÃbÙÉŽNÙí>ÇL]í¶²–âŒÿ/Ínz¶å
Õ?Îcµˆ$í¿W-™óà¢ey÷àÞ¬œ¤CðÏjX”ÃGÄ„·º?ë0L‘هƞ‹&ˆ{ªìëá/Ç}k9?ÄX1⋲T½Hõ5Ôgÿ§yÈy…_—ú$lA~©î1úëùññðÑÝŠžgÕþÊ/«¦…8�¯þ   
>ü‚ƒ€–aÈÉ„iG‰}¹²#À

I enclose that in single quotes and send it in an insert statement to
pgSQL. 

The statement has been working, until I got to a string that generated
the above encrypted data, then it broke.

Alternatively, can anyone suggest an encryption algorithm that will
only return characters that are valid UTF8 encoded?

Thanks in advance

John T

Re: [SQL] Storing encrypted data?

2006-07-16 Thread Michael Fuhr 
On Mon, Jul 17, 2006 at 09:11:12AM +0800, John Tregea wrote:
> Does anyone have experience with storing encrypted data into pgSQL? I 
> have a pgSQL database which uses UTF8 encoding. I am encrypting plain 
> text in my (GUI) application and sending it to a field (with data type 
> 'text') in my database.
> 
> I get an error back saying "invalid byte sequence for encoding UTF8; oxd733"

Use bytea instead of text for binary data; alternatively, base64
encode ("armor") the encrypted data and store it as text.  If you
insert binary data then you'll need to escape non-printable characters
or use an API that can pass binary data to the backend (e.g., libpq's
PQexecParams() or some language's interface to that function).  Some
APIs might automatically escape data if you use placeholders; check
your API's documentation.

http://www.postgresql.org/docs/8.1/interactive/datatype-binary.html
http://www.postgresql.org/docs/8.1/interactive/libpq-exec.html

-- 
Michael Fuhr

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] Storing encrypted data?

2006-07-16 Thread John Tregea 

Hi Michael,

Thanks for your advice. I was looking at the bytea data type in the 
PostgreSQL book I bought (Korry and Susan Douglas, second edition). I 
was concerned that if I have to escape certain characters like the 
single quote or the backslash, how do I guarantee that the escaped 
version does not already appear in the encrypted string?


Should I use the octal value to escape the single quote (\047) and 
backslash (\\134)?


Those character sequences are extremely unlikely to occur in an 
encrypted string.


Is the the right approach ?

Also... I note that I cannot change the data type of my field from text 
to bytea (I am using PGADMIN III). Do you know why?


Regards

John T

Michael Fuhr wrote:

On Mon, Jul 17, 2006 at 09:11:12AM +0800, John Tregea wrote:
  
Does anyone have experience with storing encrypted data into pgSQL? I 
have a pgSQL database which uses UTF8 encoding. I am encrypting plain 
text in my (GUI) application and sending it to a field (with data type 
'text') in my database.


I get an error back saying "invalid byte sequence for encoding UTF8; oxd733"



Use bytea instead of text for binary data; alternatively, base64
encode ("armor") the encrypted data and store it as text.  If you
insert binary data then you'll need to escape non-printable characters
or use an API that can pass binary data to the backend (e.g., libpq's
PQexecParams() or some language's interface to that function).  Some
APIs might automatically escape data if you use placeholders; check
your API's documentation.

http://www.postgresql.org/docs/8.1/interactive/datatype-binary.html
http://www.postgresql.org/docs/8.1/interactive/libpq-exec.html

  


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly