-1

I have a regular query working for the search function I'm implementing but I'm trying to implement it now by using prepared statements. I have used prepared statements before just fine but never with wildcards.

I have tried a lot of the possible solutions I have found on this site like: using CONCAT('%',?,'%) or $searchterm = "%{$searchterm}%"; or using get_result() instead of bind_result and just can't seem to get it working.

$searchtype=$_POST['searchtype'];
$searchterm=$_POST['searchterm'];

$stmt = $dbconn->prepare("SELECT name, price, id, img FROM product WHERE ? LIKE CONCAT('%',?,'%')");
$stmt->bind_param("ss", $searchtype, $searchterm);
$stmt->execute();
$stmt->bind_result($name, $price, $id, $img);

and have tried this 

$searchterm = "%{$searchterm}%";
$stmt = $dbconn->prepare("SELECT name, price, id, img FROM product WHERE ? LIKE ?");
$stmt->bind_param("ss", $searchtype, $searchterm);
$stmt->execute();
$results = $stmt->get_result();
$count = $results->num_rows;
$row = $results->fetch_array();

I've tried many simple searches which work fine with the original non-prepared stmt query of my search function, but no matter what I try with my prepared stmt I get no results.

Sorry, your search returned no results. name met 0 Above name is the search type and met is search term using CONCAT and count is getting no results.

Sorry, your search returned no results. name %met% 0 This is using $searchterm = "%{$searchterm}%";

I'm not an experienced coder by any means but browsing SO usually solves any issues I'm having but right now nothing is working. What am I missing?

Dharman
  • 30,962
  • 25
  • 85
  • 135
cjson
  • 1
  • 1
  • Duplicate of https://stackoverflow.com/questions/18527659/php-mysqli-prepared-statement-like – lufc Jul 27 '19 at 18:21
  • 1
    Usually you can't use a placeholder in a prepared statement to represent the column name with MySQL - so the first `?` will not be filled as you want (prepared statements are usually not just expanded into a string before being sent). – MatsLindh Jul 27 '19 at 18:27
  • ah, i see. thank you so much. – cjson Jul 27 '19 at 18:28
  • `$searchtype` is a string. I believe you wanted it to be a column, and you can't bind columns. Could you clarify what `$searchtype` is? – Dharman Jul 27 '19 at 20:39

1 Answers1

0

If you would like to enable the end-user a possibility of selecting in which column they want to search for you should explicitly white list them. After that you can then search in DB passing the search term bound using placeholders and the column name can be safely interpolated into the SQL.

// White list columns
$cols = ['name', 'price', 'id', 'img'];
if ($colKey = array_search($_POST['searchtype'], $cols) === false) {
    throw new \Exception('Invalid column');
}
$column = $cols[$colKey];

// Prepare SQL statement with the column name and placeholder for search term
$stmt = $dbconn->prepare("SELECT name, price, id, img FROM product WHERE $column LIKE ?");
$searchterm = '%'.$_POST['searchterm'].'%';
$stmt->bind_param("s", $searchterm );
$stmt->execute();
$results = $stmt->get_result()->fetch_all();
Dharman
  • 30,962
  • 25
  • 85
  • 135