[Israel.pm] DBI

Meir Guttman meir at guttman.co.il
Mon Nov 1 01:50:04 PDT 2010


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: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
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 .

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.perl.org.il/pipermail/perl/attachments/20101101/44b95f9b/attachment.htm 


More information about the Perl mailing list