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

[Mark Trostler]

Oh forgot to mention the final touch - the first & last single quotes:
	$value = "'$value'";
..
	Mark

Mark Trostler wrote:
> the quote() function is quite lame in my experience - for raw SQL I use:
> 
>     # get rid of any backshlased single quotes
>     $value =~ s/\\'/'/g;
> 
>     # Fixup regular single quotes
>     $value =~ s/'/', char(39), '/g;
> 
>     # Get rid of question marks
>     $value =~ s/\?/', char(63), '/g;
> 
> So "What's up" ends up like: 'What', char(39), 's up'
> 
>     Mark
> 
> 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
>>
>> _______________________________________________
>> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
>> IRC: irc.perl.org#dbix-class
>> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
>> Searchable Archive: 
>> http://www.grokbase.com/group/dbix-class@lists.rawmode.org
>>
>>
>