Last updated May 5, 2016. Created on September 18, 2008.
Edited by rooby, keopx, bpleduc, mqanneh. Log in to edit this page.

The most common form of query in Drupal is a static query. A static query is one that will be passed to the database nearly verbatim. Only Select queries may be static.

Only very simple select queries should use the static query mechanism. You should use a dynamic query if you need more complex queries, dynamic query generation or alterability.

The internal way to issue a static query is with the query method:

$result = $conn->query("SELECT nid, title FROM {node}");

The procedural wrapper is generally preferred:

$result = db_query("SELECT nid, title FROM {node}");

Calling db_query() as above is equivalent to the following:

$result = Database::getConnection()->query("SELECT nid, title FROM {node}");

(See why the procedural wrapper version is preferred?)

db_query() takes three arguments. The first is the query string, using placeholders where appropriate and denoting all table names with curly braces. The second is an array of placeholder values. The third is, optionally, an array of configuration directives to dictate how the query will be run.

Prefixing

In static queries, all table names must be wrapped in {}. That flags them so that the database system can attach a prefix string to them if appropriate. Prefixing allows for running multiple sites from the same database or, in limited cases, for sharing selected tables between sites.

Placeholders

Placeholders mark where a literal will be inserted into a query for execution. By separating them out from the query itself, we allow the database to differentiate between SQL syntax and user-provided values, thus avoiding SQL injection.

$result = db_query("SELECT nid, title FROM {node} WHERE created > :created", array(
  ':created' => REQUEST_TIME - 3600,
));

The above code will select all nodes created within the past hour (3600 seconds). The placeholder :created will be dynamically replaced by whatever the value of REQUEST_TIME - 3600 is at the point the query is run. A query may have any number of placeholders, but all must have unique names even if they have the same value. Depending on the use case, the placeholders array may be specified inline (as above) or may be built beforehand and passed in. The order of the array does not matter.

Placeholders beginning with "db_" are reserved for internal system use and should never be specified explicitly.

Note that placeholders should not be escaped or quoted regardless of their type. Because they are passed to the database server separately, the server is able to differentiate between the query string and the value on its own.

// WRONG:
$result = db_query("SELECT nid, title FROM {node} WHERE type = ':type'", array(
  ':type' => 'page',
));

// CORRECT:
$result = db_query("SELECT nid, title FROM {node} WHERE type = :type", array(
  ':type' => 'page',
));

Placeholders should not (indeed, cannot) be used for column and table names. Instead, if these are derived from unsafe input, they should be run through db_escape_table().

Placeholder arrays

Drupal's database layer includes an extra feature of placeholders. If the value passed in for a placeholder is an array, it will be automatically expanded into a comma separated list as will the corresponding placeholder. That means developers do not need to worry about counting how many placeholders they will need.

An example should make this behavior clearer:

Drupal 7:

db_query("SELECT * FROM {node} WHERE nid IN (:nids)", array(':nids' => array(13, 42, 144)));

Drupal 8:

db_query("SELECT * FROM {node} WHERE nid IN (:nids[])", array(':nids[]' => array(13, 42, 144)));

The following two statements are both equivalent to the two statements above:

db_query("SELECT * FROM {node} WHERE nid IN (:nids_1, :nids_2, :nids_3)", array(
  ':nids_1' => 13, 
  ':nids_2' => 42, 
  ':nids_3' => 144,
));

db_query("SELECT * FROM {node} WHERE nid IN (13, 42, 144)");

Query options

The third parameter to db_query() (and to the query method of the connection object) is an array of options that direct how the query will behave. There are typically only two directives that will be used by most queries. The other values are mostly for internal use.

The "target" key specifies the target to use. If not specified, it defaults to "default". At present, the only other valid value is "slave", to indicate that a query should run against a slave server if one exists.

The "fetch" key specifies how records returned from that query will be retrieved. Legal values include PDO::FETCH_OBJ, PDO::FETCH_ASSOC, PDO::FETCH_NUM, PDO::FETCH_BOTH, or a string representing the name of a class. If a string is specified, each record will be fetched into a new object of that class. The behavior of all other values is defined by PDO, and will retrieve records as a stdClass object, an associative array, a numerical array, or an array keyed both numerically and associatively, respectively. See http://php.net/manual/en/pdostatement.fetch.php. The default is PDO::FETCH_OBJ, which for consistency should be used unless there is a specific reason to do otherwise.

The following example will execute a query against a slave server if available and fetch records from the result set as an associative array.

$result = db_query("SELECT nid, title FROM {node}", array(), array(
  'target' => 'slave',
  'fetch' => PDO::FETCH_ASSOC,
));

Using the result

The result object returned by a call to db_query() can be used to get each of the rows returned and then columns. In the following example, the $result variable has all the rows of the query returned, and then the individual rows are pulled one at a time into the $row variable using fetchAssoc():

  $sql = "SELECT name, quantity FROM goods WHERE vid = :vid";

  $result = db_query($sql, array(':vid' => $vid));
  
  if ($result) {
    while ($row = $result->fetchAssoc()) {
      // Do something with:
      //    $row['name']
      //    $row['quantity']
    }
  }

Looking for support? Visit the Drupal.org forums, or join #drupal-support in IRC.

Comments

nirbhasa’s picture

When I used a placeholder such as :link-path or :link_path I got the following error in my logs:

PDOException: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined:

Replacing the placeholder with :linkpath worked just fine. Apparently hyphens and underscores also have some kind of placeholder meaning. Perhaps someone can confirm this before I edit the docs.

