Perl DBI (MySQL) puts single quote instead of actual parameter in prepared statement

Question

I'm trying to do a simple query as prepared statement but have no success. Here is the code:

package sqltest;
use DBI;

DBI->trace(2);

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

The MySQL server responds with a syntax error near '''.

The output of the DBI-trace shows

  -> 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

So to me, it looks like the statement does not get prepared as it should. When I send the query without the parameter it works as expected.

What do I miss here?

DBI version is DBI 1.637-ithread, MySQL version is 5.5.57-0+deb8u1

Tested with Windows perl 5, version 26, subversion 1 (v5.26.1) built for MSWin32-x86-multi-thread-64int
and Ubuntu perl 5, version 22, subversion 1 (v5.22.1) built for x86_64-linux-gnu-thread-multi

Edit1:
for context: I noticed the problem while using Catalyst with Catalyst::Plugin::Session::Store::DBIC. Here, the id-column is a Varchar(72) type, that holds a session-id.

Edit2:

• DBD::mysql version is 4.043
• Binding via $sth->execute('session:foo'); results in the same problem
  
• Binding via $sth->bind_param('session:foo', SQL_VARCHAR); results in the same problem
  
• Binding a numeric field does work, but only with explicit type definition $sth->bind_param(1, 1512407082, SQL_INTEGER);

Edit3:
I found the time to do some more testing, but not with no satisfying results:

• I was able to test with an older server and it worked. The versions of DBI and DBD::mysql are the same, but I found the server using MySQL 5.5 client, reported in the DBI-trace as MYSQL_VERSION_ID 50557, whereas both my original test servers using MySQL 5.7 MYSQL_VERSION_ID 50720 and MYSQL_VERSION_ID 50716
• with $dbh->{mysql_server_prepare} = 1;it works! Maybe this helps someone who finds this q., but I would rather now the real cause of the problem

Answer 1

From your trace log can be seen that question mark placeholder (?) was replaced by DBD::mysql by one apostrophe ('). So it is pure DBD::mysql bug. At the first glance it does not make sense at all... as placeholder is replaced by parameter which is put into two apostrophes.

Relevant code which is doing this placeholder replacement can be found there: https://metacpan.org/source/MICHIELB/DBD-mysql-4.043/dbdimp.c#L784-786

          *ptr++ = '\'';
          ptr += mysql_real_escape_string(sock, ptr, valbuf, vallen);
          *ptr++ = '\'';

So question is, can above C code result in just one apostrophe in *ptr buffer? And answer is yes, when mysql_real_escape_string() returns integer of same value as pointer size minus one -- to simulate numeric operation of decrement by one, when both apostrophes are written to same position in *ptr buffer.

And can this happen? Yes, it can, because Oracle changed API of mysql_real_escape_string() C function in MySQL 5.7.6 client library:

https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-6.html#mysqld-5-7-6-feature

Incompatible Change: A new C API function, mysql_real_escape_string_quote(), has been implemented as a replacement for mysql_real_escape_string() because the latter function can fail to properly encode characters when the NO_BACKSLASH_ESCAPES SQL mode is enabled. In this case, mysql_real_escape_string() cannot escape quote characters except by doubling them, and to do this properly, it must know more information about the quoting context than is available. mysql_real_escape_string_quote() takes an extra argument for specifying the quoting context. For usage details, see mysql_real_escape_string_quote().

And documentation for mysql_real_escape_string() from MySQL 5.7.6 version says:

https://dev.mysql.com/doc/refman/5.7/en/mysql-real-escape-string.html

Return Values: The length of the encoded string that is placed into the to argument, not including the terminating null byte, or -1 if an error occurs.

Therefore if you enable NO_BACKSLASH_ESCAPES SQL mode on you MySQL server, then mysql_real_escape_string() from MySQL 5.7.6 client cannot work and return error, therefore -1 casted to unsigned long. unsigned long is on both 32bit and 64bit x86 platform of same size as pointer, therefore above C code from DBD::mysql drivers results in one apostrophe character.

Now I fixed this problem for DBD::MariaDB driver (fork of DBD::mysql) in following pull request: https://github.com/gooddata/DBD-MariaDB/pull/77

So DBD::MariaDB would be compatible also when compiled with MySQL 5.7 client library.

Answer 2

After some testing, I came to the conclusion that this seems to be a compatibility problem between DBD::mysql and MySQL client 5.7 (and/or MySQL server 5.5).

At least, I found a solution for Ubuntu 16 (xenial), so for others, that may run into the same issue:

• downgrade to MySQL 5.6 like described here. For me, installing libmysqlclient-dev without the server/client was sufficient
• reinstall DBD::MySQL sudo cpanm --reinstall DBD::mysql, so that it gets build with the now installed MySQL 5.6

I will file an issue at the DBD::mysql GitHub and will update this answer, if there are any news regarding this problem.

---

An alternative solution, that also worked for me:
let the server prepare your statement $dbh->{mysql_server_prepare} = 1;