PhpRiot
Become Zend Certified

Prepare for the ZCE exam using our quizzes (web or iPad/iPhone). More info...


When you're ready get 7.5% off your exam voucher using voucher CJQNOV23 at the Zend Store

Quoting Values and Identifiers

When you form SQL queries, often it is the case that you need to include the values of PHP variables in SQL expressions. This is risky, because if the value in a PHP string contains certain symbols, such as the quote symbol, it could result in invalid SQL. For example, notice the imbalanced quote characters in the following query:

<?php
$name 
"O'Reilly";
$sql "SELECT * FROM bugs WHERE reported_by = '$name'";

echo 
$sql;
// SELECT * FROM bugs WHERE reported_by = 'O'Reilly'

Even worse is the risk that such code mistakes might be exploited deliberately by a person who is trying to manipulate the function of your web application. If they can specify the value of a PHP variable through the use of an HTTP parameter or other mechanism, they might be able to make your SQL queries do things that you didn't intend them to do, such as return data to which the person should not have privilege to read. This is a serious and widespread technique for violating application security, known as "SQL Injection" (see http://en.wikipedia.org/wiki/SQL_Injection).

The Zend_Db Adapter class provides convenient functions to help you reduce vulnerabilities to SQL Injection attacks in your PHP code. The solution is to escape special characters such as quotes in PHP values before they are interpolated into your SQL strings. This protects against both accidental and deliberate manipulation of SQL strings by PHP variables that contain special characters.

Using quote()

The quote() method accepts a single argument, a scalar string value. It returns the value with special characters escaped in a manner appropriate for the RDBMS you are using, and surrounded by string value delimiters. The standard SQL string value delimiter is the single-quote (').

Example 211. Using quote()

<?php
$name 
$db->quote("O'Reilly");
echo 
$name;
// 'O\'Reilly'

$sql "SELECT * FROM bugs WHERE reported_by = $name";

echo 
$sql;
// SELECT * FROM bugs WHERE reported_by = 'O\'Reilly'

Note that the return value of quote() includes the quote delimiters around the string. This is different from some functions that escape special characters but do not add the quote delimiters, for example mysql_real_escape_string().

Values may need to be quoted or not quoted according to the SQL datatype context in which they are used. For instance, in some RDBMS brands, an integer value must not be quoted as a string if it is compared to an integer-type column or expression. In other words, the following is an error in some SQL implementations, assuming intColumn has a SQL datatype of INTEGER

<?php
SELECT 
FROM atable WHERE intColumn '123'

You can use the optional second argument to the quote() method to apply quoting selectively for the SQL datatype you specify.

Example 212. Using quote() with a SQL Type

<?php
$value 
'1234';
$sql 'SELECT * FROM atable WHERE intColumn = '
     
$db->quote($value'INTEGER');

Each Zend_Db_Adapter class has encoded the names of numeric SQL datatypes for the respective brand of RDBMS. You can also use the constants Zend_Db::INT_TYPE, Zend_Db::BIGINT_TYPE, and Zend_Db::FLOAT_TYPE to write code in a more RDBMS-independent way.

Zend_Db_Table specifies SQL types to quote() automatically when generating SQL queries that reference a table's key columns.

Using quoteInto()

The most typical usage of quoting is to interpolate a PHP variable into a SQL expression or statement. You can use the quoteInto() method to do this in one step. This method takes two arguments: the first argument is a string containing a placeholder symbol (?), and the second argument is a value or PHP variable that should be substituted for that placeholder.

The placeholder symbol is the same symbol used by many RDBMS brands for positional parameters, but the quoteInto() method only emulates query parameters. The method simply interpolates the value into the string, escapes special characters, and applies quotes around it. True query parameters maintain the separation between the SQL string and the parameters as the statement is parsed in the RDBMS server.

Example 213. Using quoteInto()

<?php
$sql 
$db->quoteInto("SELECT * FROM bugs WHERE reported_by = ?""O'Reilly");

echo 
$sql;
// SELECT * FROM bugs WHERE reported_by = 'O\'Reilly'

You can use the optional third parameter of quoteInto() to specify the SQL datatype. Numeric datatypes are not quoted, and other types are quoted.

Example 214. Using quoteInto() with a SQL Type

<?php
$sql 
$db
    
->quoteInto("SELECT * FROM bugs WHERE bug_id = ?"'1234''INTEGER');

echo 
$sql;
// SELECT * FROM bugs WHERE reported_by = 1234

Using quoteIdentifier()

Values are not the only part of SQL syntax that might need to be variable. If you use PHP variables to name tables, columns, or other identifiers in your SQL statements, you might need to quote these strings too. By default, SQL identifiers have syntax rules like PHP and most other programming languages. For example, identifiers should not contain spaces, certain punctuation or special characters, or international characters. Also certain words are reserved for SQL syntax, and should not be used as identifiers.

However, SQL has a feature called delimited identifiers, which allows broader choices for the spelling of identifiers. If you enclose a SQL identifier in the proper types of quotes, you can use identifiers with spellings that would be invalid without the quotes. Delimited identifiers can contain spaces, punctuation, or international characters. You can also use SQL reserved words if you enclose them in identifier delimiters.

The quoteIdentifier() method works like quote(), but it applies the identifier delimiter characters to the string according to the type of Adapter you use. For example, standard SQL uses double-quotes (") for identifier delimiters, and most RDBMS brands use that symbol. MySQL uses back-quotes (`) by default. The quoteIdentifier() method also escapes special characters within the string argument.

Example 215. Using quoteIdentifier()

<?php
// we might have a table name that is an SQL reserved word
$tableName $db->quoteIdentifier("order");

$sql "SELECT * FROM $tableName";

echo 
$sql
// SELECT * FROM "order"

SQL delimited identifiers are case-sensitive, unlike unquoted identifiers. Therefore, if you use delimited identifiers, you must use the spelling of the identifier exactly as it is stored in your schema, including the case of the letters.

In most cases where SQL is generated within Zend_Db classes, the default is that all identifiers are delimited automatically. You can change this behavior with the option Zend_Db::AUTO_QUOTE_IDENTIFIERS. Specify this when instantiating the Adapter. See this example.

Zend Framework