Quoting and escaping
Description
MDB2 provides a quote() method to quote a value into a DBMS specific format that is suitable to compose query statements. It has four parameters (only the first one is required): the value to be quoted, its datatype, whether or not to quote the value, and whether or not to escape the wildcards in the value. If you don't provide the datatype, it will be guessed from the value.
Doing a query with quoted values
<?php
// Create a valid MDB2 object named $mdb2
// at the beginning of your program...
require_once 'MDB2.php';
$mdb2 =& MDB2::connect('pgsql://usr:pw@localhost/dbnam');
if (PEAR::isError($mdb2)) {
die($mdb2->getMessage());
}
// some sample values to be inserted
$id = 1;
$name = 'sample item';
$time = date('Y-m-d H:i:s');
// Proceed with a query...
$query = 'INSERT INTO tablename (id, itemname, saved_time) VALUES ('
. $mdb2->quote($id, 'integer') .', '
. $mdb2->quote($name, 'text') .', '
. $mdb2->quote($time, 'timestamp') .')';
$res =& $mdb2->exec($query);
?>
With the third parameter of the quote() you can specify whether or not the above fields should be individually quoted:
Individually choose the values to be quoted
<?php
$query = 'INSERT INTO sometable (textfield1, boolfield2, datefield3) VALUES ('
.$mdb2->quote($val1, "text", true).', '
.$mdb2->quote($val2, "boolean", false).', '
.$mdb2->quote($val3, "date", true).')';
?>
The above example will quote the fields and the resulting SQL will look as such:
INSERT INTO sometable FIELDS (textfield1, boolfield2, datefield3) VALUES ('blah', 1, '2006-02-21')
where the values defined were the values inserted accordingly. You will notice that the "boolfield2" is unquoted as we specified FALSE in the quote() method.
NB: If you use prepared statements, then quoting will be done automatically, you don't need to do it yourself.
Identifiers
You can quote the db identifiers (table and field names) with quoteIdentifier(). The delimiting style depends on which database driver is being used. NOTE: just because you CAN use delimited identifiers, it doesn't mean you SHOULD use them. In general, they end up causing way more problems than they solve. Anyway, it may be necessary when you have a reserved word as a field name (in this case, we suggest you to change it, if you can). Also, don't use quoteIdentifier() if you have a period in the table name itself (which, BTW, is a really bad idea), since it will consider it as a schema.table pair.
Some of the internal MDB2 methods generate queries. Enabling the quote_identifier
option of MDB2 you can tell MDB2 to quote the identifiers in these generated
queries. For all user supplied queries this option is irrelevant.
Portability is broken by using the following characters inside delimited identifiers:
-
backtick (
`) -- due to MySQL -
double quote (") -- due to Oracle
-
brackets ([ or ]) -- due to Access
Delimited identifiers are known to generally work correctly under the following drivers:
mssql
mysql
mysqli
oci8
pgsql
sqlite
Firebird/InterBase doesn't seem to be able to use delimited identifiers via PHP 4. They work fine under PHP 5.
Quoting options
Within the MDB2 API there are a number of options to set the quoting options, one of which simply quotes the identifiers within the abstraction, the other quotes the field values on insert/update etc. when using the prepared statements methods.
When using the quote_identifier option, all of the
field identifiers will be automatically quoted in the resulting SQL statements:
<?php
$mdb2->setOption('quote_identifier', true);
?>
will result in a SQL statement that all the field names are quoted with the
backtick '`' operator (in MySQL).
SELECT * FROM `sometable` WHERE `id` = '123';
as opposed to:
SELECT * FROM sometable WHERE id='123';
Escape
If you want to escape a value, without surrounding it with quotes, you can use the escape() method. If you also want to escape the wildcards (_ and %), set the second parameter to TRUE
If you just want to escape the wildcards in a value, you can use the escapePattern() method.