php - 如何避免PDO准备语句中的转义字符 "\"？

Question

这个问题已经有答案了:

PHP - Using PDO with IN clause array (9 个回答)

已关闭 7 年前。

我有这个 SQL 查询:

$sql = "SELECT ac.id AS target_id FROM account AS ac JOIN 
               address_vod__c AS ad 
               ON (ad.account_vod__c = ac.id AND ad.primary_vod__c = 1)  
               WHERE ac.id IN (?)";

我正在尝试为 IN 添加值数组中的子句如下:

// $values is a huge array containing values

$params = [implode("','", $values)];
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$result = $stmt->fetchAll();

代码可以工作，但我得到了错误的 SQL:

SELECT 
    ac.id AS target_id
FROM
    account AS ac
        JOIN
    address_vod__c AS ad ON (ad.account_vod__c = ac.id
        AND ad.primary_vod__c = 1)
WHERE
    ac.id IN ('00180000017rkSfAAI\',\'0018000001GgXTtAAN\',\'0018000001GgXTYAA3')

我期望并且我看起来像:

SELECT 
    ac.id AS target_id
FROM
    account AS ac
        JOIN
    address_vod__c AS ad ON (ad.account_vod__c = ac.id
        AND ad.primary_vod__c = 1)
WHERE
    ac.id IN ('00180000017rkSfAAI','0018000001GgXTtAAN','0018000001GgXTYAA3')

如何避免 PDO 转义字符串？

Answer 1

您正在执行ac.id IN (?)。这告诉数据库您想要将一个参数绑定(bind)到查询。如果有多个元素，则需要多个 ?:ac.id IN (?,?,?)。

您可以做的是动态添加?，然后绑定(bind)您需要的每个参数。

$params = implode(',', array_fill(0, count($values), '?'));
$sql = "SELECT ac.id AS target_id FROM account AS ac
    JOIN address_vod__c AS ad ON (ad.account_vod__c = ac.id AND ad.primary_vod__c = 1)
    WHERE ac.id IN ({$params})";

$stmt = $pdo->prepare($sql);
$stmt->execute($values);
$result = $stmt->fetchAll();