Topic: PostgreSQL warnings in log

Hey,

I get periodical warnings in my PostgreSQL logfile:

postgres[98505]: [2-1] WARNING:  nonstandard use of \\ in a string literal at character 137
postgres[98505]: [2-2] HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.

Running PunBB 1.2.17 and PostgreSQL 8.3.

Any ideas?

Yanks.

Re: PostgreSQL warnings in log

Well, Googling the error message turned up some interesting results, but nothing that sheds any light on why PHP would be escaping something improperly. If you have any ideas or more details, that would be great.

Re: PostgreSQL warnings in log

Seems to have something to do with PunBB using backslashes to escape stuff:

escape_string_warning (boolean)

    When on, a warning is issued if a backslash (\) appears in an ordinary string literal ('...' syntax) and standard_conforming_strings is off. The default is on.

    Applications that wish to use backslash as escape should be modified to use escape string syntax (E'...'), because the default behavior of ordinary strings will change in a future release for SQL compatibility. This variable can be enabled to help detect applications that will break.

From: http://postgresql.mirrors-r-us.net/docs … tible.html

So instead of \\ php should be using E to escape backslashes?

4

Re: PostgreSQL warnings in log

If I remember correctly, it's due to some mod using addslashes instead of db->escape. PgSQL is very strict by default. Do you have the PM mod installed, perchance?

5 (edited by Ghirai 2008-04-25 13:01)

Re: PostgreSQL warnings in log

I have User Management mod (which i haven't used yet) and mod_easy_bbcode.

I did a couple minor modification (like showing thread title in a particular forum when viewing the index, etc), but none add any extra stuff to the db.

Any way i could check who the culprit is?

EDIT: I grep-ed for addslashes, only place i found it was in header.php, javascript "function process_form(the_form)", which doesn't seem to have anything to do with the db.

Re: PostgreSQL warnings in log

Ghirai: Yeah, I know why it would be happening, the question in that case is why PHP is not using the alternative syntax.

7 (edited by Ghirai 2008-04-27 00:35)

Re: PostgreSQL warnings in log

Here's phpinfo(), if you think it helps figuring things out:
<removed>

EDIT: Also if you need any additional info let me know hmm

Re: PostgreSQL warnings in log

Smartys, any indeas?

Seems everything is working, but the warnings are annoying... sad

Re: PostgreSQL warnings in log

Nope, I'm not a PostgreSQL expert and I really have no idea wink

Re: PostgreSQL warnings in log

Well, to be clear, I can maybe deal with the issue if you can figure out what query/queries is/are actually causing the warnings.

11

Re: PostgreSQL warnings in log

Alright, i'll come back when i find new info big_smile

So far warning is triggered periodically, on vacuum it seems.

12

Re: PostgreSQL warnings in log

May be useful in regards to debugging:

http://archives.postgresql.org/pgsql-ge … g01385.php

13

Re: PostgreSQL warnings in log

Thanks.

Keeping an eye on the logs...

14 (edited by Ghirai 2008-04-29 20:16)

Re: PostgreSQL warnings in log

Alright, here we go.

I posted  'test\123':

STATEMENT:  INSERT INTO node_tb_posts (poster, poster_id, poster_ip, message, hide_smilies, posted, topic_id) VALUES ('Ghirai',2, '<ip-removed>', 'test\\123', '0', 1209499955, 76)

This also happens on edits.


Any indeas? smile
Thanks.

Re: PostgreSQL warnings in log

This isn't making much sense to me. Either backslashes can be used as an escape character or they can't. If they can't, we need another escape character, since we need them to prevent SQL injects.
Unless someone can shed more light on this (and provide a solution other than "add E before every time you escape something properly", which isn't cross database compatible), I'm afraid there's nothing much we can do.

16

Re: PostgreSQL warnings in log

This is what the PgSQL site says on the topic:

Add E'' syntax so eventually ordinary strings can treat backslashes literally

Currently PostgreSQL processes a backslash in a string literal as introducing a special escape sequence, e.g. \n or \010. While this allows easy entry of special values, it is nonstandard and makes porting of applications from other databases more difficult. For this reason, the PostgreSQL project is planning to remove the special meaning of backslashes in strings. For backward compatibility and for users who want special backslash processing, a new string syntax has been created. This new string syntax is formed by writing an E immediately preceding the single quote that starts the string, e.g. E'hi\n'. While this release does not change the handling of backslashes in strings, it does add new configuration parameters to help users migrate applications for future releases:

    *

      standard_conforming_strings ? does this release treat backslashes literally in ordinary strings?
    *

      escape_string_warning ? warn about backslashes in ordinary (non-E) strings

The standard_conforming_strings value is read-only. Applications can retrieve the value to know how backslashes are processed. (Presence of the parameter can also be taken as an indication that E'' string syntax is supported.) In a future release, standard_conforming_strings will be true, meaning backslashes will be treated literally in non-E strings. To prepare for this change, use E'' strings in places that need special backslash processing, and turn on escape_string_warning to find additional strings that need to be converted to use E''. Also, use two single-quotes ('') to embed a literal single-quote in a string, rather than the PostgreSQL-supported syntax of backslash single-quote (\'). The former is standards-conforming and does not require the use of the E'' string syntax. You can also use the $$ string syntax, which does not treat backslashes specially.

Re: PostgreSQL warnings in log

And again, that doesn't make sense. I don't know about the whole "nonstandard" thing, I know MySQL and SQLite do it. However, it doesn't address the issue of how to escape a character if there are no longer escape sequences. For example:
foo="this is a string"
lets say that string is created by user input. Without an escape character, how do I prevent this?
foo="this is a string" or 1=1 or foo=""

18

Re: PostgreSQL warnings in log

Don't make any gorm to me either. big_smile That's why I posted it. Thought you may be able to make head or tail of it. big_smile

As it is, the logged warning is just that. Everything works as expected, and the logging of that specific message can be disabled if required, so it's not any type of priority problem yet.