Placeholders and the query builder?

Question

I read on the drupal.org site: "Never put a literal value or variable directly into a query fragment, just as literals and variables should never be placed into a static query directly (they can lead to SQL injection vulnerabilities) http://drupal.org/node/1848348

$query = db_select('node', 'n');
$query -> condition('node', $nid);
$nodes = $query
-> fields('n', array('title'))
-> execute();

Does this also apply to queries written with the query builder, like the one above? If so, how should it be re-written?

Thanks

Answer 1

To clear out the confusion, a static query usually refers to a raw mysql query that you use through db_query() function. You write the entire query and pass it as the first parameter of the db_query function.

Dynamic queries, like the one you have in your question, is called dynamic query which makes it really easy to build the query dynamically. Think about a query that you need to iterate through a dynamic list of conditions. You will need to fight with string related functions to build the query, that you can now use the query builder to do for you!

If you use something like this:

$result = db_query('SELECT n.title
FROM {node} n WHERE n.uid = :uid', array(':uid' => $uid));

It's considered safe against SQL injection (when you query the node table for users, always pay attention to their access permissions).

But this:

$result = db_query('SELECT n.title
FROM {node} n WHERE n.uid = ' . $uid); 

is bad! We can't make sure that $uid will be completely safe to throw into the database.

Does this also apply to queries written with the query builder, like the one above? With Query builder, you will not need to use these parameters (:uid from above "good" example) in most queries. WHERE, ORDER BY and other clauses have their own methods that you can use in the query builder. Your code in the question is in the correct format.

In case you need to use a variable in the query, do use parameterized pattern.

Correct:

$query = db_select('node', 'n');

$table_alias = $query->join('users', 'u', 'n.uid = u.uid AND u.uid = :uid', array(':uid' => 5));

Wrong:

$query = db_select('node', 'n');

$table_alias = $query->join('users', 'u', 'n.uid = u.uid AND u.uid = ' . $uid);

(I'm aware that above query couple do not make any sense :D Just copied from the linked page in your question)