title: Securely escaping dynamic table names in MySQL using Codeigniter
body: <p>I'm facing a slight problem. I'm working on an application at the moment that requires the use of dynamic table names in MySQL.</p>
  
  <p>Basically, I have a process of selecting albums from a database based on a number of factors (Genre, Play Length, Release Date etc) -- There is a section of this process that allows the user to create a bunch of custom filters..</p>
  
  <p>The custom filters return a count to the user of all the albums within that selection criteria. The ID's of those albums are then stored in a table with a randomly generated hash/serial number (e.g. albumSelect_20880f9c05d68a)</p>
  
  <p>I did it this way because I didn't want to store a huge comma separated list in a field (Really silly) -- And I didn't fancy sending an array of values to a hidden field in my HTML as this would just increase data throughput (Could be thousands of rows at a time)</p>
  
  <p>In CodeIgniter, I'm using Query Bindings to generate my SQL queries, like so:</p>
  
  <pre><code>select * from artists where artistName = ? AND albumTitle = ?
  </code></pre>
  
  <p>The query is then automatically escaped when I parameterize the query</p>
  
  <pre><code>$query = $this-&gt;db-&gt;query($sql,array("Singer","Album"));
  </code></pre>
  
  <p><strong>Now comes the tricky part</strong></p>
  
  <p>If I write my query to look something like this:</p>
  
  <pre><code>$sql = "select albumid from albums where albumid in (select albumid from albumSelect_?)";
  $this-&gt;db-&gt;query($sql,array('20880f9c05d68a'));
  </code></pre>
  
  <p>The resulting query becomes:</p>
  
  <pre><code>select `albumid` from `albums` where `albumid` in (select `albumid` from `albumSelect_'20880f9c05d68a'`)
  </code></pre>
  
  <p>And quite rightly so, but obviously the query is then invalid..</p>
  
  <p><strong>Edit: More Info</strong></p>
  
  <p>The query could be part of a bigger query, depending on what criteria the user selects. e.g.</p>
  
  <pre><code>$sql = "select albumid from albums where albumid in(select albumid from tags where tag = ?) AND albumid in(select albumid from albumSelect_?)";
  </code></pre>
  
  <p>I was just wondering if there was a way to get this working, OR if anyone could suggest a better alternative.. Concatenation of the table name is obviously <strong>not</strong> an option.</p>
  
  <p>Thanks in advance!</p>
  
  <p>Dave</p>