Using A PDO Prepared Statement On A Search Form Query - PHP

Question

I have some images that are outputted onto a page with PHP from a MySQL database. The while loop that outputs them includes a join because it gets data from two database tables (an imageposts table and a users table).

With the current output a query is run, but because there is no user input it uses the following code:

<?php
// IMAGE GRID JOIN
$stmt = $connection->query("SELECT imgp.*, u.id, u.firstname, u.lastname
FROM imageposts AS imgp
INNER JOIN users AS u ON imgp.user_id = u.id");

while ($row = $stmt->fetch()) {
    // from imageposts table
    $db_image_id = htmlspecialchars($row['image_id']);
    $db_image_title = htmlspecialchars($row['image_title']);
    $db_image_filename = htmlspecialchars($row['filename']);
    $db_ext = htmlspecialchars($row['file_extension']);
    $db_username = htmlspecialchars($row['username']);

    // from users table
    $db_firstname = htmlspecialchars($row['firstname']);
    $db_lastname = htmlspecialchars($row['lastname']);

?>

-- HTML OUTPUT

<?php } ?>

The Issue

I'm also setting up my first search form for the site, and when the images are outputted I want them to pull in the same information as above (in addition to the search query), but because it's a search form and has user input, I'm going to need to use a prepared statement, and I cannot get my head around how to approach this?

The search will take place on the image_title column of the imageposts table, so in terms of sudo code it would be:

$searchSQL = "SELECT * FROM `imageposts` WHERE `image_title` LIKE %$searchQuery% ";

where the $searchQuery is the value of a search input field.

But how do I integrate that with the query in the first section of this question, and also use a prepared statement for security?

Would the MySQL be this?:

"SELECT imgp.*, u.id, u.firstname, u.lastname
FROM imageposts AS imgp
INNER JOIN users AS u ON imgp.user_id = u.id WHERE image_title LIKE %$searchQuery%"

And if so, how do I make it secure in terms of the prepared statement on the $searchQuery?

score 2 · Accepted Answer · answered May 06 '22 at 01:19

Alternative 1:

$sql = "SELECT imgp.*, u.id, u.firstname, u.lastname
  FROM imageposts AS imgp
  INNER JOIN users AS u ON imgp.user_id = u.id 
  WHERE image_title LIKE CONCAT('%', ?, '%')";
$stmt = $connection->prepare($sql);
$stmt->execute([$searchQuery]);

Alternative 2:

$sql = "SELECT imgp.*, u.id, u.firstname, u.lastname
  FROM imageposts AS imgp
  INNER JOIN users AS u ON imgp.user_id = u.id 
  WHERE image_title LIKE ?";
$stmt = $connection->prepare($sql);
$stmt->execute(["%{$searchQuery}%"]);

Using A PDO Prepared Statement On A Search Form Query - PHP

1 Answers1