Apostrophie problem(s)

The code below creates a database, then reads it but it fails when I try to match an apostrophied name.

Any suggestions as to where I am going wrong would be much appreciated.



Owen

============================================================ ===========


#!/usr/bin/perl -w

use strict;
use DBI;
my $dbh = DBI->connect("dbi:SQLite:tennisclub.db", "", "", {RaiseError => 1, AutoCommit => 1});
eval { local $dbh->{PrintError} = 0; $dbh->do("DROP TABLE tennisclub");};

$dbh->do("CREATE TABLE tennisclub (id INTEGER PRIMARY KEY, last)");
my $sth = $dbh->prepare(q{INSERT INTO tennisclub (id,last) VALUES(NULL, ?)}) or die $dbh->errstr;

while (){
chomp;
my $last = $_;
$last = uc($last);
# $last = quotemeta($last); print "\$last after quotemeta $last\n";
$sth->execute($last) or die $dbh->errstr;
}

$dbh = DBI->connect("dbi:SQLite:tennisclub.db", "", "");
my $SQL;
my $cursor;
my @rec;

my $name = "o'jones" ;print "Name as entered $name\n";
#$name =~ s/\'/\\\'/; print "After escaping $name\n";
$name = uc($name); print "After upper casing $name\n";

$SQL = "select * FROM tennisclub";
$cursor = $dbh->prepare($SQL);
$cursor->execute();

# This works

while(@rec = $cursor->fetchrow_array){
print "Name is $rec[1]\n";# print the last name
}

# This produces an error
#$SQL = qq{SELECT FROM tennisclub WHERE last LIKE '$name'};
$SQL = "select * FROM tennisclub where last LIKE '%$name%' ";
$cursor = $dbh->prepare($SQL);
$cursor->execute();

while(@rec = $cursor->fetchrow_array){
print "$rec[1]\n";# print the last name
}

__DATA__
O'Jones
Smith
Brown

Re: Apostrophie problem(s)