75th Trombone’s picture

I'm using :remote_addr as a placeholder name in Drupal 7.39, and it seems to work correctly without generating warnings.

fmitchell’s picture

From Larry Garfield (crell) per Lullabot:

[T]he Select builder adds a LOT of overhead. You should not use it unless you need one of the things it offers: Dynamic query structure, hook_query_alter, certain db-unportable features that don't have a simpler version (random ordering, for instance), etc.

For the run of the mill SQL query, db_query() is several times faster than db_select(). If you need a limit query, db_query_range() is still there.

You must use the query builders for non-Select queries, because those are much harder to make portable due to stupidity in many older databases. (Oracle is worst, but Postgres is dumb in places, too.) The actual code you'd have to write for those is vastly different, so the query builders are a necessity. SELECT queries are far more standardized. db_query() is still your friend in many many cases.

Really, db_select() doesn't insulate you from as many db portability issues as you think. It doesn't do anything for SQL functions that differ between databases, doesn't help with date handling, etc. Its primary advantage is what Eaton talks about in this article: When your query structure may change depending on various conditions, it's now dead-simple to do so.

If your query will never change, don't waste the CPU cycles on it. Just write proper ANSI SQL in the first place and it will be sufficiently portable.

aaronbauman’s picture

It should be mentioned that Drupal 7 static queries do not support double quotes for string literals, even from static queries (and even though MySQL has no problem supporting them).
Single quotes are still OK for strings, but if your old D6 code has static queries using double-quoted strings (e.g. in a where clause) it will crash and burn.

See more discussion in #813310: db_query(): Static query with double quotes is not properly parsed and #783814: db_query() should whitelist queries

mikedotexe’s picture

For the fellow newbs, you can't just

dsm($result);

like you'd expect.
(dsm is of course, a great debugging function from the Devel module)

If you foreach through the $results you can view what's actually returned.
Also, there's a function not mentioned here at all, which is fetchAll()

To see the results you can also do:

dsm($result->fetchAll(), 'results');

Hakuna Matata

Elijah Lynn’s picture

Legacy function that was poorly named.

Use dpm() instead, since the 'p' maps to 'print_r'.

http://api.drupal.org/api/devel/devel.module/function/dsm/7

-----------------------------------------------
The Future is Open!

dgtlmoon’s picture

better still, use a good quality breakpoint debugger setup (xdebug + phpstorm)

tswaters’s picture

You can do something like this (D7):

  return theme('table', array(
    'header' => array(
      'Node ID',
      'Title'
    ),
    'rows' => db_select('node', 'n')
        ->fields('n', array('nid', 'title'))
        ->orderBy('nid', 'desc' )
        ->execute()
        ->fetchAll(PDO::FETCH_NUM)
    ));

Who needs loops anyway?

sun’s picture

Given the slightly snarky outro, I'm not sure whether the comment was meant as a fun/gimmick or not, but for everyone who isn't sure:

Do not do that. The queried data contains user input, which would be output unfiltered and unsanitized to everyone, opening your site up to XSS and CSRF attacks.

The raw queried data needs to be filtered and sanitized for HTML output first.

Daniel F. Kudwien
unleashed mind

fasteddy760’s picture

Did not know TSWaters comment was a joke.
Was studying and comparing it to other code to see the difference and why it *might* be a better approach. ;-/

Cayenne’s picture

To fully flesh out your security warning, is the issue that the user input (title or desc) is fed directly to a SQL query, or is it that the result of the query is fed directly, in unsanitized version, to another Drupal function that should not be fed unsanitized values?

selfsimilar’s picture

You might expect the output to look like:

<table>
<tr><td>value1</td></tr>
</table>

but if the output is malicious you might get instead:

<table>
<tr><td><script {redirect browser to bad site} /></td></tr>
</table>

or

<table>
<tr><td><iframe src="badsite"/></iframe></td></tr>
</table>

Or some other bad code that will execute in browser and take you in one way or another to a malware/phishing/drive-by site. Don't trust unsanitized user input,

keopx’s picture

Format to use like query:

$result = db_query('SELECT nid, title FROM {node} WHERE type = :type AND title LIKE :title', array(
    ':type'  => 'content_type',
    ':title' => "%$title%",
  ));
  if ($result) {
    while ($row = $result->fetchAssoc()) {
      // Do something with:
      //    $row['nid']
      //    $row['title']
    }
  }

Drupal and GNU/Linux enthusiast

diego.pasc’s picture

Maybe this helps somebody else as I haven't found this anywhere else in doc

First of all, better to point out again that a static INSERT (that is, using 'db_query') is strongly discouraged (see https://www.drupal.org/node/310079 at paragraph 'db_insert or db_query')

Nevertheless I needed to insert a record into a *not* drupal table (but in the same DB where Drupal tables are) => the table didn't have the usual drupal prefix

In this scenario db_insert cannot be used because it will automatically add the drupal 'prefix' to the table.....resulting in an error 'db table not found' of course.
(Actually I was expecting to find an option in db_insert to switch prefixing on/off.... but I haven't found it)

So I used db_query and, to get the just inserted primary key, I run the following:

$res = db_query("INSERT INTO not_drupal_table (field1, field2) VALUES (:field1, :field2)", array(':field1' => 123, ':field2' => 456));
$lastInsertId = $res->dbh->lastInsertId();

where $res->dbh holds the reference to the database connection object for this statement.

If you know a way to use db_insert for this scenario (or if you have some remarks/improvings to my piece of code), please let me know.