Database abstraction layer

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 the PagerDefault
Extender for queries that return results that need to be presented on
multiple pages, and the Tablesort Extender 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

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 iterate over the result set using
foreach ().

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 should start with a colon ":" and can be
followed by one or more letters, numbers or underscores.

Named placeholders begin with a colon followed by a unique string. Example:

<?php
SELECTnid, titleFROM {node} WHEREuid=: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:

<?php
SELECTnid, titleFROM {node} WHEREuid=?;
?>

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:

<?php
SELECTnid, titleFROM {node} WHEREtitleLIKE :title%;
?>

It should instead read:

<?php
SELECTnid, titleFROM {node} WHEREtitleLIKE :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:

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.

Drupal also supports transactions, including a transparent fallback for
databases that do not support transactions. To start a new transaction,
simply call $txn = db_transaction(); in your own code. The transaction will
remain open for as long as the variable $txn remains in scope. When $txn is
destroyed, the transaction will be committed. If your transaction is nested
inside of another then Drupal will track each transaction and only commit
the outer-most transaction when the last transaction object goes out out of
scope, that is, all relevant queries completed successfully.