I get this error on the last page of the installler:

SQLSTATE[22025]: Invalid escape sequence: 7 ERROR: invalid escape string HINT: Escape string must be empty or one character.

The consequence here seems to be that the installation fails (since an ERROR means that no more commands will be processed until a new transaction is started).

The site is non-functional after this install (even though the final page say that Drupal was installed successfully).

The error I get when visiting the main site URL after this error is:

PDOException: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near ")" LINE 1: SELECT * FROM menu_router WHERE path IN () ORDER BY fit DESC... ^: SELECT * FROM {menu_router} WHERE path IN () ORDER BY fit DESC LIMIT 1 OFFSET 0; Array ( ) in menu_get_item() (line 426 of /usr/srv/www/misc/mikkel.live.revealit.dk/includes/menu.inc).

Which seems to imply that there is also a check missing in menu_get_item, since it tries to make an IN-query with an empty array…

CommentFileSizeAuthor
#11 2010-09-18-core-910388-no-prefix.patch1.34 KBmikl
#8 2010-09-18-core-910388.patch1.35 KBmikl
#7 2010-09-14-core-910388.patch1.44 KBmikl
Screen shot 2010-09-13 at 21.42.12.PNG49.36 KBmikl

Comments

mikl’s picture

mikl
Location Møn
commented

Spun the IN-query part out as #910400: SQL error in menu_get_items when menu items has no ancestors and router item is not in cache..

mikl’s picture

mikl
Location Møn
commented
Issue tags: +PostgreSQL

Ah, the plot thickens:

[2-1] ERROR: invalid escape string
[2-2] HINT: Escape string must be empty or one character.
[2-3] STATEMENT: DELETE FROM cache
[2-4] WHERE (cid ILIKE 'theme\_registry%' ESCAPE '\\')
[3-1] ERROR: invalid escape string
[3-2] HINT: Escape string must be empty or one character.
[3-3] STATEMENT: DELETE FROM cache_menu
[3-4] WHERE (cid ILIKE 'links:navigation:%' ESCAPE '\\')
[4-1] ERROR: current transaction is aborted, commands ignored until end of transaction block
[4-2] STATEMENT: DELETE FROM cache_block
[4-3] WHERE (expire <> '0') AND (expire < '1284408298')
[5-1] ERROR: current transaction is aborted, commands ignored until end of transaction block
[5-2] STATEMENT: DELETE FROM cache_page
[5-3] WHERE (expire <> '0') AND (expire < '1284408298')
[6-1] ERROR: current transaction is aborted, commands ignored until end of transaction block
[6-2] STATEMENT: SELECT menu_name FROM menu_links WHERE expanded <> 0 GROUP BY menu_name
[7-1] ERROR: invalid escape string
[7-2] HINT: Escape string must be empty or one character.
[7-3] STATEMENT: DELETE FROM queue
[7-4] WHERE (created < '1283544298') AND (name ILIKE 'drupal_batch:%' ESCAPE '\\')

PostgreSQL appears not to like the " ESCAPE '\\\\'" that is added by DatabaseConnection_pgsql::mapConditionOperator(). The four backslashes means that the escape character is defined as two slashes (and not one).

damien tournoud’s picture

damien tournoud commented

Hm. PostgreSQL and PDO do some weird things with string encoding.

Would you have a non-standard configuration for this configuration parameter?

standard_conforming_strings (boolean)

This controls whether ordinary string literals ('...') treat backslashes literally, as specified in the SQL standard. The default is currently off, causing PostgreSQL to have its historical behavior of treating backslashes as escape characters. The default will change to on in a future release to improve compatibility with the standard. 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 should be used if an application desires backslashes to be treated as escape characters.

mikl’s picture

mikl
Location Møn
commented
Assigned: mikl » Unassigned

Okay, I managed to stop the error from occurring by commenting out “standard_conforming_strings = on” in my postgresql.conf, thus returning to the old non-conforming string behaviour, which it seems that we are compensating for in our code.

This constitutes a problem in that “standard_conforming_strings = on” will be the default for PostgreSQL 9.1.

This hails back to #426008: C-style backslashe escapes in string constants cause errors or warnings in postgresql which I have closed myself, since the problem goes away when enabling standard_conforming_strings. I have also written a blog post explaining why standard_conforming_strings is a good thing.

I think we need to find some sort of solution to this that would work both with and without this setting enabled, since the current state of the code essentially forces you to use non-standard-conforming mode if you want to run Drupal. I’d like some input from the DBTNG maintainers…

damien tournoud’s picture

damien tournoud commented

