Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

perl

dbi

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?

like image 442
RazerM Avatar asked Dec 16 '15 17:12

RazerM


People also ask

What is DBI module?

The DBI is a database access module for the Perl programming language. It provides a set of methods, variables, and conventions that provide a consistent database interface, independent of the actual database being used.

What is DBD in Perl?

DBI is a database-independent interface for the Perl programming language. DBD::mysql is the driver for connecting to MySQL database servers with DBI. DBI is the basic abstraction layer for working with databases in Perl. DBD::mysql is the driver for using MySQL with DBI.


2 Answers

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.

like image 161
ThisSuitIsBlackNot Avatar answered Oct 12 '22 12:10

ThisSuitIsBlackNot


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.

like image 28
ikegami Avatar answered Oct 12 '22 13:10

ikegami