| View previous topic :: View next topic |
| Author |
Message |
Kenji Miyamoto
Veteran
Joined: 28 May 2005
Posts: 1452
Location: Looking over your shoulder.
|
 Posted: Sun Feb 18, 2007 6:40 am Post subject: Perl SQL Query Problems: Character Escaping or Encoding? |
 |
|
I've run into a problem with SQL queries under Perl; I need to store data containing arbitrary characters, which may or may not contain SQL characters. What do I do?
This doesn't work: | Code: | foreach my $ebuild (@ebuilds) {
my $ename = $ebuild->name;
my $longdesc = $ebuild->longdesc;
$longdesc =~ s/([\(\)\:\"\'\[\]])/\\\1/;
$dbh->do("INSERT INTO ebuilds (name, catname, longdesc, longhash) VALUES(\'$ename\', \'$catname\', \'" . (($longdesc) ? "$lo$
} |
$longdesc is the issue.
I also get output similar to the following when trying to use "ESCAPE '//'": | Code: | DBD::SQLite::db do failed: near "ESCAPE": syntax error(1) at dbdimp.c line 271 at /usr/local/bin/portfind-sql line 245.
DBD::SQLite::db do failed: near "ESCAPE": syntax error(1) at dbdimp.c line 271 at /usr/local/bin/portfind-sql line 245.
DBD::SQLite::db do failed: near "ESCAPE": syntax error(1) at dbdimp.c line 271 at /usr/local/bin/portfind-sql line 245.
DBD::SQLite::db do failed: near "ESCAPE": syntax error(1) at dbdimp.c line 271 at /usr/local/bin/portfind-sql line 245.
DBD::SQLite::db do failed: near "ESCAPE": syntax error(1) at dbdimp.c line 271 at /usr/local/bin/portfind-sql line 245.
DBD::SQLite::db do failed: near "ESCAPE": syntax error(1) at dbdimp.c line 271 at /usr/local/bin/portfind-sql line 245.
DBD::SQLite::db do failed: near "ESCAPE": syntax error(1) at dbdimp.c line 271 at /usr/local/bin/portfind-sql line 245.
DBD::SQLite::db do failed: unrecognized token: "\"(1) at dbdimp.c line 271 at /usr/local/bin/portfind-sql line 245.
DBD::SQLite::db do failed: near "ESCAPE": syntax error(1) at dbdimp.c line 271 at /usr/local/bin/portfind-sql line 245.
DBD::SQLite::db do failed: near "ESCAPE": syntax error(1) at dbdimp.c line 271 at /usr/local/bin/portfind-sql line 245.
DBD::SQLite::db do failed: near "ESCAPE": syntax error(1) at dbdimp.c line 271 at /usr/local/bin/portfind-sql line 245.
DBD::SQLite::db do failed: unrecognized token: "\"(1) at dbdimp.c line 271 at /usr/local/bin/portfind-sql line 245.
DBD::SQLite::db do failed: unrecognized token: "\"(1) at dbdimp.c line 271 at /usr/local/bin/portfind-sql line 245.
DBD::SQLite::db do failed: near "ESCAPE": syntax error(1) at dbdimp.c line 271 at /usr/local/bin/portfind-sql line 245. |
I've also tried Base64 encoding: | Code: | | Wide character in subroutine entry at /usr/local/bin/portfind-sql line 247. |
_________________
[ Kawa-kun, new and improved!! ]
Alex Libman seems to be more of an anarchist than a libertarian. |
|
| Back to top |
|
 |
dleverton
Guru
Joined: 28 Aug 2006
Posts: 517
|
| Posted: Sun Feb 18, 2007 11:16 am Post subject: Re: Perl SQL Query Problems: Character Escaping or Encoding? |
|
|
| Kenji Miyamoto wrote: | I've run into a problem with SQL queries under Perl; I need to store data containing arbitrary characters, which may or may not contain SQL characters. What do I do?
This doesn't work: | Code: | foreach my $ebuild (@ebuilds) {
my $ename = $ebuild->name;
my $longdesc = $ebuild->longdesc;
$longdesc =~ s/([\(\)\:\"\'\[\]])/\\\1/;
$dbh->do("INSERT INTO ebuilds (name, catname, longdesc, longhash) VALUES(\'$ename\', \'$catname\', \'" . (($longdesc) ? "$lo$
} |
$longdesc is the issue.
|
Escaping is icky. Try
| Code: | my $sth = $dbh->prepare("INSERT INTO ebuilds (name, catname, longdesc, longhash) VALUES(?,?,?,?)");
foreach my $ebuild (@ebuilds) {
my $ename = $ebuild->name;
my $longdesc = $ebuild->longdesc;
$sth->execute($ename, $catname, $longdesc ? "$lo$
} |
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
Portage & Programming
