Securely escaping dynamic table names in MySQL using Codeigniter

Question

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.

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..

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)

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)

In CodeIgniter, I'm using Query Bindings to generate my SQL queries, like so:

select * from artists where artistName = ? AND albumTitle = ?

The query is then automatically escaped when I parameterize the query

$query = $this->db->query($sql,array("Singer","Album"));

Now comes the tricky part

If I write my query to look something like this:

$sql = "select albumid from albums where albumid in (select albumid from albumSelect_?)";
$this->db->query($sql,array('20880f9c05d68a'));

The resulting query becomes:

select `albumid` from `albums` where `albumid` in (select `albumid` from `albumSelect_'20880f9c05d68a'`)

And quite rightly so, but obviously the query is then invalid..

Edit: More Info

The query could be part of a bigger query, depending on what criteria the user selects. e.g.

$sql = "select albumid from albums where albumid in(select albumid from tags where tag = ?) AND albumid in(select albumid from albumSelect_?)";

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 not an option.

Thanks in advance!

Dave

Answer 1

The escaping mechanisms are only for data strings, not for schema names. In other words, only for the content of a table, not for its structure. So you'll either have to paste the string into the query yourself, or avoid using tables in this way. The ? of query templates won't help you there.

If you paste the string into the table name, you can use the usual PHP string concatenation mechanisms. You should make extra sure that you check the string against a suitably strict regular expression, to avoid SQL injection. Make sure that you really only paste a single random string of the format you generate, and nothing else.

As an alternative, you could have one big table, containing all the selections, and use an additional column to hold your identification hash, or some other suitable key to identify a single selection. That way, you wouldn't have to modify the database schema during normal operations. I believe most developers, me included, would rather avoid such modifications by program code. Good database design works with a fixed schema.