First, in SQLite, single quotes in strings are not escaped. They are
instead doubled. For example,
O'Jones
should be the SQL string
'O''Jones'
(that's standard SQL behavior). It MySQL that makes it different with quotes.

Second, if you really need to quote a string to be interpolated to a
SQL, you should use the method 'quote' of the database handler.

$quoted_string = $dbh->quote($string); # as the DBI documentation says

(In this case, a SQLite handle will quote "O'Jones" as "'O''Jones'",
while a MySQL handle would answer "'O\\'Jones'".)

Third, you probably will do better by using SQL statements with
placeholders that take care of all these subtleties for you.

Regards,
Adriano Ferreira

On 9/9/06, Owen wrote:
> The code below creates a database, then reads it but it fails when I try to match an apostrophied name.
>
> Any suggestions as to where I am going wrong would be much appreciated.
>
>
>
> Owen
>
> ============================================================ ===========
>
>
> #!/usr/bin/perl -w
>
> use strict;
> use DBI;
> my $dbh = DBI->connect("dbi:SQLite:tennisclub.db", "", "", {RaiseError => 1, AutoCommit => 1});
> eval { local $dbh->{PrintError} = 0; $dbh->do("DROP TABLE tennisclub");};
>
> $dbh->do("CREATE TABLE tennisclub (id INTEGER PRIMARY KEY, last)");
> my $sth = $dbh->prepare(q{INSERT INTO tennisclub (id,last) VALUES(NULL, ?)}) or die $dbh->errstr;
>
> while (){
> chomp;
> my $last = $_;
> $last = uc($last);
> # $last = quotemeta($last); print "\$last after quotemeta $last\n";
> $sth->execute($last) or die $dbh->errstr;
> }
>
> $dbh = DBI->connect("dbi:SQLite:tennisclub.db", "", "");
> my $SQL;
> my $cursor;
> my @rec;
>
> my $name = "o'jones" ;print "Name as entered $name\n";
> #$name =~ s/\'/\\\'/; print "After escaping $name\n";
> $name = uc($name); print "After upper casing $name\n";
>
> $SQL = "select * FROM tennisclub";
> $cursor = $dbh->prepare($SQL);
> $cursor->execute();
>
> # This works
>
> while(@rec = $cursor->fetchrow_array){
> print "Name is $rec[1]\n";# print the last name
> }
>
> # This produces an error
> #$SQL = qq{SELECT FROM tennisclub WHERE last LIKE '$name'};
> $SQL = "select * FROM tennisclub where last LIKE '%$name%' ";
> $cursor = $dbh->prepare($SQL);
> $cursor->execute();
>
> while(@rec = $cursor->fetchrow_array){
> print "$rec[1]\n";# print the last name
> }
>
> __DATA__
> O'Jones
> Smith
> Brown
>
>

RE: Apostrophie problem(s)

------_=_NextPart_001_01C6D527.B652754D
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Owen wrote:
> The code below creates a database, then reads it but it fails when I
> try to match an apostrophied name.
>=20
> Any suggestions as to where I am going wrong would be much
> appreciated.
>=20
> $SQL =3D "select * FROM tennisclub where last LIKE '%$name%' ";
> $cursor =3D $dbh->prepare($SQL);
> $cursor->execute();

Since the percent signs in the LIKE clause are part of a string, you =
should
send that entire string operand as a bind parameter.

Untested code, but hopefully it'll get the idea across:

$SQL =3D "select * from tennisclub where last like ? escape ?";
$cursor =3D $dbh->prepare($SQL);

# '%', and '_' are special characters for the LIKE operator.
# Put a backslash ahead of each one, and backslashes too.
(my $escaped =3D $name) =3D~ s/([\\%_])/\\$1/g;

# include the literal %s in the like operand.
$cursor->execute("%$escaped%", '\');

Philip



------_=_NextPart_001_01C6D527.B652754D--

Re: Apostrophie problem(s)

Just DO NOT place values into SQL commands, use placeholders. It makes
reusing SQL statements easier, it avoids all kinds of quoting issues,
reduces the lines of code you need to write, and (in a web environment)
avoids SQL injection, a common path for all kinds of attacks.

Your Code:

$SQL = "select * FROM tennisclub where last LIKE '%$name%' ";
$cursor = $dbh->prepare($SQL);
$cursor->execute();

Better:

$cursor=$dbh->prepare("select * from tennisclub where last like ?");
$cursor->execute('%'.$name.'%');

Imagine someone querying your database for the following $name:

x%' or 1=1 or last like '%x

The result is the entire tennisclub table when using your code, an empty
result set when using placeholders (unless someone has a REALLY strange
name ...).

Imagine the following:

x%'; delete from tennisclub where 1=1 or last like '%x

Perhaps this does not work with all databases, but one or two might kill
your entire tennisclub.

Alexander

On 10.09.2006 04:52, Owen wrote:

>The code below creates a database, then reads it but it fails when I try to match an apostrophied name.
>
>Any suggestions as to where I am going wrong would be much appreciated.
>
>
>
>Owen
>
>=========================================================== ============
>
>
>#!/usr/bin/perl -w
>
>use strict;
>use DBI;
> my $dbh = DBI->connect("dbi:SQLite:tennisclub.db", "", "", {RaiseError => 1, AutoCommit => 1});
> eval { local $dbh->{PrintError} = 0; $dbh->do("DROP TABLE tennisclub");};
>
> $dbh->do("CREATE TABLE tennisclub (id INTEGER PRIMARY KEY, last)");
> my $sth = $dbh->prepare(q{INSERT INTO tennisclub (id,last) VALUES(NULL, ?)}) or die $dbh->errstr;
>
> while (){
> chomp;
> my $last = $_;
> $last = uc($last);
> # $last = quotemeta($last); print "\$last after quotemeta $last\n";
> $sth->execute($last) or die $dbh->errstr;
>}
>
>$dbh = DBI->connect("dbi:SQLite:tennisclub.db", "", "");
>my $SQL;
>my $cursor;
>my @rec;
>
>my $name = "o'jones" ;print "Name as entered $name\n";
>#$name =~ s/\'/\\\'/; print "After escaping $name\n";
>$name = uc($name); print "After upper casing $name\n";
>
>$SQL = "select * FROM tennisclub";
>$cursor = $dbh->prepare($SQL);
>$cursor->execute();
>
># This works
>
>while(@rec = $cursor->fetchrow_array){
>print "Name is $rec[1]\n";# print the last name
>}
>
># This produces an error
>#$SQL = qq{SELECT FROM tennisclub WHERE last LIKE '$name'};
>$SQL = "select * FROM tennisclub where last LIKE '%$name%' ";
>$cursor = $dbh->prepare($SQL);
>$cursor->execute();
>
>while(@rec = $cursor->fetchrow_array){
>print "$rec[1]\n";# print the last name
>}
>
>__DATA__
>O'Jones
>Smith
>Brown
>
>
>


--
Alexander Foken
mailto:alexander@foken.de http://www.foken.de/alexander/