DBI, quoting and like

castaway has asked for the wisdom of the Perl Monks concerning the following question:

Hi all,
I was bugging everyone in the CB about parts of this yesterday, and I still can't get it working to my liking, so I'll just post the entire sub, and see if anyone has any suggestions.

The problem:
I have a table (or actually two similar tables, but hopefully thats not relevant - see code), that looks like this:

mud_id  |  Name            |  <Other fields .. >
------------------------------------------------
  1      FinalFrontier        <IP, Port etc>
  2      Timewarp
  3      Timewarp-RENEGADE
  4      Kylere'srealm
  5      MG:test@blah
[download]

The mud_id is unique, and the names should also be unique, at least, I check before adding..
I have a sub which gets passed a string, which can be a complete mud name, or an abbreviation of one, and I'm trying to find either an exact match, or return a list of matches.

Code:

sub getMudName
{
    # Get a name or all names matching or abbreviated by given name
    # Parameter: Object, Name
    my ($dbobj, $type, $name) = @_;

    my $dbh = $dbobj->{$databasehandle};
    $dbobj->{$errormsg} = '';

    my $table;
    if($type eq 'i3')
    {
        $table = 'Intermud3';
    }
    elsif($type eq 'i2')
    {
        $table = 'Intermud2';
    }
    else
    {
        $dbobj->{$errormsg} = "Oops, invalid type: $type\n";
        return undef;
    }

# Tyes fun version: select Name from intermud2 where Name = 'Timewarp'
+ or (0=(select count(*) from intermud2 where Name='Timewarp') and Nam
+e like 'Timewarp%')

#    my $stmS = "SELECT Name FROM $table WHERE Name like '$name%' orde
+r by ";
    my $stmS = "SELECT Name FROM $table WHERE Name like ? order by ";
    $stmS .= "length(Name)";
    $dbobj->debug("getMudName: $stmS\n");
    $stmS = $dbh->prepare($stmS);
#    my $res = $stmS->execute();
    my $res = $stmS->execute($name . '%');
    if(!$res)
    {
        $dbobj->{$errormsg} ="Can't select from $table: " .
            $dbh->errstr() . "\n";
        return undef;
    }
    my $ids = $dbh->selectcol_arrayref($stmS);
    $dbobj->debug(Dumper($ids));
    if(!$ids)
    {
        $dbobj->{$errormsg} = "No Name matching name: " .
            $stmS->err() . "\n";
        return undef;
    }
    if(lc($ids->[0]) eq lc($name))
    {
        return [$ids->[0]];
    }
    return $ids;
}
[download]

This worked wonderfully (see commented out bits) until I tried to make it work for mud names that have a quote in them, by using bound parameters.. If I pass this code 'final' or 'finalfrontier' then it finds nothing at all :(
Any ideas?

20030225 Edit by Corion: Removed runaway bold tag

Comment on DBI, quoting and like - SQLite Select or Download Code

Replies are listed 'Best First'.
Re: DBI, quoting and like - SQLite by bart (Canon) on Feb 25, 2003 at 09:22 UTC
We still have no clue why the origional version refuses to work. But via the ChatterBox, we found another, simpler solution that does work: `$name = "kylere'"; my $sql = "SELECT Name FROM $table WHERE Name like ? order by Name"; # ... or order by whatever, it doesn't matter my $ids = $dbh->selectcol_arrayref($sql, undef, "$name%");` [download]	[reply] [d/l]
Re: Re: DBI, quoting and like - SQLite by castaway (Parson) on Feb 25, 2003 at 09:30 UTC
I've actually just commented out the 'execute' line, and added the parameters to selectcol_arrayref, as bart suggested, which also works. Still no clue as to why. Thanks bart! C.	[reply]
Re: DBI, quoting and like - SQLite by jammin (Novice) on Feb 25, 2003 at 09:38 UTC
The question I have here is how are you setting the var to include the quotes: i.e. $var = '\'final\''; or just $var = 'final'? the latter won't work if the entry starts with a ' and you're doing a '$string%' match. I recommend you use the sql escape of quoting a quote with a quote so before you do a match run `$var =~ s/\'/\'\'/g;` first Good luck	[reply] [d/l]
Re: Re: DBI, quoting and like - SQLite by castaway (Parson) on Feb 25, 2003 at 10:06 UTC
I'm matching against text typed in by the user.. It's read and passed on like this: `imagine IO::Socket, IO::Select code here .. $line = <STDIN>; chomp($line); parse_command($line); .. sub parse_command { my ($data) = @_; .. if($data =~ /^tell (.+?)\@(.+?) (.+)$/) { @args = ($2, 'tell', $1, $3); } .. if(@args) { $i2->send(@args); } } .. sub send { my ($me, $dest, $request, @params) = @_; .. if($dest ne 'all' && !defined($testmud = $me->getMudName($dest))) ... }` [download] Does that answer the question, more or less? ;) C.	[reply] [d/l]
Re: DBI, quoting and like - SQLite by diotalevi (Canon) on Feb 25, 2003 at 13:35 UTC
The mud_id is unique, and the names should also be unique, at least, I check before adding.. That's a race condition - you should assert your "Name" column as unique and let your database handle that. Seeking Green geeks in Minnesota	[reply]
Re: DBI, quoting and like - SQLite by p6steve (Sexton) on Feb 25, 2003 at 19:49 UTC
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);	[reply]
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]
Re: Re: Re: DBI, quoting and like - SQLite by dws (Chancellor) on Feb 26, 2003 at 08:12 UTC
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]