7

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?

  • 2
    As a side note, you don't use t() for strings that don't need to be translated. The name of a database table, or a database field is not something that needs to be translated; the "node" table is always the "node" table, independently from the language currently enabled for the site. – kiamlaluno Mar 27 '12 at 13:56
  • @kiamlaluno ah right thks for that. – Stephen Young Mar 27 '12 at 14:49
6

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.

  • Great thanks very much Clive - that worked for me. Does the db_select function automatically sanitise potentially malicious data entry? – Stephen Young Mar 27 '12 at 14:49
  • 1
    @StephenYoung It does, the Drupal database layer is essentially a wrapper around PDO so you get all of the parametized query goodness built right in. The only way to use it unsafely is like the second example in your question, which as you rightly said would potentially be open to exploit. – Clive Mar 27 '12 at 15:02
  • @StephenYoung Data passed to the database API are sanitized. – kiamlaluno Mar 27 '12 at 15:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.