[Dbix-class] Error with escaped apostrophe and bind param

Ronald J Kimball rkimball+dbixclass at pangeamedia.com
Fri Mar 14 19:08:21 GMT 2008


I'm getting an error with the following query, which finds all users who 
have not yet been sent a message from the specified user with the 
specified subject(s).

Due to SQL::Abstract limitations, I'm providing some literal SQL for the 
join clause, which happens to contain an escaped apostrophe.

my $user_id = 43;
my @old_subjects = ("What's up");

my $users =
   $schema->resultset('User')->search(
     { 'user_message.message_id' => undef,
       'me.status' => 1,
     },
     { from => [
                { me => 'user' },
                [ { user_message => 'user_message',
                    -join_type => 'left' },
                  { 'user_message.recip_user_id' => 'me.user_id',
                    'user_message.subject' =>
                      \ ('IN (' .
                         join(', ', map $schema->storage->dbh->quote($_),
                                       @old_subjects) .
                         ')'),
                    'user_message.sender_user_id' => $user_id,
                  },
                ],
               ],
     },
   );

my $count = $users->count;


Error message:

DBIx::Class::ResultSet::count(): DBI Exception: DBD::mysql::st 
bind_param failed: Illegal parameter number [for Statement "SELECT 
COUNT( * ) FROM user me LEFT JOIN user_message user_message ON ( 
user_message.recip_user_id = me.user_id AND user_message.sender_user_id 
= 43 AND user_message.subject IN ('What\'s up') ) WHERE ( me.status = ? 
AND user_message.message_id IS NULL )"]


Without the apostrophe in the message subject, the query works fine.


Is this a known issue?  Are there any workarounds?  (If necessary, I can 
just run this query directly through DBI rather than using DBIC.)

DBIx::Class 0.08007
DBI 1.50
DBD::mysql 3.0002


thanks,
Ronald



More information about the DBIx-Class mailing list