Perl and Postgresl table name with dash

Question

This line works fine under linux :

PGPASSWORD=***** psql --username=**** -h ***** -d **** -p **** -P pager=off -t --csv -c "select \"Nom\", \"Path\" from \"vVMware-Vms\" where \"Date\" = '2025-07-22';"

When I try to embed it in a perl script, it outputs me an error. Tried many separators (single-quote, double-quote) without success.

In my perl script, I create my string wih this line (but tried many other things) :

$Cmd = qq(PGPASSWORD=**** psql --username=**** -h ***** -d ***** -p **** -P pager=off -t --csv -c "select \"Nom\", \"Path\" from \"vVMware-Vms\" where \"Date\" = '2025-07-22';");

When printing it (with Dumper), I got :

$VAR1 = 'PGPASSWORD=**** psql --username=**** -h **** -d **** -p **** -P pager=off -t --csv -c "select "Nom", "Path" from "vVMware-Vms" where "Date" = \'2025-07-22\';"';

And when running, I got this message :

ERROR:  syntax error at or near "-"
LIGNE 1 : select Nom, Path from vVMware-Vms where Date = '2025-07-22';
                                       ^

And of course, I cannot change the table name and don't want to use DBI module.

Any clue ?

Works with double escaping backslashes. My goal was to suppress the existing use of the DBI module ! :-) — Denis.A
– Denis.A, Commented Jul 24 at 9:08
DBI will fix many problems and avoid many others, and probably make your program safer and easier to maintain. It is not unreasonable to have one module dependence for a program. — TLP
– TLP, Commented Jul 24 at 11:07
Using a bourne shell command that launches psql instead of DBI makes it less portable, not more. You now require sh and psql on top of perl. — ikegami
– ikegami, Commented Jul 24 at 11:17

Ruslan Osmanov · Accepted Answer · 2025-07-24 09:24:22Z

2

You need to escape the quotes at two levels: Perl and shell.

However, your code escapes them only at the Perl level:

$Cmd = qq(PGPASSWORD=**** psql --username=**** -h ***** -d ***** -p **** -P pager=off -t --csv -c "select \"Nom\", \"Path\" from \"vVMware-Vms\" where \"Date\" = '2025-07-22';");

So, for example, "select "\"Nom\" becomes "select "Nom" in Perl. This is the entire string you get at Perl level:

PGPASSWORD=**** psql --username=**** -h ***** -d ***** -p **** -P pager=off -t --csv -c "select "Nom", "Path" from "vVMware-Vms" where "Date" = '2025-07-22';"

The shell then interprets "select "Nom" as select concatenated with unescaped Nom, concatenated with a beginning of a double quoted string. You can check that as follows:

$ printf '%s\n' "select "Nom", "Path" from "vVMware-Vms" where "Date" = '2025-07-22';"
select Nom, Path from vVMware-Vms where Date = '2025-07-22';

To properly escape the string in Perl, you need to double-escape the quotes as follows:

$Cmd = qq(PGPASSWORD=**** psql --username=**** -h ***** -d ***** -p **** -P pager=off -t --csv) .
    qq(-c "select \\\"Nom\\\", \\\"Path\\\" from \\\"vVMware-Vms\\\" where \\\"Date\\\" = '2025-07-22';");

Note that \\\" produces \": the first backslash escapes the second one; the third backslash escapes the double quote.

Alternatively, wrap the -c option argument in single quotes:

