bindParam for a GET-value for LIKE in a PDO MySQL query

Question

Is there any way of converting

$term = $_GET['p'];
$stmt = $dbh->prepare("
    SELECT      *
    FROM        posts
    WHERE       heading LIKE '%$term%'
    OR          full_text LIKE '%$term%'
    LIMIT       0 , 30
");
$stmt->execute();
$answer = $stmt->fetch(PDO::FETCH_ASSOC);

INTO something like

$term = "'%" . $_GET['p'] . "%'";
$stmt = $dbh->prepare("
    SELECT      *
    FROM        posts
    WHERE       heading LIKE :term
    OR          full_text LIKE :term
    LIMIT       0 , 30
");
$stmt->bindParam(":term", $term);
$stmt->execute();
$answer = $stmt->fetch(PDO::FETCH_ASSOC);

So that I can use bindParam(":term", $term); instead of '%$term%' in the query?

I've already looked at these Using LIKE in bindParam for a MySQL PDO Query and Mysql, PDO - Like statement not working using bindParam. But they doesn't give me any proper answer for my question.

It's great you're asking how to escape things properly rather than grab the first thing that appears to work and run with it. Always be extremely wary of putting variables directly into your SQL. — tadman, Aug 21 '12 at 18:03

score 5 · Accepted Answer · answered Aug 21 '12 at 16:56

Concatenate the wildcard symbols to the variable within SQL:

$stmt = $dbh->prepare("
    SELECT      *
    FROM        posts
    WHERE       heading   LIKE CONCAT('%', :term, '%')
    OR          full_text LIKE CONCAT('%', :term, '%')
    LIMIT       0 , 30
");
$stmt->bindParam(':term', $_GET['p']);
$stmt->execute();
$answer = $stmt->fetch(PDO::FETCH_ASSOC);

score 0 · Answer 2 · answered Aug 27 '17 at 23:37

You need only %. Dont use ' (simple queto)

For Example :

$term = "'%" . $_GET['p'] . "%'";
$stmt = $dbh->prepare("
    SELECT      *
    FROM        posts
    WHERE       heading LIKE :term
    OR          full_text LIKE :term
    LIMIT       0 , 30
");
$termWithQueto = "%".$term."%";
$stmt->bindParam(":term", $termWithQueto, PDO::PARAM_STR);
$stmt->execute();
$answer = $stmt->fetch(PDO::FETCH_ASSOC);

bindParam for a GET-value for LIKE in a PDO MySQL query

2 Answers2