Use PDO to bind array

Question

So I am building a job website where the user selects several locations, and it should return the corresponding jobs. This means I will get an array of locations from the user. How would I use PDO to bind this array. At the moment I just add the value into the SQL directly however i know this is a security issue. How could I rewrite it so it uses syntax like:

$stmt->bindValue(":locations", $locations);

This is as far as I have got:

            $location = $_POST['location'];
            $locs = "'" . implode("', '", $location) . "'";

            //create query
            $sql = 
            "SELECT *
            FROM job
            JOIN location ON job.location_id = location.id
            WHERE location.region IN ($locs)
            ";

            //get results
            try
            {
                $stmt = $DB->prepare($sql);
                $stmt->execute();
                $results = $stmt->fetchAll(); 
            }
            catch (Exception $ex)
            {
                echo $ex->getMessage();
            }

            //print results
            foreach($results as $res)
            {
                echo "<p>Title:" . $res['title'];
                echo "<p>Views:" . $res['views'];
                echo "<p>Created At:" . $res['created_at'];
                echo "<p>Image:" . $res['img_url'];
                echo "<p>Salary:" . $res['salary'];
                echo "<p>Region:" . $res['region'];
                echo "<p>State:" . $res['state'];
                echo "<br/><br/>";

            }

I don't think you can bind using PDO for an IN clause. You will likely need to validate the format in PHP to prevent injection, then put it straight into the query like you are already doing. — S. Dev, Apr 29 '18 at 10:47

jspcal · Accepted Answer · 2018-04-30T09:14:05.460

3

It's not possible to bind an array to a list used in an IN clause. You can however bind multiple scalar values like so:

where location.region in (:region1, :region2, :region3, [...])

$params = array(
    ':region1' => $regions[0],
    ':region2' => $regions[1],
    ':region3' => $regions[2]
    [...]
);

$stmt->execute($params);

You'd need to programatically build up the IN ($placeholders) part of the query to handle a dynamic number of parameters.

edited Apr 30 '18 at 09:14

answered Apr 29 '18 at 10:47

jspcal

50,847
7
72
76

I was reading the PHP docs and you can bind an array to the execute method. See this page, example #5 http://php.net/manual/en/pdostatement.execute.php – Magearlik Apr 30 '18 at 08:25
That doesn't mean you can bind an array to a single placeholder in an IN clause. From the doc: "Multiple values cannot be bound to a single parameter... [I]t is not allowed to bind two values to a single named parameter in an IN() clause." – jspcal Apr 30 '18 at 09:17

Use PDO to bind array

1 Answers1