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?

@ikegami I do use strict and warnings, and in fact the values are coming from a config file. I just forgot to use double slashes for the SO example. I'll edit, thanks. — RazerM, Dec 16 '15 at 17:24
Can you edit your question to show your attempt with single quotes and the exact error message you got for it? There's code in [dbdimp.c](https://metacpan.org/source/TURNSTEP/DBD-Pg-3.5.3/dbdimp.c#L122) in the DBD::Pg distribution that looks like it should handle single-quoted attributes properly, but I have no way to test right now. — ThisSuitIsBlackNot, Dec 16 '15 at 17:57
@ThisSuitIsBlackNot: Do you mean `"sslcert='C:\\path with\\spaces.crt';"` etc.? As I suspect the OP means `'sslcert=C:\path with\spaces.crt;'` — Borodin, Dec 16 '15 at 18:03
@Borodin The code I linked to in my last comment converts a DBI-style connection string to a [libpq-style connection string](http://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-CONNSTRING), converting semi-colons to spaces unless they're in single quotes, among other things. Based on eyeballing the code (can't test right now), I think `"sslcert='C:\\path with\\spaces.crt';"` should work, but `"'sslcert=C:\path with\spaces.crt;'"` or `"sslcert='C:\\path with\\spaces.crt;'"` won't. But the OP didn't specify which one of those he used. — ThisSuitIsBlackNot, Dec 16 '15 at 18:17
@ThisSuitIsBlackNot: Ah I see he does write *“quotes around the values”* so you may well be right. At least he can try for himself now. Thanks — Borodin, Dec 16 '15 at 18:19
@Borodin When constructing the dsn I tried with "$key='$value'" and the equivalent with double quotes. I'll need to check the errors again at work tomorrow (where my Windows PC is). — RazerM, Dec 16 '15 at 21:56
"Why do I need double escaped backslashes?" Perl converts `"sslcert='C:\\\\path with\\\\spaces.crt';"` to `"sslcert='C:\\path with\\spaces.crt';"`. And the [docs](http://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-CONNSTRING) for libpq say, "Single quotes and backslashes within the value must be escaped with a backslash, i.e., `\'` and ``\\``." — ThisSuitIsBlackNot, Dec 17 '15 at 15:25
@ThisSuitIsBlackNot Ok. It's a shame this isn't handled by DBD::Pg, since it is an implementation detail. — RazerM, Dec 17 '15 at 16:27
I wouldn't necessarily expect DBD::Pg to do that for you, but it definitely *should* be documented. I'll try to submit a doc patch. — ThisSuitIsBlackNot, Dec 17 '15 at 16:36

ThisSuitIsBlackNot · Accepted Answer · 2015-12-17T17:00:13.147

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.

ikegami · Answer 2 · 2015-12-17T17:09:33.040

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.

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

Update

2 Answers2