The Postgres PDO driver is treating backslashes in passwords as escape characters. So passwords containing backslashes arrive at the server with the backslash missing or some weird escape stuff going on.

The PDO bug with example is here:
http://bugs.php.net/bug.php?id=53217

Attached patch doubles up the backslashes to work around the problem.

CommentFileSizeAuthor
pdo_pgsql_password_backslash.patch1.11 KBbellHead
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Crell’s picture

Component: database system » postgresql database

That's a superb code comment. :-) The testbot isn't really useful here since it only runs MySQL, so I defer to one of the Postgres maintaners to RTBC this.

Josh Waihi’s picture

What version of PostgreSQL is this effected in? I'm using PostgreSQL 9 with PHP 5.3, using a backslash in my password worked fine. All I did was change my password to "\1Geo" then login again. No errors, no issues.

bellHead’s picture

I've got 8.4, but I don't think the pg version matters. I was seeing the missing \ on the wire to the server.

I'm rebuilding my ahead-of-repository-versions system at the moment and I'll give it another bash, including testing a wider range of possibilities for placing \ in the password (since my initial test had it in a pair with another punctuation mark).

bellHead’s picture

I'm still getting the error.

To reproduce:

  • To set a user password with a backslash in it you have to escape it in psql ALTER USER geo SET PASSWORD '\\1Geo' for your example
  • You can log into psql with the password containing the backslash without escaping it, I'm not sure what needs to be done in a pgpass file
  • If you turn the connection SSL off (ssl = false in the security and authentication section) you can see the credentials on the wire with wireshark/etherape/your-favourite-libpcap-frontend
Josh Waihi’s picture

Priority: Normal » Minor
Status: Needs review » Reviewed & tested by the community

Ok, this makes sense to me now. I'm happy with the commit. Its kinda minor since its more a PostgreSQL specific issue than anything to do with Drupal.

catch’s picture

Version: 7.x-dev » 8.x-dev
Issue tags: +Needs backport to D7
Dries’s picture

I'm confused though. Josh reported that it worked for him, but apparently it is broken on all PDO versions. Thoughts?

bellHead’s picture

It is broken in all PDO versions. I think that Josh had trouble reproducing it because he didn't actually get a password with a backslash set up to test with - as I mentioned in #4 the backslash needs to be escaped in the CREATE USER or ALTER USER command to have an effect.

Dries’s picture

Status: Reviewed & tested by the community » Fixed

No word from Josh yet.

I've decided to commit the above patch to 7.x and 8.x as there is enough evidence that this is an issue with PDO.

Josh can always re-open the issue if necessary.

Josh Waihi’s picture

Thanks Dries. As bellHead suggested, its a limitation with PDO rather than a bug with Drupal. It can be easily avoided by not using backslashes in the password to connect the database. Albeit, not an ideal solution, I'm fine with it.

IMO, its better to run passwordless accounts over SSL with proper configured pg_hba.conf since database credentials are readable in settings.php anyway.

Status: Fixed » Closed (fixed)
Issue tags: -Needs backport to D7

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