Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot

DBI, place holders and CGI forms

by Anonymous Monk
on Jun 02, 2011 at 15:08 UTC ( #907815=perlquestion: print w/replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

So I'm using DBI to query a database and populate a webpage using CGI. Now I have a form with fields, some mandatory, others optional. In my code I use place holders because I read that's the safe way to do things. Can I still use place holders but dynamically create a query adding to the 'where' clause based on parameters send via CGI? That is to say, I'll need to detect which fields have been submitted. Thanks

Replies are listed 'Best First'.
Re: DBI, place holders and CGI forms
by runrig (Abbot) on Jun 02, 2011 at 15:19 UTC
    You can do that, or you can use the DBI 'quote' method on the parameters to dynamically add them to the sql string.
      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

        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);
Re: DBI, place holders and CGI forms
by wind (Priest) on Jun 02, 2011 at 15:32 UTC

    Yes, even if you dynamically build your sql statement, you should still use placeholders and bind values.

    A functionally equivalent method would be to use DBI::quote, but I prefer to always use placeholders less one forget to quote a field.

Re: DBI, place holders and CGI forms
by kennethk (Abbot) on Jun 02, 2011 at 15:37 UTC
    Whether or not you can insert placeholders for column names is database-specific (see Placeholders and Bind Values) but will at the least compromise the speed of your queries if they are used more than once. Rather than using placeholders for column names, the easy solution would be to just bind nulls to the unused parameters and use 1 SQL statement.

    If you are doing updates, so you cannot have unused fields present, I personally usually use branching in the Perl and concatenate my SQl based on local requirements. Ugly, I know, but it gets the job done.

      Whether or not you can insert placeholders for column names is database-specific

      Perhaps I misunderstand you. I would be quite surprised if any DBD allowed ordinary placeholders to be used to insert column names.

      For maximum clarity, assume I get a dynamic column name and desired value like so:

      my $colname= 'city'; my $value= 'Toledo';

      such that I want to use that to dynamically create the query:

      select * from mytable where city = 'Toledo';

      and I try to use a placeholder for the column name like so:

      my $sth= $dbh->prepare("select * from mytable where ? = ?"); ... $sth->execute( $colname, $value );

      Then the query that gets run should surely be:

      select * from mytable where 'city' = 'Toledo';

      which should always return 0 rows since the string 'city' is never equal to the string 'Toledo'.

      But the link you provide is indeed an excellent resource for this type of question. Thanks!

      - tye        

      My db is 10G oracle. I don't understand your bind to null answer. Do you mean bind the variable for example $foo = $cgi->param('foo') || undef; and call execute with $foo, or something else?
        Something functionally equivalent, yes. DBD::Oracle will map undef to an Oracle null. I would probably build a hash with expected field/value pairs (protection against an untrusted client), and pass that into my query routine. Any unspecified parameter would therefore be automatically mapped to undef.

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (6)
As of 2018-03-20 00:43 GMT
Find Nodes?
    Voting Booth?
    When I think of a mole I think of:

    Results (247 votes). Check out past polls.