PDO and MySQL Fulltext searches

by

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:

More Related Contents:

  1. Can I mix MySQL APIs in PHP?
  2. How to apply bindValue method in LIMIT clause?
  3. PDO support for multiple queries (PDO_MYSQL, PDO_MYSQLND)
  4. PDOException “could not find driver”
  5. Row count with PDO
  6. How to prevent duplicate usernames when people register?
  7. PDO get the last ID inserted
  8. How do I use password hashing with PDO to make my code more secure? [closed]
  9. How to view query error in PDO PHP
  10. PDO::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers
  11. Insert/update helper function using PDO
  12. Resetting array pointer in PDO results
  13. pdo prepared statements with wildcards
  14. PDO multiple queries
  15. How to bind parameters to a raw DB query in Laravel that’s used on a model?
  16. Insert multiple rows with PDO prepared statements
  17. PDO::PARAM for type decimal?
  18. Update query with PDO and MySQL
  19. What is the advantage of using try {} catch {} versus if {} else {}
  20. Is “SET CHARACTER SET utf8” necessary?
  21. Can’t find FULLTEXT index matching the column list (indexes is set)
  22. Unknown database error in PHP but it exists in PHPMyAdmin
  23. Set PDO to throw exceptions by default [duplicate]
  24. PDO: MySQL server has gone away
  25. How do detect that transaction has already been started?
  26. Trying to access array offset on value of type bool
  27. How to get a query error from prepare() in PDO PHP?
  28. Checking for an empty result (PHP, PDO, and MySQL) [duplicate]
  29. PHP PDO how to run a multiple query request?
  30. Is a BLOB converted using the current/default charset in MySQL?

Leave a Comment