Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things

DBI variable argument count

by anothersmurf (Novice)
on Sep 13, 2012 at 22:09 UTC ( #993589=perlquestion: print w/replies, xml ) Need Help??
anothersmurf has asked for the wisdom of the Perl Monks concerning the following question:

I have an application that can have a variable argument count so I have to assemble the query myself each time it runs. This instance uses a select statement.

I want to split the SQL to protect against insertion attacks so I have been using this:

my $sth = $dbh->prepare( $sql ); $sth->execute( $bar, $baz );

I am having trouble with the execute() subroutine. I don't know how to properly pass only the arguments that are needed / were supplied to the program. I see there is an execute_array subroutive and I'm not sure if that offers help. The documentation's example is unfortunately above my head. :(

Thank you monks. Long time no see.

Replies are listed 'Best First'.
Re: DBI variable argument count
by ig (Vicar) on Sep 13, 2012 at 22:58 UTC

    I tend to assemble my arguments to execute as I assemble my SQL statement. There are many ways to do it. The following might give you some ideas:

    use strict; use warnings; use Data::Dumper; update(10, { name => 'test', value => 'success', }); sub update { my ($key, $params) = @_; my $sql = "update my_table set "; my @args; $sql .= join( ', ', map { push(@args, $params->{$_}); "$_ = ?" } keys %$params ); $sql .= " where key = ?"; push(@args, $key); die Dumper([$sql, \@args]); }

    which gives a coordinated SQL statement and array of arguments for execute:

    $VAR1 = [ 'update my_table set value = ?, name = ? where key = ?', [ 'success', 'test', 10 ] ];

    update: you should check the field names to avoid SQL injection. I typically qualify them against a list of known field names one way or another (grep a list, lookup in a hash, match a regular expression, etc.)

      oh my. I don't get it. I think I'm doing this right but I'm obviously not.
      $request = "SELECT * FROM nsmw WHERE unit = ? AND name1 REGEXP ? AND e +mail REGEXP ? ORDER BY unit" @values = ("001", "duckman", ""); # Do database transaction my $dbh = DBI->connect("DBI:mysql:database=db;host=localhost", "user", + "user",{'RaiseError' => 1}); my $sth = $dbh->prepare($request); $sth->execute(@values); while (my $ref = $sth->fetchrow_hashref()) { # here is where the magic happens h3("Unit $ref->{'unit'}"); }
      There is a matching row in that DB but for some reason it just doesn't print anything in the while loop. :(

      I think maybe I'm just too tired. I've been coding for 9 hours without a break =)

        Take a break - it's amazing what a fresh mind can see.

        I'm not familiar with REGEXP, so can't comment on that.

        I would strip down the query then build it back up one argument at a time, to see where the problem is. Do you get some records from the query select * from nsmw? If so, then add your where clauses one at a time. This will, at least, narrow your focus a bit.

        update: a quick look at REGEXP turns up this for MySQL 5.1:

        The REGEXP and RLIKE operators work in byte-wise fashion, so they are not multi-byte safe and may produce unexpected results with multi-byte character sets. In addition, these operators compare characters by their byte values and accented characters may not compare as equal even if a given collation treats them as equal.

        Are your strings plain ASCII or are they multi-byte?

Re: DBI variable argument count
by NetWallah (Abbot) on Sep 13, 2012 at 22:27 UTC
    Try this: (pseudocode)
    my $sth = $dbh->prepare( $sql ); my @var_args = ($bar); # if at least one arg if (Some-condition){ push @var_args, $baz; # others, as needed } $sth->execute( @var_args );
    Update: Corrected declaration of my @var_args

                 I hope life isn't a big joke, because I don't get it.

      I see. I must have messed something up when I tried to pass an array to execute() instead of the individual comma-delimited scalars.

      Thank you.
Re: DBI variable argument count
by CountZero (Bishop) on Sep 14, 2012 at 12:36 UTC
    This may be a good moment for you to invest some time in learning to use DBIx::Class and if that is too much for now, have a look at SQL::Abstract which allows a Perl data-structure to be translated into SQL code.


    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

    My blog: Imperial Deltronics
      No, the SQL statement succeeds from the client. It is a rather simple statement. It is also using ASCII characters. Everything looks right to me still.

      I will check out those modules when I get home. I like DBI and it has worked well for me, however I am trying to learn to write scripts the safer way to protect against SQL attacks and have read repeatedly that this is the way to go about it. :(

Re: DBI variable argument count
by roboticus (Chancellor) on Sep 14, 2012 at 14:08 UTC


    $ cat #!/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 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)

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


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

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

      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, )
      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 | | +------+--------------------+---------------------+ 1 row in set (0.00 sec)


        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.


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

        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", ""); while (my $ref = $sth->fetchrow_hashref()) { # here is where the magic happens h3("this should be running but isn't"); h3("Unit $ref->{'unit'}"); }
        I guess I need to learn how to do my SQL differently.
Re: DBI variable argument count
by anothersmurf (Novice) on Sep 13, 2012 at 22:17 UTC
    I think it may have come to me. Perhaps I need to put the argument values into temporary scalars ($value1, etc) and have a piece of logic code to do the correct execute() depending on the number of values? It seems like that would work but would be very inelegant. I'm happy for any advice! :)

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://993589]
Approved by ig
Front-paged by ig
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (1)
As of 2017-01-18 06:37 GMT
Find Nodes?
    Voting Booth?
    Do you watch meteor showers?

    Results (160 votes). Check out past polls.