When saying i am storing binary data into MySQL, What my friend is doing is using Storable module to freeze-thaw complex hash structure, and save them into MySQL. But storable, might return data "freezed" with un-wanted characters that MySQL might understand as not part of the data.
This is why I needed quote method so data will inserted/retrieved without any problem. Chanan On Mon, Nov 1, 2010 at 10:50 AM, Meir Guttman <m...@guttman.co.il> wrote: > 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 > > > > > > 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 . > > -----Original Message----- > From: Shlomi Fish [mailto:shlo...@iglu.org.il] > Sent: Monday, November 01, 2010 10:30 AM > To: perl@perl.org.il > Cc: Meir Guttman > Subject: Re: [Israel.pm] DBI > _______________________________________________ > Perl mailing list > Perl@perl.org.il > http://mail.perl.org.il/mailman/listinfo/perl > -- =================== ---- Chanan Berler ---- =================== _______________________________________________ Perl mailing list Perl@perl.org.il http://mail.perl.org.il/mailman/listinfo/perl
