Home » Developer documentation » Coding standards

SQL coding conventions

Don't use Reserved Words

Don't use (ANSI) SQL / MySQL / PostgreSQL / MS SQL Server / ... Reserved Words for column and/or table names. Even if this may work with your (MySQL) installation, it may not with others or with other databases. Some references:

Some commonly misused keywords: TIMESTAMP, TYPE, TYPES, MODULE, DATA, DATE, TIME, ...
See also [bug] SQL Reserved Words.

Capitalization, indentation, user-supplied data

  • UPPERCASE reserved words.
  • lowercase table, column and constraint names.
  • Enclose each table name with {} (this allows Drupal to prefix table names).
  • User-supplied arguments should be moved out of the query body and passed in as separate parameters to <a href="http://api.drupal.org/apis/db_query">db_query()</a>, <a href="http://api.drupal.org/apis/db_query_range">db_query_range()</a>, and <a href="http://api.drupal.org/apis/db_query_temporary">db_query_temporary()</a>, etc. The query body should only contain placeholders specifying the type of the arguments (%d|%s|%%|%f|%b). This ensures that the data will be properly escaped and avoids SQL injection attacks.
  • Any string literal or %s placeholder must be enclosed by single quotes: ' Never use double quotes.

Example:

<?php
  db_query("INSERT INTO {node_access} (nid, gid, realm, grant_view, grant_update, grant_delete) VALUES (%d, %d, '%s', %d, %d, %d)", 0, 0, 'all', 1, 0, 0);
?>

NOTE: as of Drupal 6.x, table definitions and constraints (e.g. primary keys, unique keys, indexes) should be always handled by the Schema API, which solves cross-database compatibility concerns automatically.

Naming

  • Use plural or collective nouns for table names since they are sets and not scalar values. (Others prefer table names to match field names and prefer singular table names. Drupal uses both: blocks, filters, users; but book, poll, role.)
  • Name every constraint (primary, foreign, unique keys) yourself. Otherwise, you'll see funny-looking system-generated names in error messages. This happened with the moderation_roles table which initially defined a key without explicit name as KEY (mid). This got mysqldump'ed as KEY mid (mid) which resulted in a syntax error as mid() is a mysql function (see [bug] mysql --ansi cannot import install database).
  • Index names should begin with the name of the table they depend on, eg. INDEX users_sid_idx.

NOTE: as of Drupal 6.x, table definitions and constraints should be always handled by the Schema API.

Configure your Database server for standard compliance

Most Database Servers use extension to standard SQL. However, many of them can be configured to run in a (more) standard compliant mode. Every developer is encouraged to use the mode most standard compliant to avoid sloppy coding and compatibilty problems.

MySQL
Enable ANSI and Strict Mode

Please help growing this list for other database servers!

References

‹ Header comment blocksupList of SQL Reserved Words ›
» Login or register to post comments

Oracle reserved words

müzso - March 18, 2007 - 11:13

Recently there were some efforts on making Drupal work on Oracle databases. I think it might be time to add a link in the documenation to Oracle Database Reserved Words too.

Login or register to post comments

Missing description for fields

Thomas_Zahreddin - December 1, 2007 - 09:28

in the coding standards and in many modules I miss the desciption for fields created in tables of the database.

Please add this point to the coding standards.

Thanks

Login or register to post comments
 
 

Drupal is a registered trademark of Dries Buytaert.