In Kohana 3, you can now bind named params in queries, but how do I escape ones I can not bind?

Question

In Kohana 3, I can bind a param in a query like so

$query = 'SELECT name FROM users WHERE id = :id';

This is neat, but sometimes I want other things to be configurable, like the table name. When I use a named param for table name, it fails. I have just been building the string in these circumstances.

My question is, how can I escape potentially bad characters when doing this kind of string building? In Kohana 2.x, you could do $this->db->escape() but since Kohana 3 is radically different, I'd imagine there is a new way?

Answer 1

I've never used kohana, so apologies if this comment is completely irrelevant, but I have a few comments about what you are trying to do:

When I use a named param for table name, it fails.

I assume that this is because it is relying on the underlying database's parameter mechanism, and I don't know of any database that allows you to give a table name as a parameter in a query. So yes, most likely you'll have to build a string yourself.

how can I escape potentially bad characters when doing this kind of string building?

This question seems strange... either you have potentially bad characters in some of your table names... if so, why? Or else you're receiving the table name from an untrusted source (user?). This sounds like a bad idea to me. What if they try to read from a table they weren't supposed to have access to? Wouldn't it be better to have a well defined list of allowable tablenames and check that the table exists in that list, instead of escaping potentially bad table names?

And I have one final observation: if you are able to change the table name in a query and it still works, this is possibly a sign that your database is not normalized correctly. Perhaps you could combine your tables and add an extra column with information about the grouping, and use a WHERE clause to select the data you want? I'd need to know more about your model to suggest something more concrete.

I'm not sure if this helps answer your question at all, but these were my thoughts on reading your question. I hope you can use some of it.

Answer 2

Just to add to Mark Byers's answer, the reason that the parametrization of the table name is failing is that parameters are escaped as values, while a table name is an identifier. What's probably happening is that the table name is being wrapped in quotes, while it should be being wrapped in backticks instead.

However, as Mark says, a need to escape table names suggests bad design. They should either be hard coded or stored in some configuration file whose contents can be trusted not to need escaping anyway. Trying to cover all of your bases to such an extent that you don't trust your own configuration is most likely a waste of time.