Geek Answers Handbook

Perl DBI (MySQL) puts a single quote in place of the actual parameter in the prepared message

I try to make a simple query as a prepared statement, but have not succeeded. 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);

MySQL server responds with syntax error near ''' .

The output of the DBI trace is displayed

  -> 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, it seems that the statement is not being prepared as it should. When I submit a request without a parameter, it works as expected.

What am I missing here?

DBI version DBI DBI 1.637-ithread , MySQL version 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 a problem when using Catalyst with Catalyst :: Plugin :: Session :: Store :: DBIC . Here id-column is a Varchar (72) type that contains a session identifier.

Edit2:

  • DBD :: mysql version 4.043
  • Binding with $sth->execute('session:foo'); leads to the same problem
  • Binding via $sth->bind_param('session:foo', SQL_VARCHAR); leads to the same problem
  • Binding a number field works, but only with an explicit type definition of $sth->bind_param(1, 1512407082, SQL_INTEGER);

Edit3:
I took the time to do some more tests, but not with satisfactory results:

  • I was able to test the older server and it worked. The versions of DBI and DBD :: mysql are the same, but I found a server using the MySQL 5.5 client that was specified in the DBI MYSQL_VERSION_ID 50557 as MYSQL_VERSION_ID 50557 , while both of 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! Perhaps this helps someone who finds this q. But I would now prefer the real cause of the problem.
+8
mysql perl dbi prepared-statement
source share
1 answer

After some testing, I came to the conclusion that this is a compatibility issue between DBD :: mysql and MySQL 5.7 (and / or MySQL 5.5).

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

  • upgrade to MySQL 5.6 as described here . It is enough for me to install libmysqlclient-dev without a server / client
  • reinstall DBD :: MySQL sudo cpanm --reinstall DBD::mysql so that it will be created with MySQL 5.6 installed

I will write an error in DBD :: mysql GitHub and update this answer if there is any news on this issue.

An alternative solution that also helped me:
let the server prepare your expression $dbh->{mysql_server_prepare} = 1;

+3
source share

More articles:


All Articles