php - 无法获取搜索以获取PHP MySQL查询中的OR语句

当前正在建立一个CRUD系统以跟踪产品编号。在我为搜索设置的查询中，除了“ p.name”外，似乎没有其他任何内容，无论我将其放在WHERE语句中的第一还是第二位进行搜索功能。

但是，如果我将第二个函数中的p.name更改为其他名称，它将被选中。

如果我添加“或p.family喜欢？”查询将不会执行。

这是用于搜索产品的代码。我已经添加了评论“ //-我可以在这里添加另一个喜欢的东西吗？”

<?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'];
    }

}

这是用于搜索的页面

<?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";
?>

如果有人可以帮助我，那就太好了！谢谢。

最佳答案

您应该能够像初审那样做到这一点。您需要为查询中的每个值使用占位符，然后需要为每个占位符绑定。

$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);

bindParam函数的第一个参数是它如何映射到查询中的占位符，1表示它与第一个占位符一起使用。另一种语法是在execute中传递绑定。

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

此外，直接在DOM中使用用户提供的数据可以使您进行XSS注入。您应该转义输入，以使恶意代码无法执行。

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

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

关于php - 无法获取搜索以获取PHP MySQL查询中的OR语句，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/44708789/