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

Re: DBI, place holders and CGI forms

by runrig (Abbot)
on Jun 02, 2011 at 15:19 UTC ( [id://907816]=note: print w/replies, xml ) Need Help??


in reply to DBI, place holders and CGI forms

You can do that, or you can use the DBI 'quote' method on the parameters to dynamically add them to the sql string.

Replies are listed 'Best First'.
Re^2: DBI, place holders and CGI forms
by Anonymous Monk on Jun 02, 2011 at 15:33 UTC
    I'm looking at the quote method, the pod says not to be used with placeholders and bindvariables. From the example shown I dont see how I could use it to dynamically build up a SQL query anyway. To better explain my problem. I have a form with fields foo bar and baz. In the perl code I have a sql query "select col1, col2, col3 from tab_name where foo=? and bar=? and baz=?". Is there a way I can remove parameters from the where clause, while still doing things safely, or do I need to learn a better way of dynamically generating a sql query based on input?

      You generally use placeholders or DBI's quote but usually not both. I never use quote myself. placeholders are generally the way to go because you can often prepare the SQL once with the placeholders and execute as many times as you like - thus saving parsing and preparing of the SQL repeatedly.

      When DBI says don't quote placeholders it means don't run the quote method on placeholders i.e., don't do:

      $s = $h->prepare(/select * from table where a = ?/); $s->execute($h->quote("myvalueforcolumna"));

      You use quote when you are building all the SQL yourself and not using placeholders so the above would be:

      $s = $h->prepare(q/select * from table where a = / . $h->quote("myva +lueforcolumna");

      Try and stick with placeholders. I don't know your schema but you can usually do something like:

      select col1, col2, col3 from tab_name where (? is null or foo = ?) a +nd (? is null or bar = ?) and (? is null or baz = ?)

      then pass each value for foo, bar and baz twice

        I never use quote myself.

        Actually, quote() is the more general solution. I've run into quite a few different problems trying to use placeholders. And I've never noticed any performance problem with my failure to re-use prepared queries. I've actually had more problems due to people being too aggressive about trying to re-use prepared queries such as using prepare_cached() way too much.

        A significant fraction (perhaps a majority) of the environments where I've done significant DBI work were such that placeholders had no performance impact at all. For example, the default with DBD::mysql seems to still be to not do server-side prepares.

        At more than one recent employer I've experienced upgrading DBD::Pg such that prepares started happening server-side and then having to disable server-side prepares due to more than one resulting problem (the query planner got significantly stupider in a few important cases, it was incompatible with PgBouncer or other middle-layer tools, and other problems).

        We had some breakage at PerlMonks because "limit ?" worked fine on older versions of DBD::mysql but then stopped working after some ugprade.

        And I've had significant problems with placeholders when using DBD::ODBC. The first batch of problems were around certain data type checking such that "where somedate = '2011-01-01'" is no problem but "where somedate = ?" plus passing '2011-01-01' to execute() gave some "type mismatch" error.

        The scariest problem was using DBD::ODBC and having "where somedate between cast(? as datetime) and cast(? as datetime)" work without emitting any errors or warnings but then returning the wrong data. But, of course:

        join ' ', ... "where somedate between", $dbh->quote($start), "and", $dbh->quote($end),

        worked exactly as expected (whether "cast( ... as datetime)" was included or not).

        So, I like being able to use placeholders. But way too often that actually requires being able to disable server-side prepares which then just boils down to $dbh->quote() except that the DBD module is doing that call for you (and thus removes your claimed advantage).

        Actually, the majority of my experience using DBI is via some wrapper (quite a few different ones, some extremely thin, some only moderately thin) that takes my column names and column values and assembles at least some of the SQL for me.

        - tye        

        Thanks for this reply. I just tried this in sqlplus and I'm starting to understand your advice. Thank you again. I'll test this with DBI in a while. Is there any way via DBI or SQL plus I can easily benchmark two statements? Thank you.
      You can also stick with placeholders by doing something like:
      my @where, @params); if (defined $foo) { push @where, "foo = ?"; push @params, $foo; } if (defined $bar) { push @where, "bar = ?"; push @params, $bar; } my $sth = $dbh->prepare($sql . " WHERE ". join(" AND ", @where)); $sth->execute(@params);

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others imbibing at the Monastery: (3)
As of 2024-03-19 04:39 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found