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

[Mark Trostler]

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