| Subject: | Prepare statement broken if using MATCH ... AGAINST with escaped single quote |
We have a problem with the prepare statement. If we use a quoted ' in a MATCH ... AGAINST select, a following 'and id=?' is ignored. See the following code snipped:
This fails:
<snip>
use DBI;
my $dsn = "DBI:mysql:database=test;host=localhost";
my $dbh = DBI->connect($dsn, "root");
$dbh->do("DROP TABLE IF EXISTS articles");
$dbh->do(qq{
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
)
});
$dbh->do(qq{ INSERT INTO articles set title="this is a 'test'",body="name is O'test" });
my $sth = $dbh->prepare("SELECT * FROM articles WHERE MATCH(title,body) AGAINST('O\\\'test') and id=?");
my $rs = $sth->execute(1);
</snip>
=> DBD::mysql::st execute failed: called with 1 bind variables when 0 are needed at test2_mysql.pl line 17.
If I change the last lines to:
<snip>
my $sth = $dbh->prepare("SELECT * FROM articles WHERE id=? and MATCH(title,body) AGAINST('O\\\'test')");
my $rs = $sth->execute(1);
</snip>
the script runs fine.