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: Open
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-24 18:47
Message:
Hello Stéphane,
do you get that error with DSPAM GIT?
Could you please run this and post the result:
SELECT f.attnum AS number, f.attname AS name, f.attnum, f.attnotnull AS
notnull, pg_catalog.format_type(f.atttypid,f.atttypmod) AS type, CASE WHEN
p.contype = 'p' THEN 't' ELSE 'f' END AS primarykey, CASE WHEN p.contype =
'u' THEN 't' ELSE 'f' END AS uniquekey, CASE WHEN p.contype = 'f' THEN
g.relname END AS foreignkey, CASE WHEN p.contype = 'f' THEN p.confkey END
AS foreignkey_fieldnum, CASE WHEN p.contype = 'f' THEN g.relname END AS
foreignkey, CASE WHEN p.contype = 'f' THEN p.conkey END AS
foreignkey_connnum, CASE WHEN f.atthasdef = 't' THEN d.adsrc END AS default
FROM pg_attribute f JOIN pg_class c ON c.oid = f.attrelid JOIN pg_type t ON
t.oid = f.atttypid LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum
= f.attnum LEFT JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN
pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY ( p.conkey ) LEFT
JOIN pg_class AS g ON p.confrelid = g.oid WHERE c.relkind = 'r'::char AND
n.nspname = 'public' AND c.relname = 'dspam_signature_data' AND f.attnum >
0 ORDER BY number;
And please post the output of the following command:
SHOW ALL;
----------------------------------------------------------------------
Comment By: Stéphane Berthelot (sberthelot)
Date: 2010-08-24 18:05
Message:
Hello Stevan,
I still have the issue, I'm investigating to see what kind of postgresql
setting I may have that cause trouble ...
Excerpt from my sql.errors :
[08/24/2010 17:58:59] 8347: ERROR: invalid byte sequence for encoding
"SQL_ASCII": 0x00
HINT: This error can also happen if the byte sequence does not match the
encoding expected by the server, which is controlled by "client_encoding".
: INSERT INTO dspam_signature_data (uid,signature,length,created_on,data)
VALUES
(8,E'8,4c73ec4383477704710225',864,CURRENT_DATE,E'\331\235\237\210]\237\351x\002\000\000\000\303\351\...@\234\230+\302\001\000\000\000\237\3133\326\334\035\021d\002\000\000\000\245\271t\271\254\025\\\351\003\000\000\000+\203u\232\352\337\230\300\001\000\000\000%\031lzs&\377\004\001\000\000\000:}G\235\177\271L\267\001\000\000\000\332\2337\275\210\035V\352\001\000\000\000\215\227\032\247\204\242\332\217\001\000\000\000\206\227K\266B\014\035\015\001\000\000\000\332\2337\275\370\0016\351\001\000\000\000\220\231\356]\333A\372\255\001\000\000\000\2337\2758M.y\214\001\000\000\000\277\344\013\375\245c1a\002\000\000\000\220:\026\273#nF9\002\000\000\000H\252pm\274\362\364\322\001\000\000\000\257\204\364\256\006\316\202\224\001\000\000\000\3664\235&n\235\370\022\002\000\000\000\275\370\0016Y\2502\002\001\000\000\000\345d-&S
\201J\001\000\000\000\035VZ\250\362DS\370\001\000\000\000m\3664\235&\256;v\002\000\000\000&\016S\375\375\0055\342\002\000\000\000,s\337<\006\305(l\002\000\000\000\236\320\375\275\016p\333\237\001\000\000\000\275\270\353\036\357D\235\023\001\000\000\0004\235&nm\031S\314\002\000\000\000m\3664\235&\256\274\020\002\000\000\0007[\222\322\226\021\263\012\002\000\000\000E\244\021\344\221>R\241\001\000\000\000\034\341\253\240a\023\325\252\001\000\000\000aY\2564\015\251,\344\002\000\000\000\305(|\304\246pAR\002\000\000\0000\271\311\255\034H\365\227\002\000\000\000\264\332\375\206}\240\031\211\001\000\000\000O\235\376\005\365\364?d\002\000\000\000\300\332\2337\275\210<)\001\000\000\000\300\332\2337\275X\356+\001\000\000\0004\235&\256|V\334\261\002\000\000\000_%\212\325\022\000\356f\001\000\000\000\300\332\2337\275H\252
\002\000\000\000\256|V\334!RU\032\002\000\000\000\257\204\364\256\006\316\205\344\001\000\000\000\031\255\362)\031*v6\001\000\000\000\245c1\241\305\325\311\316\002\000\000\000m\3664\235&~n\022\002\000\000\000X.\235x\214U\345\326\001\000\000\000hVuu\012\210I\221\001\000\000\000\351NV-\374`\205\241\002\000\000\000m\3664\235&\376\304g\002\000\000\000\275X\356{\335\354\212w\001\000\000\0004\235&\336'']7\313\002\000\000\000s\264\300\270\324\327YI\001\000\000\000\203\207\203\350\016iI\213\001\000\000\000\2337\275X.\235x\214\001\000\000\0007\275\210<y\035[\354\001\000\000\000H\273\216n5v\023\015\001\000\000\000\307\2356\371\001\273j7\001\000\000\000\000\000\000\300\001\273\265q\001\000\000\000\3664\235&~O\235\016\002\000\000\000C\361u\034\222$\326\337\001\000\000\000\347h\276\314\000\262`\300\001\000\000\000\000\000\000\320u\307\235f\001\000\000\000&\256\274\260yA\215\227\002\000\000\000Q5\023\232\230\313}b\001\000\000\0007\275H\252p\035[\354\001\000\000\000\322\243\260\350C\361u\\\001\000\000\000\214e2ak\363Ow\001\000\000\000\235&\276\351NV-\\\002\000\000\000\3664\235&\016S\375\015\002\000\000\000\370y\212\307.''^''\001\000\000\000\245Ff\321,0H\317\001\000\000\000')
and in my pgsql server log (about the same message)
Aug 24 17:58:59 darkstar postgres[8349]: [2-1] 127.0.0.1 dspam dspam
4c73ebdc.209d 0 ERROR: invalid byte sequence for encoding "SQL_ASCII":
0x00
Aug 24 17:58:59 darkstar postgres[8349]: [2-2] 127.0.0.1 dspam dspam
4c73ebdc.209d 0 HINT: This error can also happen if the byte sequence does
not match the encoding expected by the server, which is controlled by
"client_encoding".
Aug 24 17:58:59 darkstar postgres[8349]: [2-3] 127.0.0.1 dspam dspam
4c73ebdc.209d 0 STATEMENT: INSERT INTO dspam_signature_data
(uid,signature,length,created_on,data) VALUES
(8,E'8,4c73ec4383477704710225',864,CURRENT_DATE,E'\331\235\237\210]\237\351x\002\000\000\000\303\351\...@\234\230+\302\001\000\000\000\237\3133\326\334\035\021d\002\000\000\000\245\271t\271\254\025\\\351\003\000\000\000+\203u\232\352\337\230\300\001\000\000\000%\031lzs&\377\004\001\000\000\000:}G\235\177\271L\267\001\000\000\000\332\2337\275\210\035V\352\001\000\000\000\215\227\032\247\204\242\332\217\001\000\000\000\206\227K\266B\014\035\015\001\000\000\000\332\2337\275\370\0016\351\001\000\000\000\220\231\356]\333A\372\255\001\000\000\000\2337\2758M.y\214\001\000\000\000\277\344\013\375\245c1a\002\000\000\000\220:\026\273#nF9\002\000\000\000H\252pm\274\362\364\322\001\000\000\000\257\204\364\256\006\316\202\224\001\000\000\000\3664\235&n\235\370\022\002\000\000\000\275\370\0016Y\2502\002\001\000\000\000\345d-&S
(...)
I am trying to figure out how db encoding or initialization may cause this
error. I'll post a comment again as soon as I have something useful
----------------------------------------------------------------------
Comment By: Stevan Bajic (sbajic)
Date: 2010-08-22 20:09
Message:
Hallo Stéphane, had you the chance to test latest GIT and verify if the
issue still exists there? Since you are the only one reporting that issue
and PostgreSQL is widely used I am going to set the status to pending.
Please respond in the next two weeks or the Sourceforge bug tracking system
will automatically close this bug report.
----------------------------------------------------------------------
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: Stevan Bajic (sbajic)
Date: 2010-08-05 12:24
Message:
Can you try again but use this time DSPAM Git version?
btw: You are the first one to report to have an issue with the PostgreSQL
driver inserting signature data. I just tried yesterday with a stock
PostgreSQL 9.0Beta2 and Tom Hendrikx tried this morning with 8.4.4. We both
had no issues inserting signatures.
Anyway... you have an error and this is all what counts. So please post me
that error. Telling me that you have an error is fine and dandy but I need
to error code/text (copy it from your console and paste back here). This
more helpful to me then just the statement that you can not insert
signatures.
----------------------------------------------------------------------
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.
----------------------------------------------------------------------
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.
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=1126467&aid=3023888&group_id=250683
------------------------------------------------------------------------------
Sell apps to millions through the Intel(R) Atom(Tm) Developer Program
Be part of this innovative community and reach millions of netbook users
worldwide. Take advantage of special opportunities to increase revenue and
speed time-to-market. Join now, and jumpstart your future.
http://p.sf.net/sfu/intel-atom-d2d
_______________________________________________
Dspam-devel mailing list
Dspam-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/dspam-devel
