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

Thanks to the wisdom of The Monastery I have started using placeholders for all database operations and they are being retro applied to existing code whenever I look at it to enhance security.

In a number of places I have wanted to use the same placeholder multiple times. I have found reference to named placeholders but cannot find a working example and the few experiments I have tried have not worked. Here is an example from production code: 

my $query = $dbh->prepare("SELECT idOutbound, type, DAY(dateDue) FROM 
+LD_Outbound WHERE (dateDue >= ? AND dateDue < ? + INTERVAL 1 MONTH) O
+R (defaultDate >= ? AND defaultDate < ? + INTERVAL 1 MONTH)");
$query->execute("$last_y-$last_m-01", "$last_y-$last_m-01", "$last_y-$
+last_m-01", "$last_y-$last_m-01");

[download]
Here all four placeholders are the same. I have tried using bind_param but that doesn't work 
my $query = $dbh->prepare("SELECT idOutbound, type, DAY(dateDue) FROM 
+LD_Outbound WHERE (dateDue >= ?:NOW AND dateDue < ?:NOW + INTERVAL 1 
+MONTH) OR (defaultDate >= ?:NOW AND defaultDate < ?:NOW + INTERVAL 1 
+MONTH)");
$query->bind_param(:NOW, "$last_y-$last_m-01");
$query->execute();

[download]
The documentation is not very clear.

Is there a way to just specify the variable once if it is going to be used multiple times in the query?

Replies are listed 'Best First'.
Re: Reusing placeholders
by hippo (Archbishop) on Mar 27, 2021 at 12:58 UTC

    Simpler just to use the x operator, no?

    $query->execute(("$last_y-$last_m-01") x 4);

    [download]

    🦛

[reply]
[d/l]
[select]
      Simpler just to use the x operator, no?

      As you point it out...yes it is!
      I had forgotten about the x operator...

      Possibly because I don't think I have ever used it!

[reply]
[d/l]
Re: Reusing placeholders
by erix (Prior) on Mar 27, 2021 at 13:56 UTC

    PostgreSQL (or rather, DBD::Pg) allows dollar notation too, so you can re-use those. (Must start at $1, but can be used in any order in the query), see DBD::Pg docs, under 'placeholders'.

    I normally set $dbh->{pg_placeholder_dollaronly} property so I don't start using the questionable question mark.

    (Of course, if your SQL is submitted to other DBMS than (just) postgres, you'll have to think of something else)

    edit: Oh, I see you're also the last_insert_id guy. That means you're mucking around with a lesser db than PostgreSQL and that the above should be ignored till you upgrade to the better system

[reply]
[reply]
Re: Reusing placeholders
by LanX (Saint) on Mar 27, 2021 at 15:08 UTC
    > (dateDue >= ?:NOW

    This puzzled me ...

    > . I have tried using bind_param but that doesn't work ... The documentation is not very clear.

    Really?

    It says (emphasize added):

    Some drivers also allow placeholders like :name and :N (e.g., :1, :2, and so on) in addition to ?, but their use is not portable.

    If the :N form of placeholder is supported by the driver you're using, then you should be able to use either "bind_param" or "execute" to bind values. Check your driver documentation

    So you need to check the DBD:: you are using

    update

    but I agree that the documentation of DBI is hard to read.

    This might be because of

    • age of module and
    • diversity of underlying engines.

    Cheers Rolf
    (addicted to the Perl Programming Language :)
    Wikisyntax for the Monastery

[reply]
[d/l]
      So you need to check the DBD:: you are using

      I am using MariaDB but using DBD::mysql as the driver.

[reply]
        I couldn't find any hint of that "named/numbered placeholder" feature in DBD::mysql .

        If anyone is aware of a DBD that supports this, I'd be interested to know.

        FWIW I wrote a meditation on a Perl implementation for named placeholders.

        Cheers Rolf
        (addicted to the Perl Programming Language :)
        Wikisyntax for the Monastery

[reply]
A reply falls below the community's threshold of quality. You may see it by logging in.

Back to Seekers of Perl Wisdom