How do I use spaces in parameters for DBI->connect?

Question

I'm trying to connect with an SSL client key using DBI and DBD::Pg.

use strict;
use warnings 'all';
use DBI;

my $dsn = "dbi:Pg:db=mydb;sslmode=require;host=localhost;"
    ."sslcert=C:\\path with\\spaces.crt;"
    ."sslkey=C:\\path with\\spaces.key";

my $dbh = DBI->connect( $dsn, 'username', '' );

I get the following error:

Can't connect to database: missing "=" after "with\spaces.crt" in connection info string!

I have tried using single or double quotes around the values to no avail, and I can't find anything in the documentation.

Update

With single quotes as follows:

my $dsn = "dbi:Pg:db=mydb;sslmode=require;host=localhost;"
    ."sslcert='C:\\path with\\spaces.crt';"
    ."sslkey='C:\\path with\\spaces.key'";

I get the following error:

failed: FATAL:  connection requires a valid client certificate

I know that this configuration works, as it works in Python.

It turns out that this works:

my $dsn = "dbi:Pg:db=mydb;sslmode=require;host=localhost;"
    ."sslcert='C:\\\\path with\\\\spaces.crt';"
    ."sslkey='C:\\\\path with\\\\spaces.key'";

Why do I need double escaped backslashes?

Answer 1

To include an attribute containing spaces in your DSN, surround the value with single quotes:

my $dsn = q{dbi:Pg:db=mydb;sslmode=require;host=localhost;}
        . q{sslcert='C:\\\path with\\\spaces.crt';}
        . q{sslkey='C:\\\path with\\\spaces.key'};

Note that the semicolon used to separate connection attributes must be outside the single quotes. Also note that backslashes and single quotes inside attributes must be escaped with backslashes (you have to use three backslashes above since Perl converts \\ to \ in single-quoted strings).

If your DSN is enclosed in double quotes, you have to use four backslashes, since Perl interpolates escape sequences like \n in double-quoted strings:

my $dsn = qq{dbi:Pg:db=mydb;sslmode=require;host=localhost;}
        . qq{sslcert='C:\\\\path with\\\\spaces.crt';}
        . qq{sslkey='C:\\\\path with\\\\spaces.key'};

---

As for documentation, I don't see this mentioned in DBD::Pg, but you can see that it's supported by looking at the source. The code that handles the DSN is in dbdimp.c in the DBD::Pg distribution:

    /* DBD::Pg syntax: 'dbname=dbname;host=host;port=port', 'User', 'Pass' */
    /* libpq syntax: 'dbname=dbname host=host port=port user=uid password=pwd' */

...

    /* Change all semi-colons in dbname to a space, unless single-quoted */
    dest = conn_str;
    while (*dbname != '\0') {
            if (';' == *dbname && !inquote)
                    *dest++ = ' ';
            else {
                    if ('\'' == *dbname)
                            inquote = !inquote;
                    *dest++ = *dbname;
            }
            dbname++;
    }
    *dest = '\0';

This converts a DBI-style connection string to a libpq-style connection string (libpq is the Postgres C API, which DBD::Pg uses behind the scenes). Since the generated DSN is passed straight to libpq, it needs to follow the rules for quoting and escaping described in the libpq documentation.

A documentation patch for DBD::Pg would certainly be in order.

Answer 2

The problem is the space. It's not clear if it's possible to provide a path with a space in it. If it is, it would be probably be driver-specific syntax. You might have to dig into DBI and/or DBD::Pg to determine the syntax if it's supported. Some have done this and mentioned in the comments that you might be able to use the following:

my $dsn = join(';',
   "dbi:Pg:db=mydb",
   "sslmode=require",
   "sslcert='$ssl_cert_qfn'",
   "sslkey='$ssl_key_qfn'",
);

Or you could approach the problem from another angle. Windows has a backwards compatibility system to allow applications that only support DOS-style paths. Of note is that DOS didn't allow spaces in paths. By using a DOS-style paths, you can avoid the problem.

use Win32 qw( );

my $dsn = join(';',
   "dbi:Pg:db=mydb",
   "sslmode=require",
   "sslcert=".Win32::GetShortPathName($ssl_cert_qfn),
   "sslkey=".Win32::GetShortPathName($ssl_key_qfn),
);

Another solution is to use a configuration file as detailed in DBD::Pg's documentation.