How to avoid escape character "\" in PDO prepared statement?

Question

I have this SQL query:

$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 (?)";

And I am trying to add values for IN clause from a array as follow:

// $values is a huge array containing values

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

Code is working but I am getting the wrong 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')

I expect and I am looking something like:

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')

How do I avoid PDO from escape the strings?

You are putting your entire string in one value. You need to have one `?` for each value that you're using. You'll need to have `IN(?,?,?)` and bind your three values separately. — Andy Lester, Aug 25 '15 at 16:04

score 1 · Accepted Answer · answered Aug 25 '15 at 16:03

You are doing ac.id IN (?). This is telling the database that you want to bind one parameter to the query. If you have multiple elements, you need multiple ?s: ac.id IN (?,?,?).

What you can do is dynamically add the ?s and then bind each parameter that you need.

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

score 0 · Answer 2 · answered Aug 25 '15 at 16:04

0

You should implode the placeholders and let pdo do the work

$inArray = array_fill(0, count($values), '?');
$inExpr = implode(',', $inArray);
$sql = "... WHERE id IN ($inExpr)";

And then just pass $values - pdo will do the rest for you

answered Aug 25 '15 at 16:04

Philipp

15,377
4
35
52

How to avoid escape character "\" in PDO prepared statement?

2 Answers2