C-style backslashe escapes in string constants cause errors or warnings in postgresql

I found this one today. I'm on Linux and version 8.2, if I'm correct, of PostgreSQL.

It looks like it comes from the order updating the variables as an array doing so in the cache... If a variable includes new lines then the strings will include \015\012. I did not otherwise see \\ by itself.

Any idea where the UPDATE could happen? I have not been able to find it!

Notice: pg_query() [function.pg-query]: WARNING: nonstandard use of \\ in a string literal LINE 1: UPDATE cache SET data = 'a:157:{s:18:"node_options_forum";a:... ^ HINT: Use the escape string syntax for backslashes, e.g., E'\\'. in [...]/includes/database.pgsql.inc on line 139

Thank you.
Alexis Wilke

Another note, the variables array is being cached and includes a special value (the string literal) from the contact form. That's where I get the newline from. (and thus the backslashes)

Thank you.
Alexis

I now have a fix for this problem. It was just cluttering my database way too much and preventing me from seeing other problems...

If you want the fix, I posted it here: http://new.m2osw.com/drupal_database_psql_warnings_fix

Feel free to post a comment here or on that page if you have any question.

Thank you.
Alexis Wilke

Note: I think it is quite unlikely that they will make such a change for postgresql in D6. That's one reason why I'm not posting it here. I have done that before with other problems and got no answers anyway. This can be viewed as a rather big change...

The SQL standard requires literal treatment of backslashes. Can you please tell Postgresql to act according to the SQL standard by setting the configuration variable "standard_conforming_strings" to ON?

Sorry, it was not my intend to change status.

As we have PostgreSQL 7.4 or higher as a requirement, we need to investigate if we are passing illegal strings to those versions atm.

Haine, uffner, the first poster, included a link to a page from PostgreSQL. They clearly say that the strings may include backslashes, but in that case you must prepend a capital E to the string. They also say that this is in the SQL standard.

@alexiswilke,

AFAIK, we normally just store the byte values (10), not their corresponding escape sequences (\n). Somehow there's a conversion between byte value and escape sequence.

The cache_set example provided a great hint. The conversion between byte value and escape sequence is done by db_encode_blob, a wrapper for pg_escape_bytea as certain byte values MUST be escaped as octals.

Can you please confirm that %b use is the sole source of these errors? (I could not find enough data on pg_escape_string, nor can I test atm for lack of PostgreSQL).

If so, a simple patch to http://api.drupal.org/api/function/db_encode_blob/6 would fix the issue for PostgreSQL 8.x with standard_confirming strings set to ON.

Heine,

Okay, I tried several of the functions and I can report that the problem is pg_escape_bytea(). This is found in db_encode_blob(). And you have the opposite in db_decode_blob(), obviously. So fixing the output of that one call would help...

The pg_escape_string() does not transform the \n characters. I also have a function call, utf8_encode(), that fixes problems with non-UTF-8 strings and that too does not affect \n characters.

So I guess a good fix would be this:

function db_encode_blob($data) {
  return "E'". pg_escape_bytea($data) ."'";
}

Simply add an 'E' in front of the string in that one function. What do you think?

Thank you.
Alexis Wilke

I work in a government portal and this problem causes the broken of my postgres database.
The log of the db has 56M (in just 2 days) with this error only.

I had to make a cron that restart my database once a day!

This issue has one year old, when the drupal team will fix it?

As outlined in this blog post of mine, this issue goes away when you set standard_conforming_strings = on in your postgresql.conf :)