in reply to DBI, quoting and like - SQLite

Hmmm...until I read this I would swear that variable binding was the neatest & surest way to handle all manner of quotes in fields. Certainly there is a lot of flakiness around DBI quote handling - so maybe there is some database dependency in what works where. You could try using the $quoted_string = $dbh->quote($string); method which should provide dialect neutral quote handling. I guess this is what DBI uses internally so it may give you an insight into what is happening.

You say that the failing examples are in the form 'text' - I would guess that quote returns 'text' when you pass it "'text'" because mostly that is what people want and that you really want '''text''' because you want to preserve leading & trailing single quotes.

So once you have checked that I do not lie, using quote. Then you could special case this with an re...

$name =~ s/^\'(*.)\'$/'''$1'''/ or $dbh->quote($name);

Replies are listed 'Best First'.
Re: Re: DBI, quoting and like - SQLite
by castaway (Parson) on Feb 26, 2003 at 07:26 UTC
    There seems to be a bit of confusion here.. I'm usually typing and passing values which do not contain any quotes, so to match "FinalFrontier" I just type "final" - without the quotes. The only time I need to match quotes are when they are contained within the word. (See example table above.)

    I did already try DBI->quote(), just by putting it in the middle of my statement like so:

    my $stmS = "SELECT .. WHERE Name = " . DBI->quote($name) . " .. ";

    [download]
    But that also didn't work, I figured it should do the same as binding parameters anyway..
    As bart worked out, it seems to be a problem with how I used prepare, execute and selectcol_arrayref, which works without bound params, but not with them. (I dont really need the execute anyway.. that comes of replacing fetchall_arrayref with selectcol_arrayref, and not checking the rest).

    C.

[reply]
[d/l]
      I did already try DBI->quote() ...

      This is problematic on two counts:

      First, DBI/DBD will quote for you automatically if you use query parameters and pass the value to execute(). This is the prefered way to go.

      Second, if you really must quote manually, you're generally better using $dbh->quote, which is driver-specific. (You'll get the driver-specific quote() when you use parameter binding.) The form you're using is generic. It basically does 

          $str =~ s/'/''/g;        # ISO SQL2
    return "'$str'";

      [download]
      Note the extra enclosing quotes. Given the way you're building your query, this isn't what you want.

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom