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

[Ash Berlin]

On 14 Mar 2008, at 19:08, Ronald J Kimball wrote:

> 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

Why exactly do you need to have that as part of the join condition  
rather than just in the WHERE clause? If you put as part of the search  
term then you use bind params much easier and let the *database*  
handle the quoting.