meir at guttman.co.il
Mon Nov 1 01:50:04 PDT 2010
I must confess that using a placeholder to avoid quoting problems never
occurred to me. Good to learn new tricks.
And I think that your answer for my "challenge" example wouldn't work. Many
quoting methods failed with quoting of the "xxx" in the "OPTIONALLY ENCLOSED
BY XXX" part. My solution, after much experimentation and grief, was as
my $sth = $dbh->do (
"LOAD DATA INFILE '$LDI_file_name' "
."REPLACE INTO TABLE $tables "
."COLUMNS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY ". qq|"'"| . "
ESCAPED BY '\\\\' "
."LINES TERMINATED BY '\r\n\' "
I could of course use placeholders for the LDI filename and the Table name,
but this is not the issue. The issue of this whole thread is quoting. And my
example was meant to show one problem, the one I solved with the qq|"'"|
From: Shlomi Fish [mailto:shlomif at iglu.org.il]
Sent: Monday, November 01, 2010 10:30 AM
To: perl at perl.org.il
Cc: Meir Guttman
Subject: Re: [Israel.pm] DBI
On Sunday 31 October 2010 14:42:17 Meir Guttman wrote:
> My dear shlomi,
> OK, and how would you use placeholders to pass the following (My)SQL
> LOAD DATA INFILE myLDIfile.tsv
> INTO TABLE tbl_name
> COLUMNS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY "'" ESCAPED BY '\\'
> LINES TERMINATED BY '\r\n'
I don't see why this query requires placeholders or passing data in the
place. A here-document will do fine in this case. And you may be able to
LOAD DATA INFILE ?
INTO TABLE tbl_name
COLUMNS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY "'" ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'
Assuming you want to mutate INFILE.
Furthermore I was talking about the general case - I don't rule out that
are exceptions (including this MySQL specific one), but we should recommend
people to use placeholders instead of $dbh->quote normally.
By the way, for further enlightenment regarding SQL injection attacks, see:
Shlomi Fish http://www.shlomifish.org/
"Star Trek: We, the Living Dead" - http://shlom.in/st-wtld
<rindolf> She's a hot chick. But she smokes.
<go|dfish> She can smoke as long as she's smokin'.
Please reply to list if it's a mailing list post - http://shlom.in/reply .
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the Perl