in reply to DBI variable argument count

anothersmurf:

$ cat t.pl
#!/usr/bin/perl
use 5.14.0;
use warnings;
use autodie;
use DBI;

my $orig_SQL="select * from table ";

my @ex = (
    [ qw( apple ) ],
    [ qw( cart horse ) ],
    [ qw( a quick red fox ) ],
);

for (@ex) {

    my $num_args = @$_;
    my $SQL = $orig_SQL
            . " where col in ("
            . join(", ", ("?") x $num_args)
            . ")";
    print "\n\nSQL: $SQL\n\n";
    print "execute(", join(", ", @$_), ")\n";

    # my $ST = $DB->prepare($SQL . $where_clause);
    # $ST->execute(@$_);
    # ... process results ...
}

$ perl t.pl

SQL: select * from table  where col in (?)

execute(apple)


SQL: select * from table  where col in (?, ?)

execute(cart, horse)


SQL: select * from table  where col in (?, ?, ?, ?)

execute(a, quick, red, fox)

[download]

Update: Wow! It's my 2000th post...

...roboticus

When your only tool is a hammer, all problems look like your thumb.

Replies are listed 'Best First'.
Re^2: DBI variable argument count
by clueless newbie (Curate) on Sep 15, 2012 at 13:56 UTC

    And I, for one, appreciate each and every one of your posts.

[reply]
Re^2: DBI variable argument count
by anothersmurf (Novice) on Sep 14, 2012 at 16:03 UTC
    Thank you very much for that. The join() is very nice. I did not know about that one. These work from the mysql interface, but not from my perl program: 
    SQL = "SELECT * FROM dbname WHERE unit = ? AND name1 REGEXP ? AND emai
+l REGEXP ? ORDER BY unit"

execute( 001, duckman, john@google.com )

    [download]
    Do I need to reformat the select statement? It simply won't give me an error/warning, and my $sth->fetchrow_hashref() doesn't give me any data to play with. :( There is a row in my db with appropriate data: 
    
mysql> select unit, name1, email from dbname;
+------+--------------------+---------------------+
| unit | name1              | email               |
+------+--------------------+---------------------+
| 001  | duckman industries | poorjohn@google.com |
+------+--------------------+---------------------+
1 row in set (0.00 sec)

    [download]
[reply]
[d/l]
[select]

      anothersmurf:

      Yeah, perldoc -f join is one of those *really convenient* functions. Before I learned it, I was always doing things like adding a trailing comma to everything, and then clipping off the last one. But I didn't notice that I was always doing things like that until I found the join function. Now I look back and think "Why didn't I notice that and write a join equivalent for <language X>? Ah, well, you can never notice *all* the assumptions you make...

      As someone else has already remarked, placeholders can't be used *everywhere*, but they're still very convenient. When you can't use them, and need to build some dynamic SQL on the fly, be sure to check out $DB->quote($data) to ensure that the data is properly quoted. That'll go a long way in reducing the attack surface of your application.

      ...roboticus

      When your only tool is a hammer, all problems look like your thumb.

[reply]
[d/l]
[select]
      yes... I hard coded the values and they still don't work: 
      my $request = "SELECT * FROM dbname WHERE unit = ? AND name1 REGEXP ? 
+AND email REGEXP ? ORDER BY unit"

my $dbh = DBI->connect("DBI:mysql:database=database;host=localhost", "
+user", "passwd",{'RaiseError' => 1});

my $sth = $dbh->prepare($request);

$sth->execute("001", "duckman", "google.com");

while (my $ref = $sth->fetchrow_hashref())
{
    # here is where the magic happens
    h3("this should be running but isn't");
    h3("Unit $ref->{'unit'}");
}

      [download]
      I guess I need to learn how to do my SQL differently.
[reply]
[d/l]
[reply]
[d/l]
            h3("this should be running but isn't");
    h3("Unit $ref->{'unit'}");

        [download]

        Notice the lack of a print anywhere in that block.....

        Also it was in the wrong place. Hours beating on this sillyness.

        Yes I had quotes on the REGEXP, in my original version. I kept having to modify it to put it here because it looks nothing like this in the code (and the last version where I hard coded it I forgot to, but it wouldn't have worked anyway because of the problems above.)

        Live and learn I guess. Sorry to waste everyone's time.

[reply]
[d/l]

        OK....

        I didn't know they were called placeholders until I came to this site. When I google searched for "mysql select placeholders", I discovered this:

        http://stackoverflow.com/questions/8054421/mysql-perl-placeholder-rules

        Says: "With most drivers, placeholders can't be used for any element of a statement that would prevent the database server from validating the statement and creating a query execution plan for it."

        So now I guess I need to sanitize the user input for the select statement so there is less risk of attack on that front and simply execute() with no arguments.

[reply]

In Section Seekers of Perl Wisdom