PDO itself knows how to properly escape strings depending on the standard_conforming_strings mode. The best we can do it probably to rely on it, via http://php.net/manual/en/pdo.quote.php.

Could you provide a patch that does that? ie. replace " ESCAPE '\\\\'" by " ESCAPE " . $this->quote("\\").

webchick’s picture

webchick
she/they
Primary language English
Location Vancouver 🇨🇦
commented
Priority: Critical » Major

This seems to be a site-specific configuration problem, rather than a global issue, so downgrading to major.

mikl’s picture

mikl
Location Møn
commented
Status: Active » Needs review
StatusFileSize
new 2010-09-14-core-910388.patch1.44 KB

#5: Yes, that seems to work. I’ve tested the attached patch both with and without the standard-conforming strings enabled, and it works either way. It does make the code less elegant, though: Using a function call inside a static declaration is a syntax error (which i spent a couple of hours banging my head against the wall about).

#6: It is not site-specific, but configuration specific. Many PostgreSQL servers are configured that way, specifically for compatibility with MySQL and Drupal 6, so it’d still say its rather critical to get in before D7 goes out :)

mikl’s picture

mikl
Location Møn
commented
StatusFileSize
new 2010-09-18-core-910388.patch1.35 KB

By suggestion from Damien, I have shortened the comment about the static declarations. Otherwise this patch is identical to the one from #7.

damien tournoud’s picture

damien tournoud commented
Status: Needs review » Reviewed & tested by the community

#win.

This proves how passing string literal to SQL queries is evil.

Status: Reviewed & tested by the community » Needs work

The last submitted patch, 2010-09-18-core-910388.patch, failed testing.

mikl’s picture

mikl
Location Møn
commented
Status: Needs work » Reviewed & tested by the community
StatusFileSize
new 2010-09-18-core-910388-no-prefix.patch1.34 KB

Forgot the --no-prefix to git diff…

Status: Reviewed & tested by the community » Needs work
Issue tags: -PostgreSQL

The last submitted patch, 2010-09-18-core-910388-no-prefix.patch, failed testing.

mikl’s picture

mikl
Location Møn
commented
Status: Needs work » Needs review
Issue tags: +PostgreSQL

#11: 2010-09-18-core-910388-no-prefix.patch queued for re-testing.

mikl’s picture

mikl
Location Møn
commented
Status: Needs review » Reviewed & tested by the community

Reverting to RTBC after the test-bots shenanigans. See #9.

dries’s picture

dries commented
Status: Reviewed & tested by the community » Fixed

Committed to CVS HEAD. Thanks.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

likewhoa’s picture

likewhoa commented
Title: Installation fails on PostgreSQL 8.4: Invalid escape sequence. » Installation fails on PostgreSQL >=8.4: Invalid escape sequence.
Status: Closed (fixed) » Active

Even though this was committed to HEAD, the error is still active on a fresh copy of postgres-server-9.0, drupal-7.0_rc2 and nginx-0.8.53 running GNU/Gentoo Linux.

likewhoa’s picture

likewhoa commented
Status: Active » Closed (fixed)

closing this again* but there was one error after installation which I will open a bug report for.

An AJAX HTTP error occurred. HTTP Result Code: 200 Debugging information follows. Path: http://localhost/install.php?profile=standard&locale=en&id=1&op=do StatusText: OK ResponseText: Home | Drupal @import url("http://localhost/modules/system/system.theme.css?0"); @import url("http://localhost/modules/system/system.messages.css?0"); @import url("http://localhost/modules/system/system.menus.css?0"); @import url("http://localhost/modules/system/system.base.css?0"); @import url("http://localhost/modules/comment/comment.css?0"); @import url("http://localhost/modules/field/theme/field.css?0"); @import url("http://localhost/modules/node/node.css?0"); @import url("http://localhost/modules/search/search.css?0"); @import url("http://localhost/modules/user/user.css?0"); @import url("http://localhost/modules/system/system.admin.css?0"); @import url("http://localhost/modules/system/system.maintenance.css?0"); @import url("http://localhost/themes/seven/reset.css?0"); @import url("http://localhost/themes/seven/style.css?0"); Home Installation tasksChoose profile(done)Choose language(done)Verify requirements(done)Set up database(done)Install profile(active)Configure siteFinished SQLSTATE[25P02]: In failed sql transaction: 7 ERROR: current transaction is aborted, commands ignored until end of transaction block

Also another bug when adding content /node/add

Fatal error: Unsupported operand types in /home/localhost/htdocs/modules/system/system.module on line 2093