CODEWITHSUNDEEP
phpmysqlsecuritycodeigniteractiverecord
Dave
Dave

Reputation: 1086

Securely escaping dynamic table names in MySQL using Codeigniter

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

Upvotes: 8

Views: 1794

Answers (2)

MvG
MvG

Reputation: 60958

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.

Upvotes: 1

Naltharial
Naltharial

Reputation: 2152

It sounds to me like you want to use dynamic SQL. You'll probably have to go down the path of prepared statements. With them, you can call PREPARE on the string and subsequently EXECUTE it. Normal concatenation works just fine.

That should allow you to build your SQL as a string and execute it. If you use CodeIgniter's parametrization in combination with MySQL stored procedures, you can call a query like "CALL selectAlbums(?, ?)" (assuming selectAlbums is the stored procedure containing the PREPARE for the actual query), which will return the set.

If you want to get rid of the 's in output, channel the parameters through CONCAT, which will produce a normal string.

Upvotes: 0

Related Questions