<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:David;
panose-1:0 0 0 0 0 0 0 0 0 0;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:Arial;}
h1
{margin-top:12.0pt;
margin-right:0cm;
margin-bottom:3.0pt;
margin-left:0cm;
page-break-after:avoid;
font-size:16.0pt;
font-family:Arial;}
p.MsoFootnoteText, li.MsoFootnoteText, div.MsoFootnoteText
{margin-top:0cm;
margin-right:0cm;
margin-bottom:0cm;
margin-left:17.0pt;
margin-bottom:.0001pt;
text-indent:-17.0pt;
font-size:9.0pt;
font-family:Arial;
font-style:italic;}
p.MsoCaption, li.MsoCaption, div.MsoCaption
{margin:0cm;
margin-bottom:.0001pt;
text-align:center;
font-size:10.0pt;
font-family:Arial;}
p.MsoList, li.MsoList, div.MsoList
{margin-top:0cm;
margin-right:0cm;
margin-bottom:0cm;
margin-left:34.0pt;
margin-bottom:.0001pt;
text-indent:-17.0pt;
mso-list:l5 level1 lfo18;
font-size:12.0pt;
font-family:Arial;}
p.MsoListBullet, li.MsoListBullet, div.MsoListBullet
{margin-top:3.0pt;
margin-right:0cm;
margin-bottom:3.0pt;
margin-left:25.5pt;
text-indent:-19.85pt;
mso-list:l3 level1 lfo21;
font-size:11.0pt;
font-family:Arial;}
p.MsoListNumber, li.MsoListNumber, div.MsoListNumber
{margin-top:3.0pt;
margin-right:0cm;
margin-bottom:3.0pt;
margin-left:18.0pt;
text-indent:-18.0pt;
mso-list:l2 level1 lfo2;
font-size:11.0pt;
font-family:Arial;}
p.MsoList2, li.MsoList2, div.MsoList2
{margin-top:0cm;
margin-right:0cm;
margin-bottom:0cm;
margin-left:51.05pt;
margin-bottom:.0001pt;
text-indent:-17.05pt;
mso-list:l4 level1 lfo20;
font-size:12.0pt;
font-family:Arial;}
p.MsoListBullet2, li.MsoListBullet2, div.MsoListBullet2
{margin-top:0cm;
margin-right:0cm;
margin-bottom:0cm;
margin-left:34.0pt;
margin-bottom:.0001pt;
text-indent:-17.0pt;
mso-list:l1 level1 lfo12;
font-size:11.0pt;
font-family:Arial;}
p.MsoListBullet3, li.MsoListBullet3, div.MsoListBullet3
{margin-top:0cm;
margin-right:0cm;
margin-bottom:0cm;
margin-left:51.05pt;
margin-bottom:.0001pt;
text-indent:-17.05pt;
mso-list:l0 level1 lfo17;
font-size:11.0pt;
font-family:Arial;}
p.MsoTitle, li.MsoTitle, div.MsoTitle
{margin-top:12.0pt;
margin-right:0cm;
margin-bottom:3.0pt;
margin-left:0cm;
text-align:center;
font-size:16.0pt;
font-family:Arial;
font-weight:bold;}
p.MsoBodyText, li.MsoBodyText, div.MsoBodyText
{margin-top:3.0pt;
margin-right:0cm;
margin-bottom:3.0pt;
margin-left:0cm;
font-size:11.0pt;
font-family:Arial;}
p.MsoBodyTextFirstIndent, li.MsoBodyTextFirstIndent, div.MsoBodyTextFirstIndent
{margin-top:3.0pt;
margin-right:0cm;
margin-bottom:3.0pt;
margin-left:17.0pt;
font-size:11.0pt;
font-family:Arial;}
p.MsoBodyText2, li.MsoBodyText2, div.MsoBodyText2
{margin-top:3.0pt;
margin-right:0cm;
margin-bottom:3.0pt;
margin-left:17.0pt;
font-size:11.0pt;
font-family:Arial;}
p.MsoBodyTextIndent2, li.MsoBodyTextIndent2, div.MsoBodyTextIndent2
{margin-top:0cm;
margin-right:0cm;
margin-bottom:0cm;
margin-left:34.0pt;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:Arial;}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
p.MsoPlainText, li.MsoPlainText, div.MsoPlainText
{margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
p.StyleBodyTextFirstIndentBefore063cmFirstline0cm, li.StyleBodyTextFirstIndentBefore063cmFirstline0cm, div.StyleBodyTextFirstIndentBefore063cmFirstline0cm
{margin-top:3.0pt;
margin-right:0cm;
margin-bottom:3.0pt;
margin-left:17.0pt;
font-size:11.0pt;
font-family:Arial;}
p.Sub-Title, li.Sub-Title, div.Sub-Title
{margin-top:12.0pt;
margin-right:0cm;
margin-bottom:3.0pt;
margin-left:0cm;
text-align:center;
font-size:14.0pt;
font-family:Arial;
font-style:italic;}
@page Section1
{size:21.0cm 842.0pt;
margin:2.0cm 69.65pt 65.2pt 69.6pt;}
div.Section1
{page:Section1;}
/* List Definitions */
@list l0
{mso-list-id:-126;
mso-list-type:simple;
mso-list-template-ids:1911344842;}
@list l0:level1
{mso-level-number-format:bullet;
mso-level-style-link:"List Bullet 3";
mso-level-text:\F0B7;
mso-level-tab-stop:51.05pt;
mso-level-number-position:left;
margin-left:51.05pt;
text-indent:-17.05pt;
font-family:Symbol;}
@list l1
{mso-list-id:-125;
mso-list-type:simple;
mso-list-template-ids:-1054985060;}
@list l1:level1
{mso-level-number-format:bullet;
mso-level-style-link:"List Bullet 2";
mso-level-text:\F0B7;
mso-level-tab-stop:34.0pt;
mso-level-number-position:left;
margin-left:34.0pt;
text-indent:-17.0pt;
font-family:Symbol;}
@list l2
{mso-list-id:-120;
mso-list-type:simple;
mso-list-template-ids:-909362804;}
@list l2:level1
{mso-level-style-link:"List Number";
mso-level-tab-stop:18.0pt;
mso-level-number-position:left;
margin-left:18.0pt;
text-indent:-18.0pt;}
@list l3
{mso-list-id:-119;
mso-list-type:simple;
mso-list-template-ids:2053509862;}
@list l3:level1
{mso-level-number-format:bullet;
mso-level-style-link:"List Bullet";
mso-level-text:\25A0;
mso-level-tab-stop:25.5pt;
mso-level-number-position:left;
margin-left:25.5pt;
text-indent:-19.85pt;
mso-ansi-font-size:9.0pt;
mso-bidi-font-size:9.0pt;
font-family:Arial;
mso-bidi-font-family:"Times New Roman";
mso-ansi-font-weight:normal;
mso-bidi-font-weight:normal;
mso-ansi-font-style:normal;
mso-bidi-font-style:normal;}
@list l4
{mso-list-id:1805538338;
mso-list-type:hybrid;
mso-list-template-ids:-858103508 -1883372704 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}
@list l4:level1
{mso-level-style-link:"List 2";
mso-level-tab-stop:51.05pt;
mso-level-number-position:left;
margin-left:51.05pt;
text-indent:-17.05pt;}
@list l5
{mso-list-id:1928418554;
mso-list-type:hybrid;
mso-list-template-ids:-1894493196 2132983906 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}
@list l5:level1
{mso-level-number-format:hebrew-1;
mso-level-style-link:List;
mso-level-tab-stop:34.0pt;
mso-level-number-position:center;
margin-left:34.0pt;
text-indent:-17.0pt;}
ol
{margin-bottom:0cm;}
ul
{margin-bottom:0cm;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoPlainText><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Dear Shlomi,<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>I must confess that using a placeholder to avoid quoting problems
never occurred to me. Good to learn new tricks… <o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>And I think that your answer for my "challenge" example
wouldn't work. Many quoting methods failed with quoting of the "xxx"
in the </span></font>"OPTIONALLY ENCLOSED BY XXX"<font face=Arial><span
style='font-family:Arial'> part. My solution, after much experimentation and grief,
was as follows:<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoPlainText style='margin-left:36.0pt'><font size=2 face="Courier New"><span
style='font-size:10.0pt'> my $sth = $dbh->do (<o:p></o:p></span></font></p>
<p class=MsoPlainText style='margin-left:36.0pt'><font size=2 face="Courier New"><span
style='font-size:10.0pt'>
"LOAD DATA INFILE '$LDI_file_name' "<o:p></o:p></span></font></p>
<p class=MsoPlainText style='margin-left:36.0pt'><font size=2 face="Courier New"><span
style='font-size:10.0pt'> ."REPLACE
INTO TABLE $tables "<o:p></o:p></span></font></p>
<p class=MsoPlainText style='margin-left:36.0pt'><font size=2 face="Courier New"><span
style='font-size:10.0pt'> ."COLUMNS
TERMINATED BY '\t' OPTIONALLY ENCLOSED BY ". qq|"'"| . " ESCAPED
BY '\\\\' "<o:p></o:p></span></font></p>
<p class=MsoPlainText style='margin-left:36.0pt'><font size=2 face="Courier New"><span
style='font-size:10.0pt'> ."LINES
TERMINATED BY '\r\n\' "<o:p></o:p></span></font></p>
<p class=MsoPlainText style='margin-left:36.0pt'><font size=2 face="Courier New"><span
style='font-size:10.0pt'> );<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>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 <i><span
style='font-style:italic'>quoting.</span></i> And my example was meant to show
one problem, the one I solved with the </span></font>qq|"'"|<font
face=Arial><span style='font-family:Arial'> construct.<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Regards,<br>
Meir<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>-----Original Message-----<br>
From: Shlomi Fish [mailto:shlomif@iglu.org.il] <br>
Sent: Monday, November 01, 2010 10:30 AM<br>
To: perl@perl.org.il<br>
Cc: Meir Guttman<br>
Subject: Re: [Israel.pm] DBI</span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'><o:p> </o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>On Sunday 31 October 2010 14:42:17 Meir Guttman wrote:<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>> My dear shlomi,<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>> <o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>> OK, and how would you use placeholders to pass the following
(My)SQL query?<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>> <o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>> LOAD DATA INFILE myLDIfile.tsv<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>> <o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>> INTO TABLE tbl_name<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>> <o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>> COLUMNS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY "'"
ESCAPED BY '\\'<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>> <o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>> LINES TERMINATED BY '\r\n'<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>> <o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'><o:p> </o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>I don't see why this query requires placeholders or passing data in the
first <o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>place. A here-document will do fine in this case. And you may be able
to say:<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'><o:p> </o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>[query]<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>LOAD DATA INFILE ?<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'><o:p> </o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>INTO TABLE tbl_name<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'><o:p> </o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>COLUMNS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY "'" ESCAPED
BY '\\'<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'><o:p> </o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>LINES TERMINATED BY '\r\n'<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>[/query]<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'><o:p> </o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>Assuming you want to mutate INFILE.<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'><o:p> </o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>Furthermore I was talking about the general case - I don't rule out
that there <o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>are exceptions (including this MySQL specific one), but we should
recommend <o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>people to use placeholders instead of $dbh->quote normally.<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'><o:p> </o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>By the way, for further enlightenment regarding SQL injection attacks,
see:<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'><o:p> </o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>* http://en.wikipedia.org/wiki/SQL_injection<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'><o:p> </o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>* http://bobby-tables.com/<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'><o:p> </o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>* http://community.livejournal.com/shlomif_tech/35301.html<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'><o:p> </o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>Regards,<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'><o:p> </o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'> Shlomi Fish<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'><o:p> </o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>-- <o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>-----------------------------------------------------------------<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>Shlomi Fish
http://www.shlomifish.org/<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>"Star Trek: We, the Living Dead" - http://shlom.in/st-wtld<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'><o:p> </o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'><rindolf> She's a hot chick. But she smokes.<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'><go|dfish> She can smoke as long as she's smokin'.<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'><o:p> </o:p></span></font></p>
<p class=MsoPlainText><font size=2 face="Courier New"><span style='font-size:
10.0pt'>Please reply to list if it's a mailing list post -
http://shlom.in/reply .<o:p></o:p></span></font></p>
</div>
</body>
</html>