Sterin, Ilya (I.) wrote:
Anyone can add to the FAQ, they just need to register and create a password first
Yep, and thanks for all your work on it. I guess I was being lazy and asking if this is already covered, but I can look that up myself when/if I ever have time.
--
Jeff
Ilya
-----Original Message-----
From: Jeff Zucker [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 12, 2002 1:39 PM
To: David Steinbrunner; dbi-users
Subject: Re: Having Problems with DBD::CSV (Ilya - FAQ?)
Hi David,
SQL string values must occur between single quotes, not double quotes:
SELECT * FROM foo WHERE bar = 'baz' CORRECT
SELECT * FROM foo WHERE bar = "baz" WRONG
Values that have single quotes inside them must not appear in the SQL as raw single, quotes, they must be escaped. In DBD::CSV they can be escaped with either a backslash or by using two single quotes (not a double quote):
SELECT * FROM foo WHERE bar = 'Tim O\'Reilly' CORRECT
SELECT * FROM foo WHERE bar = 'Tim O''Reilly' CORRECT
SELECT * FROM foo WHERE bar = 'Tim O'Reilly' WRONG
Although you can manually escape the quotes, it is much better to use the methods provided by DBI to handle this situation (in all DBDs, not just DBD::CSV) - the $dbh->quote() method and placeholders. See the DBI docs for the quote() method and placeholders.
The placeholders method is best since it also offers other protections. Here's a sample:
my $sth = $dbh->prepare(
"SELECT * FROM foo WHERE bar = ?"
);
$sth->execute("Tim O'Reilly");
Notice the O'Reilly is not escaped, the placeholder method does that for you. Also notice that there are no quotes around the question mark in the SQL. If you always use placeholders or the quote() method, you won't have to manually escape anything and you won't have to know or care whether values have embedded quotes.
Placeholders can also be passed as part of a do() call, so here's a small script that illustrates four different ways to correctly use embedded single quotes:
#!/usr/local/bin/perl -w
use strict;
use DBI;
my $table = 'foo';
my $dbh = DBI->connect('dbi:CSV(RaiseError=1):')
or die $DBI::errstr;
my $val = q~Tim O'Reilly~;
my $escaped_val1 = q~Tim O\'Reilly~;
my $escaped_val2 = q~Tim O''Reilly~;
my $quoted_val = $dbh->quote($val);
$dbh->do("DROP TABLE IF EXISTS $table");
$dbh->do("CREATE TABLE $table ( id INTEGER, txt CHAR(4) )");
$dbh->do("INSERT INTO $table VALUES (1,?)",{},$val);
$dbh->do("INSERT INTO $table VALUES (2,'$escaped_val1')");
$dbh->do("INSERT INTO $table VALUES (3,'$escaped_val2')");
$dbh->do("INSERT INTO $table VALUES (4,$quoted_val)");
my $sth=$dbh->prepare("SELECT * FROM $table");
$sth->execute;
while (my $r=$sth->fetch) {
print "@$r\n";
}
Except for the method of escaping quotes (which can vary between SQL implementations) all of this applies to any DBD, not just DBD::CSV.
