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.
Example 39-1. Doing a query with quoted values
With the third parameter of the quote() you can specify whether or not the above fields should be individually quoted:
Example 39-2. Individually choose the values to be quoted
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')
NB: If you use prepared statements, then quoting will be done automatically, you don't need to do it yourself.
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:
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:
SELECT * FROM `sometable` WHERE `id` = '123';
SELECT * FROM sometable WHERE id='123';
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.