Search code examples
phpmysqlpdostripslashes

mysql fulltext stripslashes not working

I want to perform this mysql search : 

 SELECT ida, MotsClef FROM Actes WHERE MATCH (MotsClef ) 
 AGAINST ('+"dette" +"plège"' IN BOOLEAN MODE);

Using php, I use regular expressions to add the +" and " to the expressions received via $_POST so a var_dump gives :

'motcle' => string '+"dette" +"plège"'

So that's fine too. However, I use prepared statements using PDO class and I have this piece of code for that:

 if($r['motcle']!=''){
     $motclef = $r['motcle'];
     $demMotsClef = " AND WHERE MATCH (MotsClef ) AGAINST (:motsclef IN BOOLEAN MODE) ";
    }
    else{
            $demMotsClef='';
    }

than:

 $f = "SELECT COUNT(*) FROM Actes, Bibliographie WHERE id = idBiblio".$demMotsClef;

$demande = $this->prepare($f);

if($r['motcle']!=''){$demande->bindValue(':motsclef',stripslashes($motclef));}

$demande->execute(); //the error is on this line//

I get a MySQL error message saying I have an error in your SQL syntax:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]:
Syntax error or access violation: 1064 You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the 
right syntax to use near 'WHERE MATCH (MotsClef ) AGAINST 
('+\"dette\" +\"plège\"' IN BOOLEAN MODE) AND a' at line 1' in 
/Library/WebServer/Documents/messources/actions.class.php on line 547.

The error in mysql syntax is that the slashes are added, hence the use of stripslashes (doesn't work).

Any idea on how to solve that - I would rather not change ini settings either in php.ini or in a .php function since that would mess up all my other mysql requests.

Thanks!

Solution

  • Ohh, well took me a while to find the error but this is definetly wrong:

    $demMotsClef = " AND WHERE MATCH (MotsClef ) AGAINST (:motsclef IN BOOLEAN MODE) ";

$f = "SELECT COUNT(*) FROM Actes, Bibliographie WHERE id = idBiblio".$demMotsClef;

    If you look at this, you'll have double WHERE, which is not allowed, you should make this change:

    $demMotsClef = " AND MATCH (MotsClef ) AGAINST (:motsclef IN BOOLEAN MODE) ";