[Israel.pm] DBI

Chanan Berler bc.other at gmail.com
Wed Nov 3 01:22:34 PDT 2010


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 <meir at 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: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
> _______________________________________________
> Perl mailing list
> Perl at perl.org.il
> http://mail.perl.org.il/mailman/listinfo/perl
>



-- 
===================
----     Chanan Berler    ----
===================


More information about the Perl mailing list