database.inc

  1. 7.x drupal/includes/database/database.inc
  2. 7.x drupal/includes/database/pgsql/database.inc
  3. 7.x drupal/includes/database/sqlite/database.inc
  4. 7.x drupal/includes/database/mysql/database.inc
  5. 5.x drupal/includes/database.inc
  6. 6.x drupal/includes/database.inc
  7. 8.x drupal/core/includes/database.inc

Wrapper for database interface code.

Functions

Namesort descending Description
db_escape_table Restrict a dynamic tablename to safe characters.
db_prefix_tables Append a database prefix to all tables in a query.
db_query Runs a basic query in the active database.
db_rewrite_sql Rewrites node, taxonomy and comment queries. Use it for listing queries. Do not use FROM table1, table2 syntax, use JOIN instead.
db_set_active Activate a database for future queries.
_db_query_callback Helper function for db_query().
_db_rewrite_sql Helper function for db_rewrite_sql.

Constants

Namesort descending Description
DB_QUERY_REGEXP Indicates the place holders that should be replaced in _db_query_callback().

File

drupal/includes/database.inc
View source
  1. <?php
  2. /**
  3. * @file
  4. * Wrapper for database interface code.
  5. */
  6. /**
  7. * @defgroup database Database abstraction layer
  8. * @{
  9. * Allow the use of different database servers using the same code base.
  10. *
  11. * Drupal provides a slim database abstraction layer to provide developers with
  12. * the ability to support multiple database servers easily. The intent of this
  13. * layer is to preserve the syntax and power of SQL as much as possible, while
  14. * letting Drupal control the pieces of queries that need to be written
  15. * differently for different servers and provide basic security checks.
  16. *
  17. * Most Drupal database queries are performed by a call to db_query() or
  18. * db_query_range(). Module authors should also consider using pager_query() for
  19. * queries that return results that need to be presented on multiple pages, and
  20. * tablesort_sql() for generating appropriate queries for sortable tables.
  21. *
  22. * For example, one might wish to return a list of the most recent 10 nodes
  23. * authored by a given user. Instead of directly issuing the SQL query
  24. * @code
  25. * SELECT n.title, n.body, n.created FROM node n WHERE n.uid = $uid LIMIT 0, 10;
  26. * @endcode
  27. * one would instead call the Drupal functions:
  28. * @code
  29. * $result = db_query_range('SELECT n.title, n.body, n.created
  30. * FROM {node} n WHERE n.uid = %d', $uid, 0, 10);
  31. * while ($node = db_fetch_object($result)) {
  32. * // Perform operations on $node->body, etc. here.
  33. * }
  34. * @endcode
  35. * Curly braces are used around "node" to provide table prefixing via
  36. * db_prefix_tables(). The explicit use of a user ID is pulled out into an
  37. * argument passed to db_query() so that SQL injection attacks from user input
  38. * can be caught and nullified. The LIMIT syntax varies between database servers,
  39. * so that is abstracted into db_query_range() arguments. Finally, note the
  40. * common pattern of iterating over the result set using db_fetch_object().
  41. */
  42. /**
  43. * Append a database prefix to all tables in a query.
  44. *
  45. * Queries sent to Drupal should wrap all table names in curly brackets. This
  46. * function searches for this syntax and adds Drupal's table prefix to all
  47. * tables, allowing Drupal to coexist with other systems in the same database if
  48. * necessary.
  49. *
  50. * @param $sql
  51. * A string containing a partial or entire SQL query.
  52. * @return
  53. * The properly-prefixed string.
  54. */
  55. function db_prefix_tables($sql) {
  56. global $db_prefix;
  57. if (is_array($db_prefix)) {
  58. if (array_key_exists('default', $db_prefix)) {
  59. $tmp = $db_prefix;
  60. unset($tmp['default']);
  61. foreach ($tmp as $key => $val) {
  62. $sql = strtr($sql, array('{'. $key. '}' => $val. $key));
  63. }
  64. return strtr($sql, array('{' => $db_prefix['default'], '}' => ''));
  65. }
  66. else {
  67. foreach ($db_prefix as $key => $val) {
  68. $sql = strtr($sql, array('{'. $key. '}' => $val. $key));
  69. }
  70. return strtr($sql, array('{' => '', '}' => ''));
  71. }
  72. }
  73. else {
  74. return strtr($sql, array('{' => $db_prefix, '}' => ''));
  75. }
  76. }
  77. /**
  78. * Activate a database for future queries.
  79. *
  80. * If it is necessary to use external databases in a project, this function can
  81. * be used to change where database queries are sent. If the database has not
  82. * yet been used, it is initialized using the URL specified for that name in
  83. * Drupal's configuration file. If this name is not defined, a duplicate of the
  84. * default connection is made instead.
  85. *
  86. * Be sure to change the connection back to the default when done with custom
  87. * code.
  88. *
  89. * @param $name
  90. * The name assigned to the newly active database connection. If omitted, the
  91. * default connection will be made active.
  92. *
  93. * @return the name of the previously active database or FALSE if non was found.
  94. */
  95. function db_set_active($name = 'default') {
  96. global $db_url, $db_type, $active_db;
  97. static $db_conns, $active_name = FALSE;
  98. if (!isset($db_conns[$name])) {
  99. // Initiate a new connection, using the named DB URL specified.
  100. if (is_array($db_url)) {
  101. $connect_url = array_key_exists($name, $db_url) ? $db_url[$name] : $db_url['default'];
  102. }
  103. else {
  104. $connect_url = $db_url;
  105. }
  106. $db_type = substr($connect_url, 0, strpos($connect_url, '://'));
  107. $handler = "./includes/database.$db_type.inc";
  108. if (is_file($handler)) {
  109. include_once $handler;
  110. }
  111. else {
  112. drupal_maintenance_theme();
  113. drupal_set_title('Unsupported database type');
  114. print theme('maintenance_page', '<p>The database type '. theme('placeholder', $db_type) .' is unsupported. Please use either <var>mysql</var> for MySQL 3.x &amp; 4.0.x databases, <var>mysqli</var> for MySQL 4.1.x+ databases, or <var>pgsql</var> for PostgreSQL databases. The database information is in your <code>settings.php</code> file.</p>
  115. <p>For more help, see the <a href="http://drupal.org/node/258">Installation and upgrading handbook</a>. If you are unsure what these terms mean you should probably contact your hosting provider.</p>');
  116. exit;
  117. }
  118. $db_conns[$name] = db_connect($connect_url);
  119. }
  120. $previous_name = $active_name;
  121. // Set the active connection.
  122. $active_name = $name;
  123. $active_db = $db_conns[$name];
  124. return $previous_name;
  125. }
  126. /**
  127. * Helper function for db_query().
  128. */
  129. function _db_query_callback($match, $init = FALSE) {
  130. static $args = NULL;
  131. if ($init) {
  132. $args = $match;
  133. return;
  134. }
  135. switch ($match[1]) {
  136. case '%d': // We must use type casting to int to convert FALSE/NULL/(TRUE?)
  137. return (int) array_shift($args); // We don't need db_escape_string as numbers are db-safe
  138. case '%s':
  139. return db_escape_string(array_shift($args));
  140. case '%%':
  141. return '%';
  142. case '%f':
  143. return (float) array_shift($args);
  144. case '%b': // binary data
  145. return db_encode_blob(array_shift($args));
  146. }
  147. }
  148. /**
  149. * Indicates the place holders that should be replaced in _db_query_callback().
  150. */
  151. define('DB_QUERY_REGEXP', '/(%d|%s|%%|%f|%b)/');
  152. /**
  153. * Runs a basic query in the active database.
  154. *
  155. * User-supplied arguments to the query should be passed in as separate
  156. * parameters so that they can be properly escaped to avoid SQL injection
  157. * attacks.
  158. *
  159. * @param $query
  160. * A string containing an SQL query.
  161. * @param ...
  162. * A variable number of arguments which are substituted into the query
  163. * using printf() syntax. Instead of a variable number of query arguments,
  164. * you may also pass a single array containing the query arguments.
  165. *
  166. * Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
  167. * in '') and %%.
  168. *
  169. * NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
  170. * and TRUE values to decimal 1.
  171. *
  172. * @return
  173. * A database query result resource, or FALSE if the query was not
  174. * executed correctly.
  175. */
  176. function db_query($query) {
  177. $args = func_get_args();
  178. array_shift($args);
  179. $query = db_prefix_tables($query);
  180. if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
  181. $args = $args[0];
  182. }
  183. _db_query_callback($args, TRUE);
  184. $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
  185. return _db_query($query);
  186. }
  187. /**
  188. * Helper function for db_rewrite_sql.
  189. *
  190. * Collects JOIN and WHERE statements via hook_db_rewrite_sql().
  191. * Decides whether to select primary_key or DISTINCT(primary_key)
  192. *
  193. * @param $query
  194. * Query to be rewritten.
  195. * @param $primary_table
  196. * Name or alias of the table which has the primary key field for this query. Possible values are: comments, forum, node, menu, term_data, vocabulary.
  197. * @param $primary_field
  198. * Name of the primary field.
  199. * @param $args
  200. * Array of additional arguments.
  201. * @return
  202. * An array: join statements, where statements, field or DISTINCT(field).
  203. */
  204. function _db_rewrite_sql($query = '', $primary_table = 'n', $primary_field = 'nid', $args = array()) {
  205. $where = array();
  206. $join = array();
  207. $distinct = FALSE;
  208. foreach (module_implements('db_rewrite_sql') as $module) {
  209. $result = module_invoke($module, 'db_rewrite_sql', $query, $primary_table, $primary_field, $args);
  210. if (isset($result) && is_array($result)) {
  211. if (isset($result['where'])) {
  212. $where[] = $result['where'];
  213. }
  214. if (isset($result['join'])) {
  215. $join[] = $result['join'];
  216. }
  217. if (isset($result['distinct']) && $result['distinct']) {
  218. $distinct = TRUE;
  219. }
  220. }
  221. elseif (isset($result)) {
  222. $where[] = $result;
  223. }
  224. }
  225. $where = empty($where) ? '' : '('. implode(') AND (', $where) .')';
  226. $join = empty($join) ? '' : implode(' ', $join);
  227. return array($join, $where, $distinct);
  228. }
  229. /**
  230. * Rewrites node, taxonomy and comment queries. Use it for listing queries. Do not
  231. * use FROM table1, table2 syntax, use JOIN instead.
  232. *
  233. * @param $query
  234. * Query to be rewritten.
  235. * @param $primary_table
  236. * Name or alias of the table which has the primary key field for this query. Possible values are: {comments}, {forum}, {node}, {menu}, {term_data}, {vocabulary}.
  237. * @param $primary_field
  238. * Name of the primary field.
  239. * @param $args
  240. * An array of arguments, passed to the implementations of hook_db_rewrite_sql.
  241. * @return
  242. * The original query with JOIN and WHERE statements inserted from hook_db_rewrite_sql implementations. nid is rewritten if needed.
  243. */
  244. function db_rewrite_sql($query, $primary_table = 'n', $primary_field = 'nid', $args = array()) {
  245. list($join, $where, $distinct) = _db_rewrite_sql($query, $primary_table, $primary_field, $args);
  246. if ($distinct) {
  247. $query = db_distinct_field($primary_table, $primary_field, $query);
  248. }
  249. if (!empty($where) || !empty($join)) {
  250. if (!empty($where)) {
  251. $new = "WHERE $where ";
  252. }
  253. $new = " $join $new";
  254. if (strpos($query, 'WHERE')) {
  255. $query = str_replace('WHERE', $new .'AND (', $query);
  256. $insert = ') ';
  257. }
  258. else {
  259. $insert = $new;
  260. }
  261. if (strpos($query, 'GROUP')) {
  262. $replace = 'GROUP';
  263. }
  264. elseif (strpos($query, 'HAVING')) {
  265. $replace = 'HAVING';
  266. }
  267. elseif (strpos($query, 'ORDER')) {
  268. $replace = 'ORDER';
  269. }
  270. elseif (strpos($query, 'LIMIT')) {
  271. $replace = 'LIMIT';
  272. }
  273. else {
  274. $query .= $insert;
  275. }
  276. if (isset($replace)) {
  277. $query = str_replace($replace, $insert . $replace, $query);
  278. }
  279. }
  280. return $query;
  281. }
  282. /**
  283. * Restrict a dynamic tablename to safe characters.
  284. *
  285. * Only keeps alphanumeric and underscores.
  286. */
  287. function db_escape_table($string) {
  288. return preg_replace('/[^A-Za-z0-9_]+/', '', $string);
  289. }
  290. /**
  291. * @} End of "defgroup database".
  292. */