X-Original-To: pgsql-sql-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 48DB59F9F27
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Sun, 16 Jul 2006 22:11:26 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 04703-02 for <pgsql-sql-postgresql.org@postgresql.org>;
	Sun, 16 Jul 2006 22:11:16 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from smtpgate1.pacific.net.sg (smtpgate1.pacific.net.sg
	[203.120.90.31]) by postgresql.org (Postfix) with SMTP id 18EEF9F9840
	for <pgsql-sql@postgresql.org>; Sun, 16 Jul 2006 22:11:16 -0300 (ADT)
Received: (qmail 20798 invoked from network); 17 Jul 2006 01:11:13 -0000
Received: from ppp235.dyn73.pacific.net.sg (HELO ?127.0.0.1?)
	(debraneys@210.24.73.235)
	by smtpgate1.pacific.net.sg with (DHE-RSA-AES256-SHA encrypted) ESMTPA;
	17 Jul 2006 01:11:11 -0000
Message-ID: <44BAE3B0.8060403@debraneys.com>
Date: Mon, 17 Jul 2006 09:11:12 +0800
From: John Tregea <john@debraneys.com>
User-Agent: Thunderbird 1.5.0.4 (Windows/20060516)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Storing encrypted data?
References: <A70AE049E81A6A40879D5DC0AC8C38C9069508ED@venus.infics.com>
In-Reply-To: <A70AE049E81A6A40879D5DC0AC8C38C9069508ED@venus.infics.com>
Content-Type: multipart/alternative;
	boundary="------------050701050608010402000602"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.097 tagged_above=0 required=5 tests=AWL, BAYES_50,
	HTML_40_50, HTML_MESSAGE, OBSCURED_EMAIL
X-Spam-Level: 
X-Archive-Number: 200607/222
X-Sequence-Number: 25796

This is a multi-part message in MIME format.
--------------050701050608010402000602
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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^(TM)LZPÃOG¤u--¨ékTz~6Ëpi^(TM)|O +LÏ°
îX@ûñÙÁl£ðOE...¢53P9×ð"á6kUÆ¢÷ÍìMNÉA.---ä
![^j"Ø|¨\X--ÛÜu?ªÅÜ-ÎáBæºñȦbF>\Z(v/ÞÎ.&ó«[=bný?Kr°óË2U×ZI©âVc°sn=¤Õg«^q0Ã8ý
<Êh?;"¨"?[S(ç+u[£    
--è---='±ú3³?ì¯,Za@LU'T¸oeÖgoeË?PÛ<...H---8ÃbÙÉZ(NÙí>ÇL]í¶²--âOEÿ/Ínz¶å
Õ?Îcµ^$í¿W-^(TM)óà¢ey÷àÞ¬oe¤CðÏjX"ÃGÄ"·º?ë0L'Ù++Æz(<&^{ªìëá/Ç}k9?ÄX1â<²T½Hõ5Ôgÿ§yÈy..._---ú$lA~©î1úëùññðÑÝS(z(gÕþÊ/«¦...8
?¯þ    
 >ü,fEUR--aÈÉ"iG0/00}¹²#À

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



--------------050701050608010402000602
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi, <br>
<br>
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. <br>
<br>
I get an error back saying "invalid byte
sequence for encoding UTF8; oxd733"<br>
<br>
I am using blowfish at 128bit to encrypt the string.<br>
<br>
The encrypted string comes out looking like this<br>
<br>
<font face="Arial" size="-1">Salted__&euml;&plusmn;9r&#8482;LZP&Atilde;OG&curren;u&#8211;&uml;&eacute;kTz~6&Euml;pi&#8482;|O
+L&Iuml;&deg;&#8249;r&deg;&ocirc;9&#8226;m&micro;&#65533;&Atilde;&iexcl;%&#8364;&copy;&otilde;P{:&#8218;&Agrave;&ordf;d<br>
&icirc;X@&ucirc;&ntilde;&Ugrave;&Aacute;l&pound;&eth;&#338;&#8230;&cent;53P9&times;&eth;&#8220;&aacute;6kU&AElig;&cent;&divide;&Iacute;&igrave;MN&Eacute;A&#8226;&#8212;&auml;
![&#710;j&#8221;&Oslash;|&uml;\X&#8211;&Ucirc;&Uuml;u&#65533;&ordf;&Aring;&Uuml;-&Icirc;&aacute;B&aelig;&ordm;&ntilde;&Egrave;&brvbar;bF&gt;\&#381;v/&THORN;&Icirc;.&amp;&oacute;&laquo;[=bn&yacute;&#65533;Kr&deg;&oacute;&Euml;2U&times;ZI&copy;&acirc;Vc&deg;sn=&curren;&Otilde;g&laquo;^q0&Atilde;8&yacute;
&lt;&Ecirc;h&#65533;;&#8221;&uml;"&#65533;[&#352;&ccedil;+u[&pound;&nbsp;&nbsp;&nbsp;
&#8211;&egrave;&#8212;=&#8216;&plusmn;&uacute;3&sup3;&#65533;&Atilde;&not;&macr;,Za@LU&#8216;T&cedil;&#339;&Ouml;g&#339;&Euml;&#127;P&Ucirc;&lt;&#8230;H&#8212;8&Atilde;b&Ugrave;&Eacute;&#381;N&Ugrave;&iacute;&gt;&Ccedil;L]&iacute;&para;&sup2;&#8211;&acirc;&#338;&yuml;/&Iacute;nz&para;&aring;<br>
&Otilde;?&Icirc;c&micro;&#710;$&iacute;&iquest;W-&#8482;&oacute;&agrave;&cent;ey&divide;&agrave;&THORN;&not;&#339;&curren;C&eth;&Iuml;jX&#8221;&Atilde;G&Auml;&#8222;&middot;&ordm;?&euml;0L&#8216;&Ugrave;&#8225;&AElig;&#382;&#8249;&amp;&#710;{&ordf;&igrave;&euml;&aacute;/&Ccedil;}k9?&Auml;X1&acirc;&#8249;&sup2;T&frac12;H&otilde;5&Ocirc;g&yuml;&sect;y&Egrave;y&#8230;_&#8212;&uacute;$lA~&copy;&icirc;1&uacute;&euml;&ugrave;&ntilde;&ntilde;&eth;&Ntilde;&Yacute;&#352;&#382;g&Otilde;&thorn;&Ecirc;/&laquo;&brvbar;&#8230;8
&#65533;&macr;&thorn;&nbsp;&nbsp;&nbsp;
&gt;&uuml;&#8218;&#402;&#8364;&#8211;a&Egrave;&Eacute;&#8222;iG&#8240;}&sup1;&sup2;#&Agrave;</font><br>
<br>
I enclose that in single quotes and send it in an insert statement to
pgSQL. <br>
<br>
The statement has been working, until I got to a string that generated
the above encrypted data, then it broke.<br>
<br>
Alternatively, can anyone suggest an encryption algorithm that will
only return characters that are valid UTF8 encoded?<br>
<br>
Thanks in advance<br>
<br>
John T<br>
<br>
<br>
</body>
</html>

--------------050701050608010402000602--