Re: Characters To be Escaped in Perl

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> I recently tried to add an element to my Postgresql database using a perl
> script, only to discover that a period was causing problems. I looked
> through the documentation for a complete list of characters that need to
> be escaped before they are added to the database, but was unable to find
> such a list. What characters need to be escaped, and is there an existing
> Perl function (in Pg.pm or otherwise) that handles this?

In general, you need to wrap everything in single quotes and escape single
quotes and backslashes within that:

my $escaped = $string;
$escaped =~ s/'/''/g;
$escaped =~ s#\\#\\\\#g;
$escaped = "'$escaped'";

In practice, you should use the quote() method provided by Pg.pm. Just pass
it the string, and it will return an escaped form. You will need a database
handle first:

use DBI;
my $dbh = DBI->connect("dbi:Pg:dbname=foobar", "fred", "",
{ AutoCommit => 1, RaiseError=>1, PrintError=>0 });
my $escaped = $dbh->quote($string);

Often times you will not even need to use this: if you are already going
through the DBI interface, as it automatically quotes things when doing
prepares and executes:
my $sth = $dbh->prepare("UPDATE tab SET foo=? WHERE pie='lemon'");
for $val ("Ain't", "It", "A", "Shame?");
$sth->execute($val); ## Each $val is quoted automatically
}

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200302102145

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+R/8yvJuQZxSWSsgRAn+PAKCi+/SL7cpnjplGN5hs5RneGPZazACfdJCD
+pq0VY1SyHnqNxkfs42T1ok=
=8X27
-----END PGP SIGNATURE-----