Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-interfaces-owner+M7037=pgsql-interfaces-ark@postgresql.org>)
 id 1WSfnY-0003ig-RR
 for pgsql-interfaces@arkaria.postgresql.org; Wed, 26 Mar 2014 04:50:45 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-interfaces-owner+M7037=pgsql-interfaces-ark@postgresql.org>)
 id 1WSfnX-0002nX-I2
 for pgsql-interfaces@arkaria.postgresql.org; Wed, 26 Mar 2014 04:50:43 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <pgsql@j-davis.com>) id 1WSfnW-0002nQ-Sh
 for pgsql-interfaces@postgresql.org; Wed, 26 Mar 2014 04:50:42 +0000
Received: from mail-ob0-f179.google.com ([209.85.214.179])
 by magus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <pgsql@j-davis.com>) id 1WSfnS-00059g-E0
 for pgsql-interfaces@postgresql.org; Wed, 26 Mar 2014 04:50:42 +0000
Received: by mail-ob0-f179.google.com with SMTP id va2so1863960obc.10
 for <pgsql-interfaces@postgresql.org>; Tue, 25 Mar 2014 21:50:37 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
 d=1e100.net; s=20130820;
 h=x-gm-message-state:message-id:subject:from:to:cc:date:in-reply-to
 :references:content-type:mime-version:content-transfer-encoding;
 bh=iFhmkcWUDP14i3/xEu01pPt26JxeeX7yeutQcukM/OY=;
 b=cHDOxHtNIZqq5hD7L7oQFNNvc9vmNHT7IkDASNvhRw3F5J+fU8Q09tccsNnlM0V2Lt
 QXKmdT51K0E1C+b2181yn0VL3FG2suSHRTpZZE6NitvMcGlYPczVsVIuAn4SrndCxjV9
 FlleT6hxSDFVOx9fkwUYufUe62wzUJb/WODV2X1Go5OlFmcyPk9yfdDGlzlSgtocpgKp
 9uVuXBVFj0z/l8ZWuMLlkeUhfGp1K13so/7CLr2XglBI4Xq7Ms1seH1PNJwzBkuU4GKM
 Ud4JQiCzDSVLzR5MGYTSqhpcYkFveWUSNKWhBNq7NcNw9CMWu101TOutfLKFr4xZqhQ6
 A6mA==
X-Gm-Message-State: 
 ALoCoQkYFc490UVGqgqWLqRUXUPXBSxI9uex9JovzQuBYBjMU465PEfpT5cMKjBvOUxWIOCkS9aj
X-Received: by 10.182.22.227 with SMTP id h3mr24401690obf.36.1395807590480;
 Tue, 25 Mar 2014 21:19:50 -0700 (PDT)
Received: from [192.168.0.106] (c-69-181-249-16.hsd1.ca.comcast.net.
 [69.181.249.16])
 by mx.google.com with ESMTPSA id m7sm2169314obo.7.2014.03.25.21.19.49
 for <multiple recipients>
 (version=SSLv3 cipher=RC4-SHA bits=128/128);
 Tue, 25 Mar 2014 21:19:49 -0700 (PDT)
Message-ID: <1395807598.2224.11.camel@jdavis>
Subject: Re: PQunescapebytea not reverse of PQescapebytea?
From: Jeff Davis <pgsql@j-davis.com>
To: Florian Weimer <fw@deneb.enyo.de>
Cc: Karthik Segpi <karthik.segpi@gmail.com>, pgsql-interfaces@postgresql.org
Date: Tue, 25 Mar 2014 21:19:58 -0700
In-Reply-To: <87bnx20w88.fsf@mid.deneb.enyo.de>
References: 
 <CACD7L9GCLsM=wEwwH707sN19dMkMHwHh+ffVmLQdsFXNR0UJ6Q@mail.gmail.com>
 <87bnx20w88.fsf@mid.deneb.enyo.de>
Content-Type: text/plain; charset="UTF-8"
X-Mailer: Evolution 3.8.4-0ubuntu1 
Mime-Version: 1.0
Content-Transfer-Encoding: 8bit
X-Pg-Spam-Score: -2.6 (--)
List-Archive: <http://archives.postgresql.org/pgsql-interfaces>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-interfaces.postgresql.org>
List-Owner: <mailto:pgsql-interfaces-owner@postgresql.org>
List-Post: <mailto:pgsql-interfaces@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-interfaces>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-interfaces>
X-Mailing-List: pgsql-interfaces
Precedence: bulk
Sender: pgsql-interfaces-owner@postgresql.org

On Wed, 2014-03-19 at 21:28 +0100, Florian Weimer wrote:
> * Karthik Segpi:
> 
> > I have a 'bytea' column in the database, onto which my custom C application
> > is inserting encrypted data. Before inserting, I am calling
> > 'PQescapebytea()' to escape the ciphertext. However, after SELECT, the data
> > needs to be 'un-escaped' before attempting to decrypt. I am trying to
> > 'un-escape' using 'PQunescapebytea'. However, I am finding that
> > 'PQunescapebytea' is not  exact inverse of 'PQescapebytea'. I saw
> > documentation and posts in the mailing lists alluding to this as well. As a
> > result, the decryption always fails.
> 
> Can you show us some example data that shows the inconsistency?
> PQunescapebytea should give you back the blob you passed to
> PQescapebytea, but the same blob can have different BYTEA
> encodings—not everyone uses the \x hexadecimal encoding.

Example:

  size_t len1, len2;
  char *str = "\\\\123";

  printf("%s\n", str);
  printf("%s\n", PQescapeBytea(str, strlen(str), &len1));
  printf("%s\n", PQunescapeBytea(
                                 PQescapeBytea(str, strlen(str), &len1),
                                 &len2));

The reason for this is that PQescapeBytea is designed to escape it to be
passed into the server via a SQL string (adding two levels of escaping,
one for the sql string and one for bytea); whereas PQunescapeBytea is
designed to unescape a result coming back from the server (which only
has one level of escaping to undo: the bytea escaping).

To be more consistent, PQ[un]escapeBytea might only [un]escape the bytea
portion, making them true inverses. That would mean that to put a bytea
in a sql string, you'd need to do something like:
  PQescapeString(PQescapeBytea(str, ...))
rather than just:
  PQescapeBytea(str, ...)

Both methods are a bit error-prone. The first is error-prone because you
have to remember to do both; the second because it's inconsistent.

The best thing to do is use parameterized input to reduce the room for
confusion. Specify bytea parameters as binary format, and you don't need
to escape them at all on input. When reading them back out of the
server, use binary format if you can; and if not, then you'll need to
use PQunescapeBytea.

Regards,
	Jeff Davis




-- 
Sent via pgsql-interfaces mailing list (pgsql-interfaces@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces