SQL查询含空字符串type条件时返回结果异常，请求排查

当前SQL查询只会验证type列的相关条件，把这个条件注释掉后才能得到预期结果。下面是相关PHP代码和生成的SQL语句，怀疑是查询语法有问题，求帮忙排查：

$requestData = $this->input->post(null, true);
$types = [
    0 =>'Processor',
    1 =>'Ram',
    2 => 'Swap',
    3 => 'Service'
];
$searchValue = $this->db->escape_str($requestData['search']['value']);
$value = array_search(strtolower($searchValue), array_map('strtolower',$types));

if (!empty($requestData['search']['value'])) {
    //$sql .= " AND (endpoint_performance_counters_alerts.type = '" . $value . "' ";
    $sql .= " AND (endpoint_performance_counters_alerts.type LIKE '%" . $value . "%' ";
    $sql .= " OR persoane_contact_fixe.Nume LIKE '%" . $searchValue . "%' ";
    $sql .= " OR persoane_contact_fixe.Prenume LIKE '%". $searchValue . "%' ";
    $sql .= " OR endpoint_performance_counters_alerts.value LIKE '%" . $searchValue . "%') ";
}

生成的SQL语句：

SELECT CONCAT(persoane_contact_fixe.Nume, ' ', persoane_contact_fixe.Prenume) as DeviceOwner,
   endpoint_performance_counters_alerts.value as value,
   endpoint_performance_counters_alerts.start_time as startDate,
   endpoint_performance_counters_alerts.type as type FROM 
 endpoint_performance_counters_alerts
   WHERE endpoint_performance_counters_alerts.company = '8'
   AND (endpoint_performance_counters_alerts.type = ''
   OR persoane_contact_fixe.Nume LIKE '%r%'
   OR persoane_contact_fixe.Prenume LIKE '%r%'
   OR endpoint_performance_counters_alerts.value LIKE '%r%')

问题出在哪 #

1. array_search返回值坑：当搜索值（比如例子里的r）不在$types的小写映射里时，array_search会返回false，而不是你以为的“找不到就不返回”。拼SQL的时候，false会被转成空字符串，就出现了endpoint_performance_counters_alerts.type = ''这种无效条件。
2. 没做有效性判断：不管$value有没有效，直接就拼进SQL了，导致这个无效条件打乱了整个OR逻辑——因为type = ''大概率没数据，所以整个OR组相当于没生效，看起来就像只验证type列了。

怎么改 #

先判断$value是不是有效（不是false），只有匹配到$types里的项时，才加type的搜索条件，否则跳过。修改后的代码：

$requestData = $this->input->post(null, true);
$types = [
    0 =>'Processor',
    1 =>'Ram',
    2 => 'Swap',
    3 => 'Service'
];
$searchValue = $this->db->escape_str($requestData['search']['value']);
$value = array_search(strtolower($searchValue), array_map('strtolower',$types));

if (!empty($requestData['search']['value'])) {
    $conditions = [];
    // 只有找到匹配的type值时，才加入这个条件
    if ($value !== false) {
        $conditions[] = "endpoint_performance_counters_alerts.type LIKE '%" . $value . "%'";
    }
    // 其他条件正常加
    $conditions[] = "persoane_contact_fixe.Nume LIKE '%" . $searchValue . "%'";
    $conditions[] = "persoane_contact_fixe.Prenume LIKE '%". $searchValue . "%'";
    $conditions[] = "endpoint_performance_counters_alerts.value LIKE '%" . $searchValue . "%'";
    
    // 把所有条件用OR拼起来
    $sql .= " AND (" . implode(' OR ', $conditions) . ") ";
}

额外提个醒 #

• 尽量用参数绑定代替直接字符串拼接，既能防SQL注入，处理特殊字符也更稳。
• 可以把$types改成反向映射，比如['processor' => 0, 'ram' => 1]，这样搜索的时候不用遍历数组，效率更高。

内容的提问来源于stack exchange，提问作者Denis