PDO and MySQL Fulltext searches

This is unfortunately a weird exception to the use of query parameters (edit: but apparently not in the most recent point-release of each MySQL branch, see below).

The pattern in AGAINST() must be a constant string, not a query parameter. Unlike other constant strings in SQL queries, you cannot use a query parameter here, simply because of a limitation in MySQL.

To interpolate search patterns into queries safely, use the PDO::quote() function. Note that PDO's quote() function already adds the quote delimiters (unlike mysql_real_escape_string()).

$quoted_search_text = $this->db->quote('+word +word');

$sql = $this->db->prepare("SELECT ... FROM search_table 
    WHERE MATCH(some_field) AGAINST($quoted_search_text IN BOOLEAN MODE");

Re comment from @YourCommonSense:

You're right, I just tested this on MySQL 5.5.31, 5.1.68, and 5.0.96 (MySQL Sandbox is a wonderful tool), and it seems that these versions do accept query parameters in the AGAINST() clause of a dynamic SQL query.

I still have a recollection of a conflict existing in the past. Maybe it has been corrected in the most recent point-release of each branch. For example, I find these related bugs:

Using a stored procedure parameter in the AGAINST() clause always returns the same result: http://bugs.mysql.com/bug.php?id=3734
Crash or strange results with prepared statement, MATCH and FULLTEXT: http://bugs.mysql.com/bug.php?id=14496

PDO and MySQL Fulltext searches

Related

Recent Posts