[4858] [postgreSQL]Queries not executed during install

[emanuele]

When installing a clean SMF using postgreSQL, I got errors on all these queries:

Code Select Expand

INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('New Year\'s', '0004-01-01');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('Christmas', '0004-12-25');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('Valentine\'s Day', '0004-02-14');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('St. Patrick\'s Day', '0004-03-17');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('April Fools', '0004-04-01');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('Earth Day', '0004-04-22');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('United Nations Day', '0004-10-24');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('Halloween', '0004-10-31');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('Mother\'s Day', '2010-05-09');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('Mother\'s Day', '2011-05-08');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('Mother\'s Day', '2012-05-13');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('Mother\'s Day', '2013-05-12');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('Mother\'s Day', '2014-05-11');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('Mother\'s Day', '2015-05-10');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('Mother\'s Day', '2016-05-08');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('Mother\'s Day', '2017-05-14');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('Mother\'s Day', '2018-05-13');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('Mother\'s Day', '2019-05-12');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('Mother\'s Day', '2020-05-10');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('Father\'s Day', '2010-06-20');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('Father\'s Day', '2011-06-19');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('Father\'s Day', '2012-06-17');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('Father\'s Day', '2013-06-16');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('Father\'s Day', '2014-06-15');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('Father\'s Day', '2015-06-21');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('Father\'s Day', '2016-06-19');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('Father\'s Day', '2017-06-18');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('Father\'s Day', '2018-06-17');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('Father\'s Day', '2019-06-16');
INSERT INTO {$db_prefix}calendar_holidays (title, event_date) VALUES ('Father\'s Day', '2020-06-21');

The reason is the escaped single quote, replacing the \' with '' works fine.

It's strange because this syntax should be supported by pg:

Any other character following a backslash is taken literally. Thus, to include a backslash character, write two backslashes (\\). Also, a single quote can be included in an escape string by writing \', in addition to the normal way of ''.

And it's very strange because I didn't experience that during my previous experiments.
One thing I changed (apart from the operating system: opensuse before, kubuntu now) is postgre version: 8.3 (I think, I cannot verify, but it was for sure 8.x with x = [3|4]) in the past, 9.1 now, here the details:

Code Select Expand

PostgreSQL 9.1.1 on i686-pc-linux-gnu, compiled by gcc-4.6.real (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 32-bit

[Norv]

I think this setting may explain it:
(http://www.postgresql.org/docs/9.1/static/runtime-config-compatible.html)

backslash_quote (enum)
This controls whether a quote mark can be represented by \' in a string literal. The preferred, SQL-standard way to represent a quote mark is by doubling it ('') but PostgreSQL has historically also accepted \'. However, use of \' creates security risks because in some client character set encodings, there are multibyte characters in which the last byte is numerically equivalent to ASCII \. If client-side code does escaping incorrectly then a SQL-injection attack is possible. This risk can be prevented by making the server reject queries in which a quote mark appears to be escaped by a backslash. The allowed values of backslash_quote are on (allow \' always), off (reject always), and safe_encoding (allow only if client encoding does not allow ASCII \ within a multibyte character).

[emanuele]

Nope, but the answer is on the same page:

standard_conforming_strings (boolean)

This controls whether ordinary string literals ('...') treat backslashes literally, as specified in the SQL standard. Beginning in PostgreSQL 9.1, the default is on (prior releases defaulted to off). Applications can check this parameter to determine how string literals will be processed. The presence of this parameter can also be taken as an indication that the escape string syntax (E'...') is supported. Escape string syntax (Section 4.1.2.2) should be used if an application desires backslashes to be treated as escape characters.

So as it is with a default postgreSQL 9.1 SMF won't work properly.

[Norv]

Looks like it! Acknowledged. I think this should be not only considered a bug, but also, we should make it better known to people as soon as possible... Where's that page documenting the known issues for 2.0? (hey, wiki's not really my cup of tea )

[emanuele]

No idea...
http://wiki.simplemachines.org/smf/Known_issues

Added a note on http://wiki.simplemachines.org/smf/Requirements_and_recommendations also.

Do you prefer it tracked for 2.0 or 2.1?

[Norv]

I'd prefer us to deal with it for 2.0, TBH.

[emanuele]

A quick way to "fix" it would be to put:

Code Select Expand

$smcFunc['db_query']('', '
SET standard_conforming_strings = off',
array()
);

somewhere in smf_db_initiate for pg only.

[Angelina Belle]

http://dev.simplemachines.org/mantis/view.php?id=4858 documented in http://wiki.simplemachines.org/smf/index.php?title=Known_issues