$Cmd = qq(PGPASSWORD=**** psql --username=**** -h ***** -d ***** -p **** -P pager=off -t --csv -c) .
    qq('select "Nom", "Path" from "vVMware-Vms" where "Date" = '\\\''2025-07-22'\\\'';');

Note that the complicated escaping for the single quotes.

edited Jul 24 at 9:24

answered Jul 24 at 8:58

Ruslan Osmanov

21.8k8 gold badges52 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Denis.A Jul 24 at 9:13

Works with only two backslashes, but since I've a date/time field, it must be wrapped by single quote, so I cannot wrap with these single-quotes (except when escaping them -> back to the beginning).

Ruslan Osmanov Jul 24 at 9:28

@Denis.A, I've updated the single-quoted part. As for the double and triple backslash escaping, I've found that, surprisingly, both triple and double slash escaping produce the same results. E.g., qq(\\\"abc\\\") is the same as qq(\\"abc\\").

Denis.A Jul 24 at 10:22

I've already noticed that during my (many) tries.

ikegami Jul 24 at 15:51

Re "Note that \\\" produces \", So would \\". No need to escape " in a when using () as the delimiters

ikegami Jul 24 at 16:00

Same goes for \\\' in the final snippet. \\' would do. \' would do if you replaced qq() with q().

ikegami · Accepted Answer · 2025-07-24 13:17:16Z

2

DBI is the way to go here.

But for educational purposes on how to properly inject text into other text, I'll answer the question as asked.

The key is building the string programmatically instead of starting with the complete command.

use String::ShellQuote qw( shell_quote );

my $host   = ...;
my $port   = ...;
my $user   = ...;
my $passwd = ...;
my $db     = ...;

my $sql = ...;

my $shell_cmd = shell_quote(
   "psql" => (
      "-h" => $host,
      "-p" => $port,
      "--username=$user",
      "-d" => $db,
      "-P" => "pager=off",
      "-t",
      "--csv",
      "-c" => $sql,
   )
);

local $ENV{ PGPASSWORD } = $passwd;

system( $shell_cmd );
die "Failed to launch shell: $!\n"               if $? == -1;
die "Shell killed by signal ".( $? & 0x7F )."\n" if $? & 0x7F;
die "Shell exited with error ".( $? >> 8 )."\n"  if $? >> 8;

But since you don't actually need the shell, you can avoid building a shell command!

my @cmd = (
   "psql" => (
      "-h" => $host,
      ...
   )
);

local $ENV{ PGPASSWORD } = $passwd;

system { $cmd[ 0 ] } @cmd;
die "Failed to launch psql: $!\n"               if $? == -1;
die "psql killed by signal ".( $? & 0x7F )."\n" if $? & 0x7F;
die "psql exited with error ".( $? >> 8 )."\n"  if $? >> 8;

Similarly, when building the SQL, you should need to properly quote interpolated values and identifiers.

Normally, one would use $dbh->quote and $dbh->quote_identifier.

my $sql = sprintf( <<'__',
   SELECT "Nom", "Path"
   FROM "vVMware-Vms"
   WHERE "Date" = %s
__
   $dbh->quote( $date ),
);

Without DBI, you'll have to provide your own quoting functions.

edited Jul 24 at 13:17

answered Jul 24 at 11:24

ikegami

391k17 gold badges291 silver badges555 bronze badges

6 Comments

ikegami Jul 24 at 13:13

fyi, this is the shell command produced:

psql -h '<host>' -p '<post>' '--username=<user>' -d '<db>' -P pager=off -t --csv -c 'SELECT "Nom", "Path" FROM "vVMware-Vms" WHERE "Date" = '\''2025-07-22'\'

. Wrapping that in q{} allows easy placement into code.

ikegami Jul 24 at 17:24

system with one arg always does (except when optimized away). system with multiple args and system BLOCK LIST never do (well, unless you do something like system { "/bin/sh" } ...).

ikegami Jul 24 at 17:26

@Ruslan Osmanov, No. No shell is called, so how could it possibly interpret "special characters". It's echo, not the shell, expanding \e and \t. Neither \e nor \t are special to sh (or any other shell I know, except within $'...' in bash) You can test this with printf '%s\n' 'a\tb' (and with printf '%s\n' $'a\tb' in bash).

|

Collectives™ on Stack Overflow

Perl and Postgresl table name with dash

2 Answers 2

5 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related