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

Hi Monks!

I am trying to add place holders to a SQL query, but I am formatting the second query based on a first SQL query.
The code sample here shows what I am trying to do, if its possible:

# The first SQL query to get the state value<br>

my $citydata = $dbh->exec_select( "select city,state from mytable wher
+e city  = ?", $city );

 # This one gets me all the values found for state 
my $statedata = join ' OR ', map { "state = $_->{ state}" } @$citydata
+;
# Now $statedate gets this:  “state = NH OR state = NY”.

# I would like to add place holders and pass these values into the sec
+ond query
# To read like “state = ? OR state ?”

  my $codedata = $dbh->exec_select( "select code from othertable where
+ (year=2014 and ($statedata)), $citydata->{state},$citydata->{state} 
+   );

[download]

Any help?
Thanks for looking!

Replies are listed 'Best First'.
Re: Place holders in SQL query
by poj (Abbot) on Oct 10, 2014 at 16:07 UTC

    Can you not just join the tables ?

    my $sql = 'SELECT A.code FROM othertable as A
           LEFT JOIN mytable AS B
                  ON B.state = A.state
           WHERE A.year=? AND B.city=?';
           
my $codedata = $dbh->selectall_arrayref( $sql, undef, 2014, $city );

    [download]
    poj
[reply]
[d/l]
Re: Place holders in SQL query
by McA (Priest) on Oct 10, 2014 at 16:26 UTC

    Hi,

    not an answer to your initial question, but an addendum: Why not just use a join or subselect? This saves really roundtrips and (real) databases are very good in this. Example:

    select code from othertable
where year = ?
and state in (select state from mytable where city = ?)

    [download]

    Regards
    McA

[reply]
[d/l]
Re: Place holders in SQL query
by mje (Curate) on Oct 10, 2014 at 15:03 UTC

    Not saying it is best but you could do this. Change your map to be 'map {"state=?"} @$citydata'. Then pull the states out of @$citydata and into an array and pass this array to the exec_select().

[reply]
      Thats where I am stuck; "Then pull the states out of @$citydata and into an array and pass this array to the exec_select()"
[reply]
        my @states = map {$_->{state}} @$citydata;
.
.
exec_select(sql, @states);

        [download]
[reply]
[d/l]
Re: Placeholders in SQL query
by erix (Prior) on Oct 10, 2014 at 15:17 UTC

    Unless you have unsafe values in that database (for 'state') I don't see why you'd need placeholders here.

    The idea of placeholders for security is for when the values that are passed to the SQL are untrusted. When you just got them from your own database there is no risk, as far as I can see (provided, of course, that there is no rubbish in the data-providing table(s)).

    Don't forget placeholder-usage comes with a price. It's becomes harder for the planner to pick a good execution plan (as it has less precise/constant information/statistics about what it is that the query has to do).

[reply]
      Don't forget placeholder-usage comes with a price. It's becomes harder for the planner to pick a good execution plan

      Don't forget non-placeholder-usage comes with a price. It is much less efficient to prepare and execute 1,000,000 separate queries than it is to prepare a query once and then execute 1,000,000 times with different bound arguments.

[reply]
      I don't see why you'd need placeholders here.

      Someday someone will (accidentally?) stuff an untrusted value into the variable that's being interpolated. Or a bad value will somehow get into the database.

      Don't forget placeholder-usage comes with a price.

      Optimizations should be reserved for later, when benchmarking shows the problem is in the query.

      Placeholders are a good idea.

[reply]
      In my particular situation its not a case of "untrusted" data, but I have to escape the data been passed. I know if I do this (noticed the single quotes): 
      my $statedata = join ' OR ', map { "state = ‘$_->{ state}’" } @$cityda
+ta;

      [download]
      The single quotes need to be there in order to work and by using place holders I would not have to do that.
[reply]
[d/l]

      The idea of placeholders for security is for when the values that are passed to the SQL are untrusted.

      If the programmer did not type the data into the program sourcecode, then it is external to the program, then it is not safe to use without placeholders

[reply]

Back to Seekers of Perl Wisdom