Re: [ dspam-Bug Tracker-3023888 ] Wrong escaping of bytea (signature data) with postgresql 8.4

On 22 Aug 2010, at 20:09, SourceForge.net wrote:

> Bug Tracker item #3023888, was opened at 2010-07-01 18:19
> Message generated for change (Comment added) made by sbajic
> You can respond by visiting: 
> https://sourceforge.net/tracker/?func=detail&atid=1126467&aid=3023888&group_id=250683
> 
> Please note that this message will contain a full copy of the comment thread,
> including the initial issue submission, for this request,
> not just the latest update.
> Category: daemon
> Group: v3.9.0
>> Status: Pending
> Resolution: None
> Priority: 5
> Private: No
> Submitted By: Stéphane Berthelot (sberthelot)
> Assigned to: Stevan Bajic (sbajic)
> Summary: Wrong escaping of bytea (signature data) with postgresql 8.4
> 
> Initial Comment:
> Escaping of insertion has been modified in 3.9.0 in two ways :
> - using PQescapeByteaConn (instead of PQescapeBytea)
> - adding E'' syntax in INSERT
> 
> But this encodes twice the data and then doesn't work on fresh PostgreSQL 8.4.2 with or without standard_conforming_string in either UTF8 or SQL_ASCII database encoding.
> 
> Removing the E'' syntax (since the string is already correctly encoded now) makes signature insertion work again
> 
> In the git code, it may be necessary to add the if construct in this patch to check about postgresql version (but <=7.4 seem not supported anymore officially by the postgres team now ...)

(...)

> ----------------------------------------------------------------------
> 
> Comment By: Stevan Bajic (sbajic)
> Date: 2010-08-05 21:08
> 
> Message:
>> The "correct" way to fix is certainly to replace queries containing
> bytea
>> (that use string copy functions) by stored procedures instead. What do
>> you think about it ?
>> 
> Hmm... so you would take a stream of data and transform it with a
> procedure on the server to fit into a normal string? Something like the
> current commands in 3.9.1 of DSPAM are doing it? That could work if you
> query inside DSPAM data from PostgreSQL but how do you want to transmit the
> other way around? I mean you have a stream of binary data and you want to
> push it to PostgreSQL. How can you assure that the procedure will see the
> data the way it should see it? And why do you think that a own crafted
> procedure should be better/faster than using
> PQescapeByteaConn/PQescapeBytea?
> 
> 
>> If ok I'll try to prepare a patch in this direction and
>> test it.
>> 
> Please take the time and test DSPAM Git and tell me if it is working there
> or not?

(...)

> ----------------------------------------------------------------------
> 
> Comment By: Stéphane Berthelot (sberthelot)
> Date: 2010-08-05 12:00
> 
> Message:
> I know it's certainly not the failsafe way to fix the bug ...
> I was referring to
> http://www.postgresql.org/docs/8.4/static/datatype-binary.html (between
> table 8-7 and table 8-8)
> The pgsql doc says that the bytea are to be encoded twice since they are
> decoded first by the query analyzer then by the bytea processor.
> 
> Removing the E'' syntax was the quickiest way for me to "fix it" (since
> with stock dspam 3.9.0 and stock postgresql 8.4 the signature data doesn't
> get inserted and return an encoding error)
> 
> I agree with you on the syntax but it doesn't explain why it doesn't work
> with E'' and start working when I remove it ...
> 
> The "correct" way to fix is certainly to replace queries containing bytea
> (that use string copy functions) by stored procedures instead. What do you
> think about it ? If ok I'll try to prepare a patch in this direction and
> test it.

This is almost certainly due to the user having changed the PostgreSQL setting 'standard_conforming_strings' to 'on'. Admittedly that would be the preferable way to interpret strings, but it's not the default (compatibility reasons mostly).

dspam=> SHOW standard_conforming_strings ;
 standard_conforming_strings 
-----------------------------
 off
(1 row)


> ----------------------------------------------------------------------
> 
> Comment By: Stevan Bajic (sbajic)
> Date: 2010-08-04 23:38
> 
> Message:
>> But this encodes twice the data
>> 
> This is simply not true. The 'E' syntax is telling PostgreSQL that the
> data is not a string literal but a escaped string. That's all.
> 
> ----------------------------------------------------------------------


That's not true either. The E'' syntax is to denote non-standard-conforming strings, meaning they don't conform to the SQL standard and contain backslash escapes (like \' instead of '').

I don't think signature data is in fact meaningful text, is it? If I understand correctly it's actually a binary string, in which case bytea would indeed be the more appropriate data-format to use.

And Stevan is correct that you don't need any stored procedures for that. Bytea is supported in versions <= 7.4 too, for those who still use that.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:1155,4c72105610404812612189!



------------------------------------------------------------------------------
This SF.net email is sponsored by 

Make an app they can't live without
Enter the BlackBerry Developer Challenge
http://p.sf.net/sfu/RIM-dev2dev