PHP PDO bindParam doesn't modify the query

Question

I have the following code:

if(isset($_GET['q'])){
    $sql = "SELECT klantnr, persnr, naam, voornaam FROM gegevens WHERE voornaam LIKE % :voorwaarde % OR naam LIKE % :voorwaarde %";

    $stmt = $db->prepare($sql);
    $stmt->bindParam(':voorwaarde', $_GET['q']);
    $stmt->bindParam(':voorwaarde', $_GET['q']);
    echo $stmt->queryString;
    $stmt->execute();
    $pers = $stmt->fetchAll();
    print_r($pers);
}

But the query that shows up from the queryString function is still:

SELECT klantnr, persnr, naam, voornaam FROM gegevens WHERE voornaam LIKE % :voorwaarde % OR naam LIKE % :voorwaarde %

Answer 1

If you want to use a LIKE search with PDO, you must be a little bit witty. What PDO does is that it quotes strings. What does that mean for you? It means that your % :term % won't be a valid SQL.

To get the proper SQL out and to correctly clean the input string, use the following:

$sql = "SELECT klantnr, persnr, naam, voornaam FROM gegevens WHERE voornaam LIKE CONCAT('%', :voorwaarde, '%') OR naam LIKE CONCAT('%',  :voorwaarde,  '%')";

What the above does is concatenating the wildcards for LIKE search to your, now clean, search string.