DBD::mysql 3.0006 - More bind problems

Hi all,

I had a problem with bind parameters last year (see http://lists.mysql.com/perl/3588) and had to revert to 2.9008 to keep things working.

We had another upgrade cycle and tried to go to 3.0006. The original problem seems to have been fixed, but we ran in to a new similar problem that has once again made us downgrade to 2.9008 just to keep things running.

Basically, if a query has a bind parameter and a string with '? (single-quote, question-mark) in it in a particular order, DBD::mysql seems to think the '? actually means single-quote, bind-parameter. This of course breaks the query.

To show this, make a table with the specs:

CREATE TABLE test (
test1 varchar(128) not null,
test2 varchar(128) not null,
test3 varchar(128) not null);

And then run (filling in necessary login details):

use DBI;
use DBI::mysql;

my $prob = "'?"; # Problem string
my $ok = "Something mundane"; # OK string
my $qmark = "Something else mundane"; # Anoter mundane string

$| = 1;
print "DBI VERSION: ", $DBI::VERSION, "\n";
print "DBD::mysql VERSION: ", $DBD::mysql::VERSION, "\n";

my $dsn = "DBI:mysql:database=DB;host=HOST"; # FILL ME IN!
my $dbh = DBI->connect($dsn, UNAME, PASSWORD, # FILL ME IN!
{RaiseError => 0,
PrintError => 1});
for my $order ([$ok, $prob, $qmark], [$ok, $qmark, $prob],
[$qmark, $prob, $ok], [$qmark, $ok, $prob],
[$prob, $ok, $qmark], [$prob, $qmark, $ok]) {
my ($ok, $qmark, $prob) = @$order;
my $query = ("INSERT INTO test VALUES (" . $dbh->quote($ok) . ", ?, " .
$dbh->quote($prob) . ")");
print "================\nQUERY IS $query\nERROR IS ";
my $sth = $dbh->prepare($query);
$sth->execute($qmark);
print "\n================\n\n";
}
__END__

Using DBD::mysql 2.9008 gives the correct output:

DBI VERSION: 1.51
DBD::mysql VERSION: 2.9008
================
QUERY IS INSERT INTO test VALUES ('Something mundane', ?, 'Something else mundane')
ERROR IS
================

================
QUERY IS INSERT INTO test VALUES ('Something mundane', ?, '\'?')
ERROR IS
================

================
QUERY IS INSERT INTO test VALUES ('Something else mundane', ?, 'Something mundane')
ERROR IS
================

================
QUERY IS INSERT INTO test VALUES ('Something else mundane', ?, '\'?')
ERROR IS
================

================
QUERY IS INSERT INTO test VALUES ('\'?', ?, 'Something else mundane')
ERROR IS
================

================
QUERY IS INSERT INTO test VALUES ('\'?', ?, 'Something mundane')
ERROR IS


But DBD::mysql 3.0006 does this:

DBI VERSION: 1.51
DBD::mysql VERSION: 3.0006
================
QUERY IS INSERT INTO test VALUES ('Something mundane', ?, 'Something else mundane')
ERROR IS
================

================
QUERY IS INSERT INTO test VALUES ('Something mundane', ?, '\'?')
ERROR IS DBD::mysql::st execute failed: called with 1 bind variables when 2 are
needed at test.pl line 27.

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

================
QUERY IS INSERT INTO test VALUES ('Something else mundane', ?, 'Something mundane')
ERROR IS
================

================
QUERY IS INSERT INTO test VALUES ('Something else mundane', ?, '\'?')
ERROR IS DBD::mysql::st execute failed: called with 1 bind variables when 2 are
needed at test.pl line 27.

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

================
QUERY IS INSERT INTO test VALUES ('\'?', ?, 'Something else mundane')
ERROR IS
================

================
QUERY IS INSERT INTO test VALUES ('\'?', ?, 'Something mundane')
ERROR IS
================

It seems string processing becomes broken once a real bind placeholder has been found.

Hopefully this will be fixed soon. Especially since the latest actually-working version I can find is over a year old.

MB



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

RE: DBD::mysql 3.0006 - More bind problems

Mathew,

You can see why this happens from the DBI_TRACE:

QUERY IS INSERT INTO test VALUES ('Something mundane', ?, '\'?')

Here DBD::mysql finds 2 parameters.

The code at fault is count_params() which attempts to skip strings but the
sequence

'\'?'

looks like a string '\' and ?' because it does not skip the chr following the \
properly.

Changing count_params() in dbdimp.c to the code at the end of this email should
fix it.

Alternatively, you can just skip the bug by adding

mysql_server_prepare => 1

to the attributes passed in your DBI connect call (but this has other
implications - see DBD::mysql pod for details).

I've cc'ed Patrick Galbraith who looks after DBD::mysql.

Sorry Patrick, I've not time right now to produce a patch but it is only really
a 1 or 2 liner change. I also only did a few quick tests - not a thorough
testing.

Replacement count_params():

static int
count_params(char *statement)
{
char* ptr = statement;
int num_params = 0;
char c;

while ( (c = *ptr++) )
{
switch (c) {
case '`':
case '"':
case '\'':
/* Skip string */
{
/* Loop until matching quote found */
char end_token = c;
while ((c = *ptr) && c != end_token)
{
/* this is the code changed */
++ptr;
if (c == '\\') {
if (*ptr) {
++ptr;
}
}

}
/* end of code changed */
if (c)
++ptr;
break;
}

case '?':
++num_params;
break;

default:
break;
}
}
return num_params;
}

Hope this helps.

Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com



On 21-Jul-2006 Matthew Braid wrote:
> Hi all,
>
> I had a problem with bind parameters last year (see
> http://lists.mysql.com/perl/3588) and had to revert to 2.9008 to keep things
> working.
>
> We had another upgrade cycle and tried to go to 3.0006. The original problem
> seems to have been fixed, but we ran in to a new similar problem that has
> once again made us downgrade to 2.9008 just to keep things running.
>
> Basically, if a query has a bind parameter and a string with '?
> (single-quote, question-mark) in it in a particular order, DBD::mysql seems
> to think the '? actually means single-quote, bind-parameter. This of course
> breaks the query.
>
> To show this, make a table with the specs:
>
> CREATE TABLE test (
> test1 varchar(128) not null,
> test2 varchar(128) not null,
> test3 varchar(128) not null);
>
> And then run (filling in necessary login details):
>
> use DBI;
> use DBI::mysql;
>
> my $prob = "'?"; # Problem string
> my $ok = "Something mundane"; # OK string
> my $qmark = "Something else mundane"; # Anoter mundane string
>
> $| = 1;
> print "DBI VERSION: ", $DBI::VERSION, "\n";
> print "DBD::mysql VERSION: ", $DBD::mysql::VERSION, "\n";
>
> my $dsn = "DBI:mysql:database=DB;host=HOST"; # FILL ME IN!
> my $dbh = DBI->connect($dsn, UNAME, PASSWORD, # FILL ME IN!
> {RaiseError => 0,
> PrintError => 1});
> for my $order ([$ok, $prob, $qmark], [$ok, $qmark, $prob],
> [$qmark, $prob, $ok], [$qmark, $ok, $prob],
> [$prob, $ok, $qmark], [$prob, $qmark, $ok]) {
> my ($ok, $qmark, $prob) = @$order;
> my $query = ("INSERT INTO test VALUES (" . $dbh->quote($ok) . ", ?, " .
> $dbh->quote($prob) . ")");
> print "================\nQUERY IS $query\nERROR IS ";
> my $sth = $dbh->prepare($query);
> $sth->execute($qmark);
> print "\n================\n\n";
> }
> __END__
>
> Using DBD::mysql 2.9008 gives the correct output:
>
> DBI VERSION: 1.51
> DBD::mysql VERSION: 2.9008
> ================
> QUERY IS INSERT INTO test VALUES ('Something mundane', ?, 'Something else
> mundane')
> ERROR IS
> ================
>
> ================
> QUERY IS INSERT INTO test VALUES ('Something mundane', ?, '\'?')
> ERROR IS
> ================
>
> ================
> QUERY IS INSERT INTO test VALUES ('Something else mundane', ?, 'Something
> mundane')
> ERROR IS
> ================
>
> ================
> QUERY IS INSERT INTO test VALUES ('Something else mundane', ?, '\'?')
> ERROR IS
> ================
>
> ================
> QUERY IS INSERT INTO test VALUES ('\'?', ?, 'Something else mundane')
> ERROR IS
> ================
>
> ================
> QUERY IS INSERT INTO test VALUES ('\'?', ?, 'Something mundane')
> ERROR IS
>
>
> But DBD::mysql 3.0006 does this:
>
> DBI VERSION: 1.51
> DBD::mysql VERSION: 3.0006
> ================
> QUERY IS INSERT INTO test VALUES ('Something mundane', ?, 'Something else
> mundane')
> ERROR IS
> ================
>
> ================
> QUERY IS INSERT INTO test VALUES ('Something mundane', ?, '\'?')
> ERROR IS DBD::mysql::st execute failed: called with 1 bind variables when 2
> are
> needed at test.pl line 27.
>
> ================
>
> ================
> QUERY IS INSERT INTO test VALUES ('Something else mundane', ?, 'Something
> mundane')
> ERROR IS
> ================
>
> ================
> QUERY IS INSERT INTO test VALUES ('Something else mundane', ?, '\'?')
> ERROR IS DBD::mysql::st execute failed: called with 1 bind variables when 2
> are
> needed at test.pl line 27.
>
> ================
>
> ================
> QUERY IS INSERT INTO test VALUES ('\'?', ?, 'Something else mundane')
> ERROR IS
> ================
>
> ================
> QUERY IS INSERT INTO test VALUES ('\'?', ?, 'Something mundane')
> ERROR IS
> ================
>
> It seems string processing becomes broken once a real bind placeholder has
> been found.
>
> Hopefully this will be fixed soon. Especially since the latest
> actually-working version I can find is over a year old.
>
> MB

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org