Prepared Statements failing with client version 5.7 and server version <=5.6

[p-jahn]

trafficstars

Hi, I encountered an issue while moving a project to Ubuntu 16, which installs MySQL components in version 5.7. When using a prepared statement, the MySQL-server respons with a syntax error near '''. It seems, that (without mysql_server_prepare) the parameter marker ? gets replaces by a single quote instead of the actual value. After some testing, the problem boiled down to the combination of using DBD-mysql linked with MySQL client 5.7 and the server side using MySQL 5.6 or lower (tested with 5.6 and 5.5).

This is my test code:

package sqltest;
use DBI;

DBI->trace(2);

my $dbh = DBI->connect('dbi:mysql:database=test;host=***;port=3306','the_username', '****');
$dbh->{RaiseError} = 1;
my $prep = 'SELECT me.id, me.session_data, me.expires FROM sys_session me WHERE me.id = ?';
my $sth = $dbh->prepare($prep);
$sth->bind_param(1, 'session:06b6d2138df949524092eefc066ee5ab3598bf96');
$sth->execute;
DBI::dump_results($sth);

and it results in the trace:

  -> bind_param for DBD::mysql::st (DBI::st=HASH(0x21e35cc)~0x21e34f4 1 'session:06b6d2138df949524092eefc066ee5ab3598bf96') thr#3ccdb4
 Called: dbd_bind_ph
  <- bind_param= ( 1 ) [1 items] at perl_test_dbi_params.pl line 10
[...]
>parse_params statement SELECT me.id, me.session_data, me.expires FROM sys_session me WHERE me.id = ?
Binding parameters: SELECT me.id, me.session_data, me.expires FROM sys_session me WHERE me.id = '
[...]
DBD::mysql::st execute 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 ''' at line 1

I'm not entirely sure, if this is really a bug or just some config problem. For reference: prior to this report, I asked for help on Stackoverflow and found a solution (downgrading MySQL to 5.6), but not the root cause.

[pali]

Hello, please provide full output of DBI trace and include also MySQL server version, MySQL client version, DBI version and DBD::mysql version.

When mysql_server_prepare is not enabled, then replacement of '?' by parameters is done by DBD::mysql code (and not by MySQL client library or MySQL server). Therefore this looks like some memory corruption in DBD::mysql.

[CaptTofu]

I have a fix I'll be getting out this week. Details to follow after some testing.

[pali]

I wrote details about this problem to stackoverflow: https://stackoverflow.com/a/50933703/9963391

[pali]

Fixed in DBD::MariaDB driver which is now cpan: http://metacpan.org/pod/DBD::MariaDB