[Pdx-pm] Escaping strings for SQL insertion
David Wheeler
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:
"quote"
$sql = $dbh->quote($value);
$sql = $dbh->quote($value, $data_type);
Quote a string literal for use as a literal value in an SQL
state-
ment, by escaping any special characters (such as quotation
marks)
contained within the string and adding the required type of
outer
quotation marks.
$sql = sprintf "SELECT foo FROM bar WHERE baz = %s",
$dbh->quote("Don't");
For most database types, quote would return 'Don''t'
(including the
outer quotation marks).
An undefined $value value will be returned as the string
"NULL"
(without single quotation marks) to match how NULLs are
represented
in SQL.
If $data_type is supplied, it is used to try to determine the
required quoting behaviour by using the information returned
by
"type_info". As a special case, the standard numeric types
are
optimized to return $value without calling "type_info".
Quote will probably not be able to deal with all possible
input
(such as binary data or data containing newlines), and is not
related in any way with escaping or quoting shell
meta-characters.
It is valid for the quote() method to return an SQL
expression that
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
Bind
Values".
Regards,
David
More information about the Pdx-pm-list
mailing list