PHP与SQL多词多列模糊搜索优化及SQL注入防护指南-php教程-PHP中文网

PHP与SQL多词多列模糊搜索优化及SQL注入防护指南

碧海醫心

发布： 2025-11-15 12:08:02

原创

114人浏览过

PHP与SQL多词多列模糊搜索优化及SQL注入防护指南

本教程详细阐述了如何在php和sql中实现对包含空格的多词多列模糊搜索功能。文章首先分析了传统`concat_ws`方法的局限性，继而提出了通过php拆分搜索词并在sql中使用多个`like`条件进行匹配的策略。更重要的是，教程强调并演示了如何利用php的预处理语句（prepared statements）彻底防范sql注入攻击，并探讨了在处理大规模数据时可能面临的性能问题及相应的优化方案，包括全文索引和专业搜索工具。

在构建基于Web的数据库搜索功能时，用户经常需要通过一个输入框搜索多个字段，并且搜索词可能包含空格。例如，用户输入“test 2”，期望“test”匹配到firstName字段，“2”匹配到id字段。然而，直接使用CONCAT_WS函数拼接所有字段并进行LIKE '%search term%'匹配，对于这种多词多列的模糊搜索场景往往无法达到预期效果。

理解传统搜索的局限性

原有的CONCAT_WS函数旨在将多个字段的内容连接成一个字符串进行匹配。当搜索词中包含空格时，例如“test 2”，CONCAT_WS会尝试在连接后的完整字符串中查找“test 2”这个精确的子串。然而，如果“test”存在于firstName字段，“2”存在于id字段，并且它们之间并没有物理上的空格（因为它们是不同的字段），那么CONCAT_WS将无法匹配到预期的结果。这是因为CONCAT_WS默认会使用逗号作为分隔符（或者在CONCAT_WS(separator, str1, str2, ...)中指定的分隔符），而用户期望的是将搜索词的每个部分分别匹配到不同的字段中。

多词多列模糊搜索的实现策略

为了实现对包含空格的多词多列模糊搜索，我们需要将用户输入的搜索词拆分成独立的单词，然后针对每个单词，在数据库的多个相关列中分别进行模糊匹配。

PHP层处理：拆分搜索词

首先，在PHP代码中，我们需要获取用户输入的搜索字符串，并将其按照空格拆分成一个单词数组。

立即学习“PHP免费学习笔记（深入）”；

纳米搜索

纳米搜索：360推出的新一代AI搜索引擎

查看详情

// 获取用户输入的搜索值
$valueToSearch = $_POST['valueToSearch'];

// 使用空格拆分搜索字符串
$searchWords = explode(' ', $valueToSearch);

// 过滤掉空字符串，以防用户输入多个连续空格
$searchWords = array_filter($searchWords);

登录后复制

SQL层构建查询：使用多个LIKE条件

接下来，我们需要根据这些拆分出的单词来构建SQL的WHERE子句。对于每个单词，我们都应该在所有需要搜索的列中应用LIKE '%word%'条件。为了实现更灵活的匹配，通常我们会为每个搜索词生成一个子条件组，例如 (column1 LIKE '%word%' OR column2 LIKE '%word%')，然后将这些子条件组通过 AND 逻辑连接起来，以确保所有搜索词都能在某些列中找到匹配。

$conditions = [];
foreach ($searchWords as $word) {
    // 为每个单词构建一个子条件组，在所有相关列中搜索
    $wordConditions = [];
    // 定义需要搜索的列
    $columnsToSearch = ['id', 'office', 'firstName', 'lastName', 'type', 'status', 'deadline', 'contactPref', 'email', 'phoneNumber', 'taxPro']; 

    foreach ($columnsToSearch as $column) {
        $wordConditions[] = "`" . $column . "` LIKE ?"; // 使用占位符
    }
    // 将一个单词的所有列条件用 OR 连接
    $conditions[] = "(" . implode(" OR ", $wordConditions) . ")";
}

