0

I have a search feature on my website, which permits to users to define keywords to be searched. Search processing occurs once a day, so the keywords are stored in a table.

The solution I was using until now is to store the keywords as a SQL query:

+---------+--------------------------------------------+------+
| user_id | subquery                                   | type |
+---------+--------------------------------------------+------+
|    1    | desc LIKE '%test1%' OR desc LIKE '%test1%' | buy  |
+---------+--------------------------------------------+------+

(subquery column can content as much as keywords the user wants)

Then perform the research by doing:

$req = "SELECT user_id, subquery, type FROM table";
$res = $pdo->prepare($req);
$res->execute();

while ($rows = $res->fetch(PDO::FETCH_ASSOC)) {
   $req2 = "SELECT * FROM items_tbl WHERE (".$rows['subquery'].") and type = '".$rows['type']."'";
   $res2 = $pdo->prepare($req2);
   $res2->execute();
   while($rows2 = $res2->fetch(PDO::FETCH_ASSOC)) {
      Do stuff...
   }
}

I would like now to use a prepared statement to catch the exceptions, so I have tried:

while ($rows = $res->fetch(PDO::FETCH_ASSOC)) {
   $req2 = "SELECT * FROM items_tbl WHERE (:subquery) and type=:type";
   $res2 = $pdo->prepare($req2);
   $res2->execute(array('subquery' => $rows['subquery'], 'type' => $rows['type']));
   while($rows2 = $res2->fetch(PDO::FETCH_ASSOC)) {
      Do stuff...
   }
}

It returns nothing, WHERE (:subquery) is not filled by the appropriate content.

What is the correct approach for doing that?

Jibeji
  • 453
  • 4
  • 14
  • 2
    Placeholders can only be used for parameter values, not entire pieces of the query. You should probably be storing the subqueries with placeholders (e.g. `desc LIKE '%?%' OR desc LIKE '%?%'`) and then store the params for the subqueries in a separate table. – El_Vanja Feb 01 '21 at 11:41
  • 2
    Rather than storing literal SQL in your table, just store some info which your code can then use to build the finished SQL. Then you can use that to gradually build a SQL string which contains the correct parameterisation. – ADyson Feb 01 '21 at 11:49
  • 2
    Why on earth would you mix prepared statements with concatenating strings? Take the time to read the documentation, specially when its to deal with **security**, then you may be able to even debug this better... – Jaquarh Feb 01 '21 at 11:50

0 Answers0