Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

Re: DBI variable argument count

by roboticus (Canon)
on Sep 14, 2012 at 14:08 UTC ( #993730=note: print w/ replies, xml ) Need Help??


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)

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

...roboticus

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


Comment on Re: DBI variable argument count
Download Code
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 )
    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)
      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'}"); }
      I guess I need to learn how to do my SQL differently.

        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.

        h3("this should be running but isn't"); h3("Unit $ref->{'unit'}");

        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.

      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.

Re^2: DBI variable argument count
by clueless newbie (Friar) on Sep 15, 2012 at 13:56 UTC

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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://993730]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (6)
As of 2014-09-24 01:37 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (244 votes), past polls