在PHP MySQL Query中无法获取搜索来获取OR语句

Question

Currently building a CRUD system to keep track of product numbers. In the query I've set up for the search, it doesn't seem to pick up anything but 'p.name', but doesn't pick up anything else, whether I put it first or second in the WHERE statement for search function.

But if I change p.name in the second function to something else it will pick that up.

If I add an "OR p.family LIKE ?" the query won't execute.

Here is the code for the search of products. I've added a comment "//--Can I add another LIKE here?"

<?php
class Product{

    // database connection and table name
    private $conn;
    private $table_name = "products";
    private $table2_name = "deleted_products";

    // object properties
    public $id;
    public $name;
    public $family;
    public $number;
    public $description;
    public $ext_description;
    public $category_id;
    public $timestamp;
    public $timestamp2;

    public function __construct($db){
        $this->conn = $db;
    }

        public function search($search_term, $from_record_num, $records_per_page){

        // select query
        $query = "SELECT
                    c.name as category_name, p.id, p.name, p.family, p.description, p.ext_description, p.number, p.category_id, p.created
                FROM
                    " . $this->table_name . " p
                    LEFT JOIN
                        categories c
                            ON p.category_id = c.id
                WHERE
                    p.family LIKE ? OR p.description LIKE ?
                ORDER BY
                    p.name ASC
                LIMIT
                    ?, ?";

        // prepare query statement
        $stmt = $this->conn->prepare( $query );

        // bind variable values
        $search_term = "%{$search_term}%";
        $stmt->bindParam(1, $search_term);
        $stmt->bindParam(2, $search_term);
        $stmt->bindParam(3, $from_record_num, PDO::PARAM_INT);
        $stmt->bindParam(4, $records_per_page, PDO::PARAM_INT);

        // execute query
        $stmt->execute();

        // return values from database
        return $stmt;
    }

    public function countAll_BySearch($search_term){

        // select query
        $query = "SELECT
                    COUNT(*) as total_rows
                FROM
                    " . $this->table_name . " p
                    LEFT JOIN
                        categories c
                            ON p.category_id = c.id
                WHERE
                    p.name LIKE ?"; // ---- Can I add another LIKE here?

        // prepare query statement
        $stmt = $this->conn->prepare( $query );

        // bind variable values
        $search_term = "%{$search_term}%";
        $stmt->bindParam(1, $search_term);

        $stmt->execute();
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        return $row['total_rows'];
    }

}

Here is the page used for the search

<?php
// core.php holds pagination variables
include_once 'config/core.php';

// include database and object files
include_once 'config/database.php';
include_once 'objects/product.php';
include_once 'objects/category.php';

// instantiate database and product object
$database = new Database();
$db = $database->getConnection();

$product = new Product($db);
$category = new Category($db);

// get search term
$search_term=isset($_GET['s']) ? $_GET['s'] : '';

$page_title = "You searched for \"{$search_term}\"";
include_once "header.php";

// query products
$stmt = $product->search($search_term, $from_record_num, $records_per_page);
//$stmt = $product->readAll($from_record_num, $records_per_page);

// specify the page where paging is used
$page_url="search.php?s={$search_term}&";

// count total rows - used for pagination
$total_rows=$product->countAll_BySearch($search_term);

// read_template.php controls how the product list will be rendered
include_once "read_template.php";

// footer.php holds our javascript and closing html tags
include_once "footer.php";
?>

If anyone can help me out that would be great! Thank you.

Answer 1

You should be able to do it like you had done in your first instance. You need a placeholder for every value in the query, you then need a binding for every placeholder.

$query = "SELECT
            COUNT(*) as total_rows
            FROM
            " . $this->table_name . " p
            LEFT JOIN
            categories c
            ON p.category_id = c.id
            WHERE
            p.name LIKE ? OR p.description LIKE ?";    

// prepare query statement
$stmt = $this->conn->prepare( $query );
// bind variable values
$search_term = "%{$search_term}%";
$stmt->bindParam(1, $search_term);
$stmt->bindParam(2, $search_term);

The first parameter of the bindParam function is how it maps to the placeholder in the query, 1 means it goes with the first placeholder. An alternative syntax is to pass the bindings in the execute.

$stmt = $this->conn->prepare( $query );
$search_term = "%{$search_term}%";
$stmt->execute(array($search_term, $search_term));

Additionally, using user provided data directly in the DOM opens you to XSS injections. You should escape the input so malicious code can't be executed.

$search_term=isset($_GET['s']) ? htmlspecialchars($_GET['s'], ENT_QUOTES) : '';

1. https://en.wikipedia.org/wiki/Cross-site_scripting
2. https://www.owasp.org/index.php/Cross-site_Scripting_(XSS)