This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
To: pgsql-novice@postgresql.org
From: Robert Mosher <mosher@andrews.edu>
Subject: [NOVICE] Characters To be Escaped in Perl
Date: Mon, 10 Feb 2003 19:09:36 -0500 (EST)
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?
===
To: pgsql-novice@postgresql.org
From: greg@turnstep.com
Subject: Re: [NOVICE] Characters To be Escaped in Perl
Date: Tue, 11 Feb 2003 02:54:53 -0000
> 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
}
===