[Mimedefang] Off Topic: How to UN-Quote the DBI quote()

[Michael Sims]

Quoting schmaus <schmaus at ties2.net>:

> I am using the DBI quote() to store emails in a DB per Phils
> suggestion.  My problem is when I bring the message back
> into a variable to view via my web interface, the email has
> all kinds of stuff besides the original email embedded in
> the email.  How can I do the reverse of a quote() when
> reading from the DB?

In general, quoting a string that is to be used in an SQL statement involves
escaping any characters within that string that have special significance to the
SQL engine's parser.  For example, a single quote means the end of a string
literal to most SQL engines, so if you need to insert a literal single quote you
have to escape it, using various syntax depending on the SQL engine you are
using.  For example, if you wanted to insert the following string:

I think it's going to rain.

Quoting this string would give you something like this (for PostgreSQL):

insert into ... values ('I think it\'s going to rain.');

PostgreSQL also accepts a doubling of the single quote to escape it (IIRC):

insert into ... values ('I think it''s going to rain.');

The important thing to remember about quoting is that it is only a method to
escape SQL metacharacters so you can insert them into the database.  In the
examples above, the backslash (or double single-quotes) are not actually
literally inserted into the database.  In either case the field is going to
contain the exact string:

I think it's going to rain.

So, when you select the data out, it's ALREADY in the proper format.  There is
no equivalent unquote(), because there's no need for it.

I took a brief look at the DBI docs, and they have this to say:

<quote>
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. There is no need to quote values being used
with /"Placeholders and Bind Values".
</quote>

It appears that the quote() method of the DBI object can't properly escape
everything, but if I'm reading the docs correctly this problem can be avoided by
 using a prepared statement and placeholders.  If you're not using prepared
statements then perhaps you should try it and see if makes a difference.

Here's an example of an SQL insert statement using placeholders:

my $query = "insert into eventlog (entry, loglevel) values (?, ?)";
my $sth   = $dbh->prepare($query);
$sth->execute($entry, $loglevel);
$sth->finish;

Assume that $entry contains characters that need to be escaped.  There is no
need to use the quote() method on it, since DBI handles this for you when you
use prepared statements and placeholders:

<quote>
Using placeholders and @bind_values with the do method can be useful because it
avoids the need to correctly quote any variables in the $statement.
</quote>

HTH...

___________________________________________
Michael Sims
Project Analyst - Information Technology
Crye-Leike Realtors
Office: (901)758-5648  Pager: (901)769-3722
___________________________________________