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 _______________________________________________ Dspam-devel mailing list Dspam-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspam-devel
