Database abstraction layer
Allow the use of different database servers using the same code base.
Drupal provides a database abstraction layer to provide developers with the ability to support multiple database servers easily. The intent of this layer is to preserve the syntax and power of SQL as much as possible, but also allow developers a way to leverage more complex functionality in a unified way. It also provides a structured interface for dynamically constructing queries when appropriate, and enforcing security checks and similar good practices.
The system is built atop PHP's PDO (PHP Data Objects) database API and inherits much of its syntax and semantics.
Most Drupal database SELECT queries are performed by a call to db_query() or db_query_range(). Module authors should also consider using pager_query() for queries that return results that need to be presented on multiple pages, and tablesort_sql() for generating appropriate queries for sortable tables.
For example, one might wish to return a list of the most recent 10 nodes authored by a given user. Instead of directly issuing the SQL query
SELECT n.nid, n.title, n.created FROM node n WHERE n.uid = $uid LIMIT 0, 10;
one would instead call the Drupal functions:
$result = db_query_range('SELECT n.nid, n.title, n.created
FROM {node} n WHERE n.uid = :uid', array(':uid' => $uid), 0, 10);
foreach($result as $record) {
// Perform operations on $node->title, etc. here.
}
Curly braces are used around "node" to provide table prefixing via DatabaseConnection::prefixTables(). The explicit use of a user ID is pulled out into an argument passed to db_query() so that SQL injection attacks from user input can be caught and nullified. The LIMIT syntax varies between database servers, so that is abstracted into db_query_range() arguments. Finally, note the PDO-based ability to foreach() over the result set.
All queries are passed as a prepared statement string. A prepared statement is a "template" of a query that omits literal or variable values in favor of placeholders. The values to place into those placeholders are passed separately, and the database driver handles inserting the values into the query in a secure fashion. That means you should never quote or string-escape a value to be inserted into the query.
There are two formats for placeholders: named and unnamed. Named placeholders are strongly preferred in all cases as they are more flexible and self-documenting.
Named placeholders begin with a colon followed by a unique string. Example:
SELECT nid, title FROM {node} WHERE uid=:uid
":uid" is a placeholder that will be replaced with a literal value when the query is executed. A given placeholder label cannot be repeated in a given query, even if the value should be the same. When using named placeholders, the array of arguments to the query must be an associative array where keys are a placeholder label (e.g., :uid) and the value is the corresponding value to use. The array may be in any order.
Unnamed placeholders are simply a question mark. Example:
SELECT nid, title FROM {node} WHERE uid=?
In this case, the array of arguments must be an indexed array of values to use in the exact same order as the placeholders in the query.
Note that placeholders should be a "complete" value. For example, when running a LIKE query the SQL wildcard character, %, should be part of the value, not the query itself. Thus, the following is incorrect:
SELECT nid, title FROM {node} WHERE title LIKE :title%
It should instead read:
SELECT nid, title FROM {node} WHERE title LIKE :title
and the value for :title should include a % as appropriate. Again, note the lack of quotation marks around :title. Because the value is not inserted into the query as one big string but as an explicitly separate value, the database server knows where the query ends and a value begins. That is considerably more secure against SQL injection than trying to remember which values need quotation marks and string escaping and which don't.
INSERT, UPDATE, and DELETE queries need special care in order to behave consistently across all different databases. Therefore, they use a special object-oriented API for defining a query structurally. For example, rather than
INSERT INTO node (nid, title, body) VALUES (1, 'my title', 'my body')
one would instead write:
$fields = array('nid' => 1, 'title' => 'my title', 'body' => 'my body');
db_insert('my_table')->fields($fields)->execute();
This method allows databases that need special data type handling to do so, while also allowing optimizations such as multi-insert queries. UPDATE and DELETE queries have a similar pattern.
Functions
Name | Location | Description |
|---|---|---|
| db_affected_rows | drupal/includes/database/database.inc | Determine the number of rows changed by the preceding query. |
| db_and | drupal/includes/database/query.inc | Returns a new DatabaseCondition, set to "AND" all conditions together. |
| db_column_exists | drupal/includes/database/database.inc | Check if a column exists in the given table. |
| db_condition | drupal/includes/database/query.inc | Returns a new DatabaseCondition, set to the specified conjunction. |
| db_delete | drupal/includes/database/database.inc | Returns a new DeleteQuery object for the active database. |
| db_distinct_field | drupal/includes/database/database.inc | Wraps the given table.field entry with a DISTINCT(). The wrapper is added to the SELECT list entry of the given query and the resulting query is returned. This function only applies the wrapper if a DISTINCT doesn't already exist in the query. |
| db_driver | drupal/includes/database/database.inc | Retrieve the name of the currently active database driver, such as "mysql" or "pgsql". |
| db_escape_table | drupal/includes/database/database.inc | Restrict a dynamic table, column or constraint name to safe characters. |
| db_fetch_array | drupal/includes/database/database.inc | |
| db_fetch_object | drupal/includes/database/database.inc | |
| db_insert | drupal/includes/database/database.inc | Returns a new InsertQuery object for the active database. |
| db_is_active | drupal/includes/database/database.inc | Determine if there is an active connection. |
| db_last_insert_id | drupal/includes/database/database.inc | Returns the last insert id. |
| db_merge | drupal/includes/database/database.inc | Returns a new MergeQuery object for the active database. |
| db_or | drupal/includes/database/query.inc | Returns a new DatabaseCondition, set to "OR" all conditions together. |
| db_placeholders | drupal/includes/database/database.inc | Generate placeholders for an array of query arguments of a single type. |
| db_query | drupal/includes/database/database.inc | Execute an arbitrary query string against the active database. |
| db_query_range | drupal/includes/database/database.inc | Execute an arbitrary query string against the active database, restricted to a specified range. |
| db_query_temporary | drupal/includes/database/database.inc | Execute a query string against the active database and save the result set to a temp table. |
| db_result | drupal/includes/database/database.inc | |
| db_rewrite_sql | drupal/includes/database/database.inc | Rewrites node, taxonomy and comment queries. Use it for listing queries. Do not use FROM table1, table2 syntax, use JOIN instead. |
| db_select | drupal/includes/database/database.inc | Returns a new SelectQuery object for the active database. |
| db_set_active | drupal/includes/database/database.inc | Sets a new active database. |
| db_table_exists | drupal/includes/database/database.inc | Check if a table exists. |
| db_update | drupal/includes/database/database.inc | Returns a new UpdateQuery object for the active database. |
| db_xor | drupal/includes/database/query.inc | Returns a new DatabaseCondition, set to "XOR" all conditions together. |
| pager_query | drupal/includes/pager.inc | Perform a paged database query. |
| tablesort_sql | drupal/includes/tablesort.inc | Create an SQL sort clause. |
| update_sql | drupal/includes/database/database.inc | Perform an SQL query and return success or failure. |
| _db_error_page | drupal/includes/database/database.inc | Prints a themed maintenance page with the 'Site offline' text, adding the provided error message in the case of 'display_errors' set to on. Ends the page request; no return. |
| _db_query_process_args | drupal/includes/database/database.inc | Backward-compatibility utility. |
| _db_rewrite_sql | drupal/includes/database/database.inc | Helper function for db_rewrite_sql. |