SELECT WHERE IN not retrieving all records

Question

Edit: To clarify, I'm using PDO to MySQL database

Ok I have a slight problem with a query. I've tried googling and 'stackoverflowing', to no avail. I'm trying to select all records from a table called download store, where the ownerid = '1'

//Partial code
 $sql="SELECT * FROM boughtdownloads WHERE ownerid=:userid";
 $prep=$GlobalDB->prepare($sql);
 $prep->bindParam(':userid',$userid,PDO::PARAM_INT);
 $prep->execute();
 if($prep->rowCount()=='0')
 {
   return 'Error with get owned IDS';
 }
 else
 {
   foreach($prep->fetchAll()as $res)
   {
      $owned[]=$res['fileid'];
   }
    $owned=implode(",",$owned);
    return $owned;
 }

That works as expected, returning 1,2

$OwnedIDS=$this->GetOwnedIDS($userid); // The same code as above ^
//This query doesn't work, even with the following:
//$OwnedIDS="1,2"; //Which is what's expected, and returned, from the above
$sql="SELECT * FROM downloadstore WHERE fileid IN (:owned)";
$prep=$GlobalDB->prepare($sql);
$prep->bindParam(':owned',$OwnedIDS);
$prep->execute();
if($prep->rowCount()=="0")
{
   return 'Error with remaining disk space';
}
 else
{
  echo $prep->rowCount();
}

This only returns 1 record, despite two records being in the downloadstore table $GlobalDB = pdo connection string (which works)

$userid = $_SESSION, which equals "1"

[Download Store screenshot] DownloadStore Table

[BoughtItems screenshot]

BoughtItems Table

As you can see, there are 2 records in each table, with valid fileid's.

But only 1 record is being pulled when querying the download store with the IN clause.

Could someone help me out please? It's probably something stupidly simple I'm overlooking, but I've triple checked and I'm sure there's nothing wrong (although clearly there is) with my query or the table(s).

Thanks, Rob

For small data sets, you may want to try `FIND_IN_SET` instead of `IN`. — showdev, Apr 16 '13 at 17:42
http://stackoverflow.com/questions/15990857/reference-frequently-asked-questions-about-pdo#15991146 — Your Common Sense, Apr 16 '13 at 18:37

score 2 · Answer 1 · answered Apr 16 '13 at 17:42

2

You need to create a bind parameter for every element of the array, then bind each one:

$bindParams = array();
foreach ($ownerIds as $index => $ownerId) {
    $bindParams[] = ':ownerId' . $index;
}

$sql = 'select * from downloadstore where fileid in (' . implode(', ', $bindParams) . ')';
$prep = $db->prepare($sql);

foreach ($ownerIds as $index => $ownerId) {
    $prep->bindValue(':ownerId' . $index, $ownerId);
}

answered Apr 16 '13 at 17:42

leftclickben

4,564
23
24

But surely it's the same as ... IN('1,2') ? Which I'm trying to achieve, or does it work differently in PDO? I will try this now - Thanks for your reply. – RobAtStackOverflow Apr 16 '13 at 17:44
Sorry for the double comment - I'm not too sure where to put this, but I've had a go and I get an error foreach argument is invalid, so I know I'm putting it in the wrong place, but I'm not too sure _where_ too put it. – RobAtStackOverflow Apr 16 '13 at 18:02
Oh sorry I didn't copy your variable names very well. I think instead of `$ownerIds` it should be `$OwnedIDs`. You might need to check the others like `$db` as well :-) – leftclickben Apr 16 '13 at 18:04
It's fine :) I figured it out that you meant OwnedIDs but still got the error. I'm still struggling in getting this to work... Would you be able to provide me with an easier solution if you don't mind? +1 for your help though, thanks. – RobAtStackOverflow Apr 16 '13 at 18:13
Oh right, you need to take this line out of your first block of code: `$owned=implode(",",$owned);` This will convert it to a string, which you don't want. – leftclickben Apr 16 '13 at 18:17
Your right, I don't want that, because it won't select all the file ID's...Thanks though :) I may have to come up with a crude way of doing this, but I don't want to do that. The weird thing, is I've had SELECT IN work before, with PDO, and I'm not sure why it's not working now... – RobAtStackOverflow Apr 16 '13 at 18:22
I'm not sure what you mean, why can't you just remove that line calling `implode()`? – leftclickben Apr 16 '13 at 18:23

Quassnoi · Answer 2 · 2013-04-16T17:45:44.647

0

In MySQL, you can't bind an IN list.

You should parse it on the client and pass it in the query text, not in a bound variable:

# sanity checks for $ownerIds go here
$sql="SELECT * FROM downloadstore WHERE fileid IN (" . implode(',', $ownerIds) . ")";

Or, preferrably, you can just do everything on the server side:

SELECT  ds.*
FROM    boughtdownloads bd
JOIN    downloadstore ds
ON      ds.fileId = bd.fileId
WHERE   bd.ownerId = :userId

edited Apr 16 '13 at 17:45

answered Apr 16 '13 at 17:39

Quassnoi

413,100
91
616
614

Only if you love SQL injection attacks. – leftclickben Apr 16 '13 at 17:40
Since when? I was under impression you pass an array...? – Kermit Apr 16 '13 at 17:41
Sorry I should clarify I'm using PDO, where you can pass variables. – RobAtStackOverflow Apr 16 '13 at 17:41
@FreshPrinceOfSO: since always. – Quassnoi Apr 16 '13 at 17:43
@leftclickben: `$ownerIds` here are not user-provided. – Quassnoi Apr 16 '13 at 17:46
@Quassnoi Today perhaps, what about tomorrow, when this function gets reused and it _does_ take user-supplied values? Besides, we should be teaching the best way to do things, not just a way. – leftclickben Apr 16 '13 at 17:47
@Quassnoi, for now it's testing purposes, the $ownerIDS will be user supplied from the database. – RobAtStackOverflow Apr 16 '13 at 18:00
@leftclickben: Are you suggesting using preparing statements (with additional round-trips to the server) only as a mean of checking if an integer value is really an integer? – Quassnoi Apr 16 '13 at 18:04
What additional round trips to the server? And no, I'm suggesting that this type of code always has the potential, present or future, for SQL injection attacks: `$sql = 'select blah blah ' . $anyVariable . ' blah blah'`. Always use a placeholder. – leftclickben Apr 16 '13 at 18:10
@Quassnoi Are you aware that he is already using prepared statements in the original question? Therefore there are no additional round trips by continuing to use them. – leftclickben Apr 16 '13 at 18:14
@Quassnoi, how I've got it set up at the moment is: User 'buys' an item. FileID and userID is inserted into that table. I then need to get the fileID where owner ID = the userID which in turn = $_SESSION['hisID']; After that I need to get the virtual file size of that file (but that's way ahead of this at the moment). Unless someone modifies the $_SESSION to include XSS, this is not vulnerable. – RobAtStackOverflow Apr 16 '13 at 18:15

SELECT WHERE IN not retrieving all records

2 Answers2