in reply to Test if string is already quote()'d?

DBI's quote method is one of those things that you should almost never use. Instead of saying something like this:

my $name = $dbh->quote("widget");
$dbh->do("delete from products where name = $name");

[download]

It's much safer to use placeholders like this:

$dbh->do("delete from products where name = ?", $name);

[download]

In this case, your function would accept only unquoted strings, but it's a better solution in the long run.

Update Sorry, that should have been:

$dbh->do("delete from products where name = ?", undef, $name);

[download]

The do method accepts an optional hashref of attributes as its second parameter.

Replies are listed 'Best First'.
Re: Re: Test if string is already quote()'d?
by Seumas (Curate) on Jun 14, 2003 at 22:57 UTC
    I use placeholders when possible, but I'm encountering difficulty in situations where I am building an SQL statement depending on various conditions.

    The only way I have found to handle such a situation is to build the SQL statement into a scalar such as $sql and then do a prepare($sql) or do($sql). But then I have to figure out how to build the execute() so I can stick the bind values in. Since the statement I'm building will have a different number and placement of placeholders depending on the situation, the number and order of values to stick in execute() will vary, too.

    I have tried a number of joins and mappings to make this all come out right, but it's nearly impossible to stuff all the values into the right order when the statement can vary drastically from one run to another.

    Also, as I understood from the DBI docs, do() should prepare (including escape quoting) a statement -- but this only seems to be true if you're using placeholders. In other words, if I build a statement and stuff it into $sql (where $sql contains the full statement without any placeholders) and run do($sql), my strings are still naked.
[reply]
      "I have tried a number of joins and mappings to make this all come out right, but it's nearly impossible to stuff all the values into the right order when the statement can vary drastically from one run to another."

      Nonesense. ;)

      Say you have a hash ref like so: 
      my $hash = {
   name  => 'Joe',
   phone => 222,
   code  => 'A',
};

      [download]
      Just a simple hash ref whose keys are fields in a database table. Now consider this sub that takes two args, a scalar (the name of the table to insert into) and a hash ref: 
      sub insert {
   my ($table,$hash) = @_;
   my $sth = $dbh->prepare(
      "insert into $table ("
      . join(',', keys %$hash)
      . ') values ('
      . join(',',map '?',keys %$hash)
      . ')'
   );
   $sth->execute(values %$hash);
}

      [download]
      What's so impossible about that? Of course, you don't have to use a hash ref, you could use a hash - or two arrays, or two array refs. The important thing is that key 3 always points at value 3 - order doesn't matter, and that's why i prefer a hash for this.

      If your queries are still too complex for this, then i recommend you look into SQL abstraction modules such as DBI::Wrap or DBIx::Abstract.

      Also, i only use do() for quick-n-dirty stuff. From the docs: 

      The do() method can be used for non repeated non-SELECT statement
(or with drivers that don't support placeholders) ...
      Best to stick with prepare, but note that if every query is going to be significantly different (different columns, different tables), then you won't gain much from prepare's caching.

      jeffa

      L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)
[reply]
[d/l]
[select]
        Heh... Well, it's funny you should mention a DBI abstraction layer, because that's exactly what I'm working on. I didn't like anything that I already saw out there because it usually included far more than I needed or not nearly enough.

        Mine works pretty well for what I need, but as I'm going back through my code to tidy things up and regain some consistancy across the board, I'm finding that little things like "when to quote a value" are popping up.

        The original way I was handling this back in the day when I was new to SQL and the DBI was to take input and stuff it into one hash and then create another hash where I'd put sql quoted values. Something like this:

        my %formdata = $query->Vars;

        Followed by a per-instance quote() of values as needed. But that was obviously stupid because now I have two sets of the same data that I have to balanc. One for calculations and conditions and manipulation and the other for inserting into the database or performing queries on. And then you run into the problem of "wait, if I do this quote() here, will the value be as up to date as possible? Or will the other version of this value be changed and this will get stale?

        Like I say, this code is a little old. *grin*

        Almost all of my code now uses prepare()->execute()->fetch() or a shorthand equivalent like fetchall_arrayref(). And placeholders almost every chance I get. And I really stay away from do(), if nothing else than it just doesn't "feel" right in most situations.

        It's actually in the conversion of my code to something more acceptable like this that has caused me to run into some logical and syntactic issues like the one this thread was about.

        I finally devised a perfect (for me) solution for implementing my INSERTS for my abstraction layer and I'll post that a little later in this thread. It seems more elegant (to me) than the others I have found in "abstraction layers" for DBI around the net.

        By the way, just so everyone understands, this isn't a big corporate project. This is just a small project of mine that has been running for about five years now. This code is the engine for an approximately 16,000 line auction engine that runs my heavily used auction site (well, about 30,000 accounts). This is a one man non-programmer's endeavor, hence the stupid questions and lack of normal project "preperation". :)
[reply]

In Section Seekers of Perl Wisdom