plperl syntax question

On Mar 12, 2020, at 4:34 PM, stan <stanb@panix.com> wrote:

I am trying to run a query in plperl that uses a Perl variable from the
same function.

Here is the code snippet:

my $stmt = qq("SELECT employee_key from employee where id = '$user' ;");
elog( NOTICE, "stmt = $stmt" );
my $rv3 = spi_exec_query('$stmt');

Here is the runtime output:

NOTICE: stmt = "SELECT employee_key from employee where id = 'stan' ;"
ERROR: syntax error at or near "$" at line 22.

As you can see, the statement seems correctly formatted, right?

What am I doing wrong here?

--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

?try
my $rv3 = spi_exec_query($stmt);

On Thursday, March 12, 2020, stan <stanb@panix.com> wrote:

my $rv3 = spi_exec_query('$stmt');
What am I doing wrong here?

Putting a variable name into a single-quoted string and expecting it to
resolve to the contents of said variable instead of being treated as a
literal.

David J.

On Thu, Mar 12, 2020 at 06:00:01PM -0600, Rob Sargent wrote:

On Mar 12, 2020, at 5:16 PM, stan <stanb@panix.com> wrote:

On Thu, Mar 12, 2020 at 04:58:08PM -0600, Rob Sargent wrote:

On Mar 12, 2020, at 4:49 PM, stan <stanb@panix.com> wrote:

On Thu, Mar 12, 2020 at 04:45:58PM -0600, Rob Sargent wrote:

On Mar 12, 2020, at 4:34 PM, stan <stanb@panix.com> wrote:

I am trying to run a query in plperl that uses a Perl variable from the
same function.

Here is the code snippet:

my $stmt = qq("SELECT employee_key from employee where id = '$user' ;");
elog( NOTICE, "stmt = $stmt" );
my $rv3 = spi_exec_query('$stmt');

Here is the runtime output:

NOTICE: stmt = "SELECT employee_key from employee where id = 'stan' ;"
ERROR: syntax error at or near "$" at line 22.

As you can see, the statement seems correctly formatted, right?

What am I doing wrong here?

--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

?try
my $rv3 = spi_exec_query($stmt);

OK, now I have:

my $stmt = qq("SELECT employee_key from employee where id = '$user' ;");
elog( NOTICE, "stmt = $stmt" );
my $rv3 = spi_exec_query($stmt);

And the runtime output is now:

NOTICE: stmt = "SELECT employee_key from employee where id = 'stan' ;"
ERROR: syntax error at or near ""SELECT employee_key from employee where
id = 'stan' ;"" at line 22.

Change you double quotes to single around you select stmt. $user probably doesn???t need quotes at all

Looks like somehow that is createing a double set of quotes
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Thanks.

my $stmt = qq('SELECT employee_key from employee where id = $user ;');
elog( NOTICE, "stmt = $stmt" );
my $rv3 = spi_exec_query($stmt);

runtimeL

NOTICE: stmt = 'SELECT employee_key from employee where id = stan ;'
ERROR: syntax error at or near "'SELECT employee_key from employee where
id = stan ;'" at line 22.
CONTEXT: PL/Perl function "a_foo"

This is not normally hard to get working in non plperl.

Sorry, I didn???t know qq was adding surrounding double quotes.
You may need something like
qq(SELECT employee_key from employee where id = ') + $user + qq(';);
or
qq(SELECT employee_key from employee where id = '$user???;);

depending on how qq works.

I will play with it, but i am not optimistic. You see the elog, that prints
the string that comes out of qq, right? qq is for quoting strings to be
passed to DBD/DBI calls.

I cannot figure out why the call in plpwel is so strange. I would love to
see a workign example of a call to this fucntion that uses a variable, and
works.

--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

On Thu, Mar 12, 2020 at 06:37:02PM -0700, David G. Johnston wrote:

On Thursday, March 12, 2020, stan <stanb@panix.com> wrote:

my $rv3 = spi_exec_query('$stmt');
What am I doing wrong here?

Putting a variable name into a single-quoted string and expecting it to
resolve to the contents of said variable instead of being treated as a
literal.
David J.

Please look at this.

Here is the code:

my $stmt = qq('SELECT employee_key from employee where id = $user ;');
elog( NOTICE, "stmt = $stmt" );
my $rv3 = spi_exec_query($stmt);

As you cna see, I use qq to ahndle the qouting: runtime output looks like
this:

OTICE: stmt = 'SELECT employee_key from employee where id = stan ;'
ERROR: syntax error at or near "$" at line 22.

Looks like a very clean string with the variable substituted.

Anyone have a working example of a call to spi_exec_query() using a variable?

also tried:

my $rv3 = spi_exec_query('$stmt');

Different trun tiem error message.

--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

On Mar 12, 2020, at 7:44 PM, stan <stanb@panix.com> wrote:

On Thu, Mar 12, 2020 at 06:37:02PM -0700, David G. Johnston wrote:

On Thursday, March 12, 2020, stan <stanb@panix.com> wrote:

my $rv3 = spi_exec_query('$stmt');
What am I doing wrong here?

Putting a variable name into a single-quoted string and expecting it to
resolve to the contents of said variable instead of being treated as a
literal.
David J.

Please look at this.

Here is the code:

my $stmt = qq('SELECT employee_key from employee where id = $user ;');
elog( NOTICE, "stmt = $stmt" );
my $rv3 = spi_exec_query($stmt);

As you cna see, I use qq to ahndle the qouting: runtime output looks like
this:

OTICE: stmt = 'SELECT employee_key from employee where id = stan ;’

You can see that the entire sql string is quoted, where what you want from the elog is just single quotes around the value (i.e. 'stan’)

The example I saw for qq on the perl site
print(qq(Welcome to GeeksForGeeks));
doesn’t have any quotes in arg to qq

Try making the sql string without using qq
my $select = “select....’” + $user + “‘;”;
(or perhaps perl has a formatted string function like printf)

On Thursday, March 12, 2020, Rob Sargent <robjsargent@gmail.com> wrote:

On Mar 12, 2020, at 7:44 PM, stan <stanb@panix.com> wrote:

On Thu, Mar 12, 2020 at 06:37:02PM -0700, David G. Johnston wrote:

On Thursday, March 12, 2020, stan <stanb@panix.com> wrote:

my $rv3 = spi_exec_query('$stmt');
What am I doing wrong here?

Putting a variable name into a single-quoted string and expecting it to
resolve to the contents of said variable instead of being treated as a
literal.
David J.

Please look at this.

Here is the code:

my $stmt = qq('SELECT employee_key from employee where id = $user ;');
elog( NOTICE, "stmt = $stmt" );
my $rv3 = spi_exec_query($stmt);

So you fixed the error in the third line i and others pointed out but then
decided to introduce a new error by changing the first line...

David J.

On Thursday, March 12, 2020, Rob Sargent <robjsargent@gmail.com> wrote:

The example I saw for qq on the perl site

print(qq(Welcome to GeeksForGeeks));

doesn’t have any quotes in arg to qq

Correct. It also says:

qq() operator in Perl <https://www.geeksforgeeks.org/introduction-to-perl/&gt; can
be used in place of double quotes. It uses a set of parentheses to surround
the string.

Try making the sql string without using qq

my $select = “select....’” + $user + “‘;”;
(or perhaps perl has a formatted string function like printf)

This is worse than using correctly.

The OP should (I think) be using a parameterized query instead of brute
force string manipulation since this code is a prone to exploit.

David J.