SQL IN not working when ran from php

Question

I'm trying to write code that basically finds your facebook friends that are on my website. I succeed in phpmyadmin running the query but for some reason when i try to run the code from php it doesn't work

Here's the php code. Whenever i take the $string echo and place it in mysql it works just fine, but for whatever reason when running it in php the query is not returning any results.

$fql = "SELECT uid FROM user WHERE uid IN (SELECT uid2 FROM friend WHERE uid1 = 100000903067831) AND is_app_user = 'true'";
        $param  =   array(
            'method'    => 'fql.query',
            'query'     => $fql

        );
    $this->load->library('facebook');

    echo  $this->facebook->getLoginUrl();
    $fqlResult   =   $this->facebook->api($param);
    $userIDarray = array();
    foreach($fqlResult as $result)
    {
        echo $result['uid']."<br>";
        array_push($userIDarray, intval($result['uid']));
    }
    $string = implode(', ',$userIDarray);
    echo $string;
    $vars = array($string);
    $query = $this->db->query("SELECT * FROM users WHERE users.facebook_id IN (?)", $vars);
    echo var_dump($query);
    foreach($query->result() as $data)
    {
        echo var_dump($data);
    }

Johan · Answer 1 · 2013-10-09T01:14:18.333

You cannot pass multiple parameters in a single ?.

You need to construct the options for IN yourself using concatenation.

Like so:

foreach($fqlResult as $result)
{
    echo $result['uid']."<br>";
    array_push($userIDarray, intval($result['uid']));
}
$string = implode(', ',$userIDarray);
$query = $this->db->query("SELECT * FROM users WHERE users.facebook_id 
                           IN ('.$string.')");

Note that you need to make sure your items in the $userIDarray are properly escaped.
Because you're not using parameters, but you've injected these values into your SQL you are in danger of SQL injection attacks.

You are passing them through intval which guarantees that the strings will only contain 0..9 and - so you are safe from that here.
If the data is not numeric, you need use mysqli_real_escape_string to compensate for the fact that you're bypassing PDO's parameters.

SQL IN not working when ran from php

1 Answers1