Trying to Bind SQL LIKE Query

Question

Trying to sanitize the following SQL query to prevent injection

$keyword = $_POST['keyword'];
$query = mysqli_query($conn, "SELECT p.name AS productname, p.manufacturer, p.productid, p.sku AS productsku, p.ean, p.weight, p.categories, s.name AS stockname, s.sku AS stocksku, s.qty, s.sellprice, s.costprice, d.distyname, d.lastupdated, m.image FROM stock s LEFT OUTER JOIN products p ON p.productid = s.prodid INNER JOIN distributor d ON d.distyid = s.distributorid LEFT OUTER JOIN media m ON s.sku = m.sku WHERE s.sku LIKE '%$keyword%' ORDER BY `stocksku`");
while($fetch = mysqli_fetch_array($query)){

Have tried the following but it will only display the result correctly if the SKU 100% matches, whereas the original query will shows similar matches too

    $query = 'SELECT p.name AS productname, p.manufacturer, p.productid, p.sku AS productsku, p.ean, p.weight, p.categories, s.name AS stockname, s.sku AS stocksku, s.qty, s.sellprice, s.costprice, d.distyname, d.lastupdated, m.image FROM stock s LEFT OUTER JOIN products p ON p.productid = s.prodid INNER JOIN distributor d ON d.distyid = s.distributorid LEFT OUTER JOIN media m ON s.sku = m.sku WHERE s.sku LIKE ? ORDER BY `stocksku`'; 
    $stmt = $conn->prepare($query);     
    $stmt->bind_param('s', $keyword);
    $stmt->execute();
    $result = $stmt->get_result();
    $fetch = $result->fetch_assoc()

Can anyone advise where i'm going wrong? Thanks

Your second query doesn't have the wildcards. I don't know if you can make your variable = `'%keyword%'` before you bind it? — Andrew, Jul 07 '23 at 20:15
For `LIKE` queries, you are still responsible for adding the `%` where appropriate. If you think about it, MySQL doesn't know if you want `%value`, `value%` or `%value%`. See https://stackoverflow.com/a/36593020/231316 — Chris Haas, Jul 07 '23 at 20:19

score 0 · Answer 1 · answered Jul 07 '23 at 20:18

$query = 'SELECT p.name AS productname, p.manufacturer, p.productid, p.sku AS productsku, p.ean, p.weight, p.categories, s.name AS stockname, s.sku AS stocksku, s.qty, s.sellprice, s.costprice, d.distyname, d.lastupdated, m.image FROM stock s LEFT OUTER JOIN products p ON p.productid = s.prodid INNER JOIN distributor d ON d.distyid = s.distributorid LEFT OUTER JOIN media m ON s.sku = m.sku WHERE s.sku LIKE ? ORDER BY stocksku';
$stmt = $conn->prepare($query);     
$stmt->bind_param('s', $keyword);
$stmt->execute();
$result = $stmt->get_result();

while ($fetch = $result->fetch_assoc()) {
    // Process each row
}

besides unquoting a column name and introducing a loop, how is this different than the OP's posted code? — Chris Haas, Jul 07 '23 at 20:22

Trying to Bind SQL LIKE Query

1 Answers1