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.
--
Jeff
David Steinbrunner wrote:
Hello, I'm having two problems with DBD::CSV. I am working with two comma delimited files and to start out on the small project I need to match names from one file to a name in the other file. Here is the block of code that I am using to get an idea of how many matches I have and what is not matching:my $dir = "/path/to/files/"; my $dbh = DBI->connect("DBI:CSV:f_dir=$dir;csv_eol=\n;csv_sep_char=,;") or die "Cannot connect: " . $DBI::errstr; my $sql = "Select fname, lname from customer_info"; my $sth = $dbh->prepare($sql) or die "Cannot prepare $sql: " . $DBI::errstr; my $rc = $sth->execute or die "Cannot execute SQL Statement: " . $DBI::errstr; my ($yes,$no); while (my ($fname,$lname) = $sth->fetchrow) { my $name = "$fname $lname"; my $sql2 = "Select email from order_info where name = '$name'"; print "$sql2\n"; my $sth2 = $dbh->prepare($sql2) or die "Cannot prepare $sql2: " . $DBI::errstr; my $rc2 = $sth2->execute or die "Cannot execute SQL Statement: " . $DBI::errstr; my $rows = $sth2->rows; if ($rows > 0) { $yes++; print "1\n"; } else { $no++; print "$name\n"; } } print "\nyes: $yes\no: $no\n"; $sth->finish if $sth; $dbh->disconnect if $dbh; On the first run I found I have one Irish man to contend with. The error follows but the name has been changed to protect the innocent: Select email from order_info where name = 'Tim O'Reilly' DBD::CSV::db prepare failed: Mismatched single quote before: 'Reilly' Cannot prepare Select email from order_info where name = 'Tim O'Reilly': Mismatched single quote before: 'Reilly' So I changed the single quotes to escaped double quotes (\") as it likely should have been in the first place but found my self with a new error: Select email from order_info where name = "George W. Bush" SQL ERROR: Table '"George W' in WHERE clause not in FROM clause! SQL ERROR: Couldn't find predicate! SV = PVIV(0x83b86c0) at 0x82cbd48 REFCNT = 1 FLAGS = (PADBUSY,PADMY,ROK) IV = 0 RV = 0x81eefd8 DBD::CSV::st execute failed: dbih_getcom handle DBD::CSV::Statement=HASH(0x81eefd8) is not a DBI handle (has no magic) at /usr/lib/perl5/site_perl/5.6.0/SQL/Statement.pm line 164. Cannot execute SQL Statement: dbih_getcom handle DBD::CSV::Statement=HASH(0x81eefd8) is not a DBI handle (has no magic) at /usr/lib/perl5/site_perl/5.6.0/SQL/Statement.pm line 164. Well, it seems that DBD::CSV thinks anything in double quote should be a table/file. Also if I remove the or die statements from the prepare and execute I will find that I still get an error from our Irish friend. Select email from order_info where name = "Tim O'Reilly" DBD::CSV::db prepare failed: Mismatched single quote before: 'Select email from order_info where name = "Tim O'Reilly" I'm I doing something wrong or is the world against me? =) Thanks for any help in advance!
