processing PSQL with system command

g_speran has asked for the wisdom of the Perl Monks concerning the following question:

Hello Perl Monks,
I am trying to execute a psql command via the system command within perl and I quite cant get it to function.
If i try from the UNIX command line by performing the following it works as expected.
I've copied the PSQL variable to the perl script and it just fails
What am i overlooking?

Unix CLI
root@ave:/home/admin/scripts/#: PSQL="psql -p 5555 mcdb -c \"update ev
+_cus_prof set active='f' where epid='INIT_EV_HIGH_PRIORITY';\""
root@ave:/home/admin/scripts/#: su - admin -c "$PSQL"
UPDATE 1
root@save:/home/admin/scripts/#:


Perl script snippit
====================
Print_Success("\nDisabling ConnectEMC & Email Home\n");
my $PSQL="psql -p 5555 mcdb -c \"update ev_cus_prof set active=\'f\' w
+here epid=\'INIT_EV_HIGH_PRIORITY\';\"";
print "PSQL: $PSQL \n";
system("su - admin -c \"$PSQL\"");


Perl Output
======================================================================
+=============================
Disabling ConnectEMC & Email Home
PSQL: psql -p 5555 mcdb -c "update ev_cus_prof set active='f' where ep
+id='INIT_EV_HIGH_PRIORITY';"
ERROR:  syntax error at end of input
LINE 1: update
              ^
sh: : command not found
[download]

Comment on processing PSQL with system command Download Code

Replies are listed 'Best First'.
Re: processing PSQL with system command by hippo (Archbishop) on Mar 30, 2022 at 13:59 UTC
`system("su - admin -c \"$PSQL\"");` You have multiply nested double quotes. Don't even try to do that. `system("su - admin -c '$PSQL'");` [download] I am trying to execute a psql command via the system command within perl Why? There's DBD::Pg so you don't have to do horrible stuff like that. 🦛	[reply] [d/l] [select]
Re^2: processing PSQL with system command by g_speran (Scribe) on Mar 30, 2022 at 14:17 UTC
I've tried that as well and fails `Disabling ConnectEMC & Email Home PSQL: psql -p 5555 mcdb -c "update ev_cus_prof set active='f' where ep +id='INIT_EV_HIGH_PRIORITY';" ERROR: column "init_ev_high_priority" does not exist LINE 1: update ev_cus_prof set active=f where epid=INIT_EV_HIGH_PRIO.. +.` [download] On this system we are not permitted to install any modules, so I need to try to make it work with all native modules/commands	[reply] [d/l]
Re^3: processing PSQL with system command by NetWallah (Canon) on Mar 31, 2022 at 05:01 UTC
On this system we are not permitted to install any modules There are multiple indications that your system is an Avamar AVE. The standard Avamar installation has included the perl DBI and DBD::Pg modules pre-installed for at least 15 years. No additional module installation required. Ask your Avamar support representative to obtain "profileadmin.pl" from the Avamar FTP site.(I am the humble author) That perl script will likely fulfill your Avamar profile editing needs and a lot more. "These opinions are my own, though for a small fee they be yours too."	[reply]
Re^3: processing PSQL with system command by hippo (Archbishop) on Mar 30, 2022 at 14:34 UTC
On this system we are not permitted to install any modules Well then you will be stuck in quote-escaping hell as I see now you have a nasty mix of both double and single quotes there. This test looks OK: `use strict; use warnings; use Test::More tests => 1; my $want = q{su - admin -c "psql -p 5555 mcdb -c \\"update ev_cus_prof + set active='f' where epid='INIT_EV_HIGH_PRIORITY';\\""}; my $PSQL = q{psql -p 5555 mcdb -c \"update ev_cus_prof set active='f' +where epid='INIT_EV_HIGH_PRIORITY';\"}; my $cmd = qq{su - admin -c "$PSQL"}; is $cmd, $want, "have $want";` [download] But, in your shoes I would approach whichever entity has imposed the no-modules rule and point out that it means you have to ask the help of complete strangers on the internet to solve what would otherwise be a trivial task. 🦛	[reply] [d/l]
Re^4: processing PSQL with system command by g_speran (Scribe) on Mar 30, 2022 at 15:47 UTC
Re^3: processing PSQL with system command by Fletch (Bishop) on Mar 30, 2022 at 14:52 UTC
Maybe slightly long in the tooth, but Yes, even you can use CPAN Edit: That being said, if you're dead set on taking the hard route you'd be better served writing the SQL into a file and then telling `psql` to run that SQL script instead of trying using `-c` and outguessing how n layers of arbitrary quoting are going to be pulled (or not pulled) apart by however many levels of interpreting. The cake is a lie. The cake is a lie. The cake is a lie.	[reply] [d/l] [select]
Re^3: processing PSQL with system command by cavac (Prior) on Mar 31, 2022 at 12:23 UTC
On this system we are not permitted to install any modules I have seen that policy countless times. It's usually "for security reasons". My usual answer to the "admin" is along the lines in writing: "This is a new security problem created by your policy. These workarounds you are proposing are very insecure! Are you really sure you want it implemented this way? Are you taking the responsibility for it?" Just make sure you CC a number of relevant people (like: your boss, the admin's boss, etc). So when the fit hits the shan, too many high level tie wearers are involved to make this a big affair that gets blamed solely on you. It usually gets swept under the rug, along with the results of all the other "informed decisions" made by middle management over the past year. `perl -e 'use Crypt::Digest::SHA256 qw[sha256_hex]; print substr(sha256_hex("the Answer To Life, The Universe And Everything"), 6, 2), "\n";'`	[reply] [d/l]
Re^3: processing PSQL with system command by Anonymous Monk on Mar 30, 2022 at 18:12 UTC
On this system we are not permitted to install any modules, so I need to try to make it work with all native modules/commands I can see your future and it holds sql injection attacks.	[reply]