The only way around it I've found

Posted by Kromey at 2:12am Aug 24 '09
You must sign in to send Kromey a message

is to dynamically generate the query itself with enough placeholders to allow for your IN query (or whatever else). Example (in semi-pseudocode because I don't care to write perfectly correct PHP at the moment):

<?php
//Create your query up until the IN clause
$query = "SELECT column1, column2, column3 FROM table WHERE column1 IN (";

//Now generate a list of '?' placeholders
$query .= trim(',', str_repeat('?,', count($params)));
//The previous line's str_repeat gives us a series of placeholders but has a trailing comma, so we trim that off

//Now finish off the query
$query .= ')';

//Now prepare it, bind it, and run it - but I'll leave that as an exercise for the reader...
?>

It's not elegant, but it works.

Also, a word of warning regarding IN clauses: If you're building them dynamically like this and aren't limiting the length of the list to something sane, you can destroy your database server. At work we're battling with this very issue, where we have unbounded IN clauses that are creating queries of 500 KB and well beyond (the query itself, not the result set!); the biggest one I've seen so far was very nearly 2 MB. When these queries get executed, the entire database server ceases doing anything else and focuses entirely on running that one query; other queries build up in a queue and wait around - our entire web app is completely useless for several minutes, resulting in a lot of furious customers, especially around payroll time (our web app happens to supply sales and other data to over 3500 salons and spas around the world; said data, of course, is used to compute hourly wages and commissions).

If it is possible to rewrite your queries to use subqueries or JOINs instead of IN clauses, you should seriously consider doing that instead of creating this kind of database-killing timebomb.