Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

Re: DBI variable argument count

by roboticus (Chancellor)
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.

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

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

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)

      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.

      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.
        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.

        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.

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 surveying the Monastery: (4)
As of 2016-07-24 20:33 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    What is your favorite alternate name for a (specific) keyboard key?


















    Results (221 votes). Check out past polls.