• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Securely escaping dynamic table name in MySQL query..

#1
[eluser]ThatDaveCarter[/eluser]
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)

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

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

The query is then automatically escaped when I parameterize the query

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

Now comes the tricky part

If I write my query to look something like this:

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

The resulting query becomes invalid because the table name is escaped, and quite rightly so, but obviously the query is then invalid..

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

Code:
$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

#2
[eluser]CroNiX[/eluser]
In that particular edge case, I think I'd just manually escape the variables with db::escape() and not use bindings.



Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.