[Pdx-pm] Escaping strings for SQL insertion

[David Wheeler]

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