Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Reusing placeholders

by Bod (Parson)
on Mar 27, 2021 at 12:49 UTC ( [id://11130466]=perlquestion: print w/replies, xml ) Need Help??

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");
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();
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 (Bishop) 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);

    🦛

      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!

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

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

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

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

        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

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

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11130466]
Approved by haukex
Front-paged by haukex
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others scrutinizing the Monastery: (7)
As of 2024-03-28 12:37 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found