Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number

Re: DBI, quoting and like - SQLite

by p6steve (Sexton)
on Feb 25, 2003 at 19:49 UTC ( #238547=note: print w/replies, xml ) Need Help??

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) . " .. ";
    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).


      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'";
      Note the extra enclosing quotes. Given the way you're building your query, this isn't what you want.

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (2)
As of 2022-05-24 06:11 GMT
Find Nodes?
    Voting Booth?
    Do you prefer to work remotely?

    Results (82 votes). Check out past polls.