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.

<?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->query($query);
?>

With the third parameter of the quote() you can specify whether or not the above fields should be individually 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).

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

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 quoteIdentifiers option, all of the field identifiers will be automatically quoted in the resulting SQL statements:

$mdb2->setOption('quote_identifiers', 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.