Adding Placeholders for PHP variables

Question

When creating a query using db_query in my module I want to make sure it's as secure as possible. So I'd like to use placeholders in my 'WHERE' clauses. The problem I'm having is how to use placeholders when the array values to be used are PHP variables. So for example:

WHERE el.field_experience_level_value='$level'

Is what I'm normally using and it works fine. But then I changed it to the following and added the array part at the end like so:

WHERE el.field_experience_level_value=':level'
...some more code
array(':level' => '$level')

I get no error but it doesn't return those results. So my main question is, how do I add a php variable such as $level above (already defined in previous code, not here) to a placeholder?

Answer 1

Better to use PDO for DB related operations in Drupal like db_select(), db_insert(), db_update() and db_delete().

Here you no need to worry about place holder and other injections.

Ex: $result = db_select('node', 'n') ->fields('n') ->condition('nid', $node->nid,'=') ->condition('status', 0,'>') ->condition('uid', array(1,5,7),'IN') ->execute() ->fetchAssoc();

Note from db_query() API page : Use this function for SELECT queries if it is just a simple query string. If the caller or other modules need to change the query, use db_select() instead.

Do not use this function for INSERT, UPDATE, or DELETE queries. Those should be handled via db_insert(), db_update() and db_delete() respectively.

Answer 2

Ok so I solved this already. You just have to NOT put quotes around the placeholder in the SQL query, so like this works:

WHERE el.field_experience_level_value=:level