On Mon, 24 Mar 2003, Paul DuBois wrote:
> At 12:52 +0000 3/24/03, Tim Bunce wrote:
> >On Mon, Mar 24, 2003 at 12:04:13AM -0500, Rudy Lippan wrote:
> > > and '/', '\', and '.' can not be used in a
> >> quoted identifier. Should quote_identifier() thow an error if it gets
> >> data that it cannot quote? -- I think it should, but I just wanted to
> >> some feed back.
> >
> >It's generally best not to second-guess the server...
> >
Yeah, I do not know what I was thinking when I wrote that -- Must have
been late or something :) I agree that the driver should not care about
the what is in a quoted identifier. I think I conflated two issues and
confused myself. The question I should have asked is: do we throw an error
if the client sends up something that cannot be escaped... See below.
> >For table names (which can't contain '.' etc) the server will
> >generate an error if and when appropriate.
>
> The exact rules for MySQL identifiers are:
>
> Database and table names cannot contain '\', '/', or '.' characters.
> Column and index names can.
What are the rules for escaping the quoting character?
create table `this"that` (foo int); -- confuses the mysql client.
create table asdf (`foo'bnar` int); -- confuses mysql client.
create table `asdf``asdf` (foo int); -- throws an error
And
mysql> create table asdf (`foo\`bnar` int);
ERROR:
Unknown command '\`'.
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'bnar` int)
And here without the client:
use DBI;
$dbh = DBI->connect("dbi:mysql:dbname=rlippan","root","", {RaiseError=>1,
PrintE
rror=>0});
my @names = (
q{`foobar`}, q{`Foo'bar`}, q{`foo"bar`}, q{`foo``bar`}, q{`foo\`bar`},
q{`foo\\\\`bar}
);
for (@names) { eval {$dbh->do(qq{ DROP TABLE $_}) } }
for (@names) {
eval {
$dbh->do(qq{
CREATE TABLE $_ (baz int)
});
}; if (my $e = $@) {
print "$_ causes: $e\n";
} else {
print "$_ Works\n";
}
}
And its output is:
`foobar` Works
`Foo'bar` Works
`foo"bar` Works
`foo``bar` causes: DBD::mysql::db do failed: You have an error in your SQL
syntax. Check the manual that corresponds to your MySQL server version
for the right syntax to use near 'bar` (baz int)' at line 1 at quoting.pl
line 14.
`foo\`bar` causes: DBD::mysql::db do failed: You have an error in your SQL
syntax. Check the manual that corresponds to your MySQL server version
for the right syntax to use near 'bar` (baz int)' at line 1 at quoting.pl
line 14.
`foo\\`bar causes: DBD::mysql::db do failed: You have an error in your SQL
syntax. Check the manual that corresponds to your MySQL server version
for the right syntax to use near 'bar (baz int)' at line 1 at quoting.pl
line 14.
=====================
What am I missing?
> >
> >A couple of other items I've remembered recently...
> >
> > - I'd really like to see mysql_client_found_rows=1 being the default.
> >
> > With mysql_client_found_rows off, normal application code can't
> > tell the difference between an update that failed to match a row
> > and one that matched a row but didn't actually change anything.
D'oh. I just wrote something that did that :( I think you just saved me
from having to track down a bug when I start testing that function. Thanks
Tim :)
It looks like the only question is one of backwards compatibility. Maybe
we throw a warning in the next version or two, and change after that?
Rudy.
