Re: [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/[EMAIL PROTECTED]

_______________________________________________
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/[EMAIL PROTECTED]