How do I select a table using a variable with db_query()?

Question

I'm trying to find records from a table based on a variable that is grabbed from user submission. This is my query:

$field = t('field_data_' . variable_get('tabbed_content_field'));
$fieldValue = t(variable_get('tabbed_content_field') . '_value');
$query = db_query('SELECT :fieldValue FROM {:field}', array(':field' => $field, ':fieldValue' => $fieldValue));

However, whenever I run this query I get the following error:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''field_data_field_news_release_date'' at line 2: SELECT :fieldValue FROM {:field}; Array ( [:field] => field_data_field_news_release_date [:fieldValue] => field_news_release_date_value )

I can retrieve the records successfully with:

$query = db_query('SELECT ' . $fieldValue . ' FROM { ' . $field . '}');

However, as I understand it this would be insecure.

Does anyone know why the first query won't work?

Answer 1

This is one of the reasons the new query builder exists in Drupal 7, so you can dynamically substitute parts of the query:

$table_name = 'the_table';
$field_name = 'a_field';
$query = db_select($table_name)->fields($table_name, array($field_name));

Using db_query() you only get parameter replacement, which would only be applicable to substitutions in WHERE conditions. This means that you cannot use db_query() to replace table names, the names of fields selected, etc., only WHERE conditions.

Have a look at the db_select() docs for more in-depth examples, including how to add conditions to the dynamic query.