[Israel.pm] DBI

[Shlomi Fish]

Hi Meir,

I don't like the fact that you are top-posting and not properly "> "-ing the 
quoted reply (and quote the entire E-mail fully while also adding an HTML 
attachment, which increases the size of the messages considerably). Please 
look up some E-mail netiquette. I'll reply to your message anyway.

On Monday 01 November 2010 10:53:57 Meir Guttman wrote:
> Dear Shlomi,
> 
> I must confess that using a placeholder to avoid quoting problems never
> occurred to me. Good to learn new tricks.

You have not known about placeholders yet? Shame on you.

> 
> 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\' "
> 

1. This is better written as a here-document.

2. You have several possible SQL injection problems there (what if 
$LDI_file_name contains single-quotes?).

3. The «. qq|"'"| .» is better written as:

<<<
qq{COLUMNS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY "'" ESCAPED BY '\\\\'}
>>>

It would be much neater inside a big here-document, however, and it would be 
the same thing at the Perl level.

>       );
> 
> 
> 
> 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.

The issue of this whole thread was how to properly pass arbitrary data that 
may include single-quotes to SQL, and placeholders is generally the right 
answer for that. And see what I've written about the qq|"'"| thing.

Regards,

	Shlomi Fish

-- 
-----------------------------------------------------------------
Shlomi Fish       http://www.shlomifish.org/
http://www.shlomifish.org/humour/ways_to_do_it.html

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