// 将所有单词的条件用 AND 连接
$whereClause = "";
if (!empty($conditions)) {
    $whereClause = " WHERE " . implode(" AND ", $conditions);
}

// 完整的查询语句结构
$query = "SELECT * FROM `master`" . $whereClause;

登录后复制

核心安全实践：防范SQL注入

在原始代码中，直接将用户输入 $valueToSearch 拼接到SQL查询字符串中 (LIKE '%".$valueToSearch."%') 存在严重的SQL注入漏洞。恶意用户可以构造特殊的输入来修改或破坏数据库查询，从而获取敏感信息或篡改数据。

强烈建议使用预处理语句（Prepared Statements）来防范SQL注入。 预处理语句将SQL查询的结构与数据分离，数据库服务器会在执行前编译查询结构，然后将数据作为参数绑定进去，从而避免了恶意代码的执行。

使用预处理语句的示例代码

以下是结合上述多词搜索逻辑和预处理语句的改进示例：

<?php

// filterTable 函数需要修改以支持预处理语句
function filterTable($query, $params = [], $paramTypes = '')
{
    // 替换为你的数据库连接信息
    $connect = mysqli_connect("localhost", "your_user", "your_password", "your_database"); 
    if (mysqli_connect_errno()) {
        die("Failed to connect to MySQL: " . mysqli_connect_error());
    }

    $stmt = mysqli_prepare($connect, $query);
    if ($stmt === false) {
        die("Prepare failed: " . htmlspecialchars($connect->error));
    }

    if (!empty($params)) {
        // 's' for string, 'i' for integer, 'd' for double, 'b' for blob
        // 这里所有搜索词都按字符串处理，所以类型字符串是 'sss...'
        mysqli_stmt_bind_param($stmt, $paramTypes, ...$params);
    }

    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt); // 获取结果集
    mysqli_stmt_close($stmt);
    mysqli_close($connect);
    return $result;
}

// 处理搜索逻辑
if(isset($_POST['search']))
{
    $valueToSearch = $_POST['valueToSearch'];
    $searchWords = explode(' ', $valueToSearch);
    $searchWords = array_filter($searchWords); // 过滤空字符串

    $conditions = [];
    $params = [];
    $paramTypes = ''; // 用于存储参数类型字符串

    $columnsToSearch = ['id', 'office', 'firstName', 'lastName', 'type', 'status', 'deadline', 'contactPref', 'email', 'phoneNumber', 'taxPro'];

    foreach ($searchWords as $word) {
        $wordConditions = [];
        foreach ($columnsToSearch as $column) {
            $wordConditions[] = "`" . $column . "` LIKE ?";
            $params[] = '%' . $word . '%'; // 绑定参数时添加通配符
            $paramTypes .= 's'; // 所有搜索词都作为字符串处理
        }
        $conditions[] = "(" . implode(" OR ", $wordConditions) . ")";
    }

    $whereClause = "";
    if (!empty($conditions)) {
        $whereClause = " WHERE " . implode(" AND ", $conditions);
    }

    $query = "SELECT * FROM `master`" . $whereClause;
    $search_result = filterTable($query, $params, $paramTypes);

} else {
    $query = "SELECT * FROM `master`";
    $search_result = filterTable($query); // 无参数查询
}

// HTML 部分
?>
<html>
    <head>
        <title>PHP HTML TABLE DATA SEARCH</title>
        <style>
            table,tr,th,td
            {
                border: 1px solid black;
            }
        </style>
    </head>
    <body>

        <form action="Untitled-1.php" method="post">
            <input type="text" name="valueToSearch" placeholder="Value To Search"><br><br>
            <input type="submit" name="search" value="Filter"><br><br>

            <table>
                <tr>
                    <th>ID</th>
                    <th>Office</th>
                    <th>First Name</th>
                    <th>Last Name</th>
                    <th>Type</th>
                    <th>Status</th>
                    <th>Deadline</th>

登录后复制

以上就是PHP与SQL多词多列模糊搜索优化及SQL注入防护指南的详细内容，更多请关注php中文网其它相关文章！