Here's the patch..
Robert
Robert Fleming wrote:
<snip>
Anyway, I would like to point out this wiki note:
http://www.dbmail.org/dokuwiki/doku.php?id=unicode_postgresql_database
to whomever is working on bug 218:
http://www.dbmail.org/mantis/view.php?id=218
In response to the last note there, I would say that this issue *will*
persist, because basically dbmail is trying to submit arbitrary octet
sequences as values for columns of type "text", and Postgres will
always try to interpret the input according to the defined encoding
for that database. I have patches to fix this if there is interest.
The patches change the column type to "bytea", which tells Postgres to
not try to decode the text, but just store it.
diff -ru ../dbmail-2.0.7/db.c ./db.c
--- ../dbmail-2.0.7/db.c 2005-08-19 07:30:42.000000000 -0700
+++ ./db.c 2005-10-29 16:47:45.000000000 -0700
@@ -1119,7 +1119,8 @@
blocktype_t is_header)
{
char *escaped_query = NULL;
- unsigned maxesclen = (READ_BLOCK_SIZE + 1) * 2 + DEF_QUERYSIZE;
+ // worst case: all bytes must be escaped; PostgreSQL bytea e.g. "\\176"
+ const unsigned maxesclen = (READ_BLOCK_SIZE + 1) * 5 + DEF_QUERYSIZE;
unsigned startlen = 0;
unsigned esclen = 0;
@@ -1884,7 +1885,7 @@
}
snprintf(query, DEF_QUERYSIZE,
- "SELECT messageblk FROM dbmail_messageblks "
+ "SELECT encode(messageblk, 'escape') AS messageblk FROM dbmail_messageblks "
"WHERE physmessage_id='%llu' "
"ORDER BY messageblk_idnr ASC", physmessage_id);
trace(TRACE_DEBUG, "%s,%s: executing query [%s]",
@@ -3915,7 +3916,7 @@
list_init(hdrlist);
snprintf(query, DEF_QUERYSIZE,
- "SELECT messageblk "
+ "SELECT encode(messageblk, 'escape') AS messageblk "
"FROM dbmail_messageblks blk, dbmail_messages msg "
"WHERE blk.physmessage_id = msg.physmessage_id "
"AND msg.message_idnr = '%llu' "
diff -ru ../dbmail-2.0.7/dbmsgbuf.c ./dbmsgbuf.c
--- ../dbmail-2.0.7/dbmsgbuf.c 2005-08-19 07:30:42.000000000 -0700
+++ ./dbmsgbuf.c 2005-10-27 23:55:25.000000000 -0700
@@ -148,7 +148,7 @@
int db_init_fetch_headers(u64_t msg_idnr)
{
- char *query_template = "SELECT block.messageblk "
+ char *query_template = "SELECT encode(block.messageblk, 'escape') AS messageblk "
"FROM dbmail_messageblks block, dbmail_messages msg "
"WHERE block.physmessage_id = msg.physmessage_id "
"AND dbmail_messageblks.is_header = 1"
@@ -159,7 +159,7 @@
}
int db_init_fetch_message(u64_t msg_idnr)
{
- char *query_template = "SELECT block.messageblk "
+ char *query_template = "SELECT encode(block.messageblk, 'escape') AS messageblk "
"FROM dbmail_messageblks block, dbmail_messages msg "
"WHERE block.physmessage_id = msg.physmessage_id "
"AND msg.message_idnr = '%llu' "
@@ -372,7 +372,7 @@
}
snprintf(query, DEF_QUERYSIZE,
- "SELECT block.messageblk "
+ "SELECT encode(block.messageblk, 'escape') AS messageblk "
"FROM dbmail_messageblks block, dbmail_messages msg "
"WHERE block.physmessage_id = msg.physmessage_id "
"AND msg.message_idnr = '%llu' "
diff -ru ../dbmail-2.0.7/dbsearch.c ./dbsearch.c
--- ../dbmail-2.0.7/dbsearch.c 2005-08-19 07:30:42.000000000 -0700
+++ ./dbsearch.c 2005-10-27 23:57:14.000000000 -0700
@@ -456,7 +456,7 @@
}
snprintf(query, DEF_QUERYSIZE,
- "SELECT block.messageblk "
+ "SELECT encode(block.messageblk, 'escape') AS messageblk "
"FROM dbmail_messageblks block, dbmail_messages msg "
"WHERE block.physmessage_id = msg.physmessage_id "
"AND msg.message_idnr = '%llu' "
diff -ru ../dbmail-2.0.7/pgsql/dbpgsql.c ./pgsql/dbpgsql.c
--- ../dbmail-2.0.7/pgsql/dbpgsql.c 2005-08-19 07:30:42.000000000 -0700
+++ ./pgsql/dbpgsql.c 2005-10-28 00:00:21.000000000 -0700
@@ -240,7 +247,11 @@
unsigned long db_escape_direct(char *to,
const char *from, unsigned long length)
{
- return PQescapeString(to, from, length);
+ size_t to_length;
+ char* to2 = PQescapeBytea(from, length, &to_length);
+ strcpy(to, to2); // FIXME: strncpy, but n would need to be passed in
+ PQfreemem(to2);
+ return strlen(to);
}
int db_do_cleanup(const char **tables, int num_tables)
diff -ru ../dbmail-2.0.7/sql/postgresql/create_tables.pgsql ./sql/postgresql/create_tables.pgsql
--- ../dbmail-2.0.7/sql/postgresql/create_tables.pgsql 2005-09-05 07:52:57.000000000 -0700
+++ ./sql/postgresql/create_tables.pgsql 2005-10-28 00:49:34.000000000 -0700
@@ -132,7 +135,7 @@
messageblk_idnr INT8 DEFAULT nextval('dbmail_messageblk_idnr_seq'),
physmessage_id INT8 REFERENCES dbmail_physmessage(id)
ON DELETE CASCADE,
- messageblk TEXT NOT NULL,
+ messageblk bytea NOT NULL,
blocksize INT8 DEFAULT '0' NOT NULL,
is_header INT2 DEFAULT '0' NOT NULL,
PRIMARY KEY (messageblk_idnr)
