Dear Shlomi,
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
follows:
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|"'"|
construct.
Regards,
Meir
-----Original Message-----
From: Shlomi Fish [mailto:[email protected]]
Sent: Monday, November 01, 2010 10:30 AM
To: [email protected]
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
query?
>
> 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
first
place. A here-document will do fine in this case. And you may be able to
say:
[query]
LOAD DATA INFILE ?
INTO TABLE tbl_name
COLUMNS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY "'" ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'
[/query]
Assuming you want to mutate INFILE.
Furthermore I was talking about the general case - I don't rule out that
there
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:
* http://en.wikipedia.org/wiki/SQL_injection
* http://bobby-tables.com/
* http://community.livejournal.com/shlomif_tech/35301.html
Regards,
Shlomi Fish
--
-----------------------------------------------------------------
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 .
_______________________________________________
Perl mailing list
[email protected]
http://mail.perl.org.il/mailman/listinfo/perl
