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/[email protected]