[Pdx-pm] Escaping strings for SQL insertion
david at kineticode.com
Tue Feb 15 17:28:20 PST 2005
On Feb 15, 2005, at 4:18 PM, Nathaniel Powning wrote:
> In good practice the strings should be kept separate from the query, in
> DBI you should prepare your query using question marks as place holders
> and set their values using bind_param.
But if, for whatever reason, you can't do that, use the DBI's quote()
method. From the docs:
$sql = $dbh->quote($value);
$sql = $dbh->quote($value, $data_type);
Quote a string literal for use as a literal value in an SQL
ment, by escaping any special characters (such as quotation
contained within the string and adding the required type of
$sql = sprintf "SELECT foo FROM bar WHERE baz = %s",
For most database types, quote would return 'Don''t'
outer quotation marks).
An undefined $value value will be returned as the string
(without single quotation marks) to match how NULLs are
If $data_type is supplied, it is used to try to determine the
required quoting behaviour by using the information returned
"type_info". As a special case, the standard numeric types
optimized to return $value without calling "type_info".
Quote will probably not be able to deal with all possible
(such as binary data or data containing newlines), and is not
related in any way with escaping or quoting shell
It is valid for the quote() method to return an SQL
evaluates to the desired string. For example:
$quoted = $dbh->quote("one\ntwo\0three")
may return something like:
CONCAT('one', CHAR(12), 'two', CHAR(0), 'three')
The quote() method should not be used with "Placeholders and
More information about the Pdx-pm-list