No problem:-) I was going to do it myself, but I'm a bit busy at the moment:-)
Ilya -----Original Message----- From: Jeff Zucker [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 12, 2002 2:52 PM Cc: dbi-users Subject: Re: Having Problems with DBD::CSV (Ilya - FAQ?) 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. > >
