[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:

              $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 
            quotation marks.

              $sql = sprintf "SELECT foo FROM bar WHERE baz = %s",

            For most database types, quote would return 'Don''t' 
(including the
            outer quotation marks).

            An undefined $value value will be returned as the string 
            (without single quotation marks) to match how NULLs are 
            in SQL.

            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 
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 



More information about the Pdx-pm-list mailing list