<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1561" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=453181418-03102006><FONT face=Arial color=#0000ff
size=2>JD,</FONT></SPAN></DIV>
<DIV><SPAN class=453181418-03102006> <FONT face=Arial
color=#0000ff size=2>The simplest problem comes when there are multiple escape
characters for the quotes. For example, if I have a string that looks
like: test\';select 4; and the apostrophes get doubled, it becomes
test\'';select 4; and when the quotes are added it becomes 'test\'';select 4;',
and this is what I get in my database I see:</FONT></SPAN></DIV>
<DIV><SPAN class=453181418-03102006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=453181418-03102006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2>neo=# select 'test\'';select
4;';<BR> ?column?<BR>----------<BR> test'<BR>(1 row)</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial color=#0000ff
size=2> ?column?<BR>----------<BR>
4<BR>(1 row)</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><SPAN class=453181418-03102006><FONT face=Arial color=#0000ff
size=2> So, as you see if I replaced ;select 4; with
;delete from my_table;, I would be able to delete everything from
my_table. Similarly I could insert stuff into the db,
etc.</FONT></SPAN></DIV>
<DIV><SPAN class=453181418-03102006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=453181418-03102006> <FONT face=Arial
color=#0000ff size=2>There are probably other chances for sql injection with
this weak quoting as well.</FONT></SPAN></DIV>
<DIV><SPAN class=453181418-03102006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=453181418-03102006><FONT face=Arial color=#0000ff
size=2>Thanks,<BR>Peter</FONT></SPAN></DIV>
<DIV><SPAN class=453181418-03102006><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<BLOCKQUOTE>
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> JD Brennan
[mailto:jazzdev@gmail.com]<BR><B>Sent:</B> Tuesday, October 03, 2006 11:09
AM<BR><B>To:</B> Peter Darley<BR><B>Subject:</B> Re: SPUG: inserting content
into mysql database<BR><BR></FONT></DIV>I agree that using a quote function
specific to your DB<BR>is the best. I think it is useful to know
what's going on under<BR>the covers though, which is why I mentioned that
it's<BR>doubling the single quotes.<BR><BR>What SQL injection attacks are you
still open to if you <BR>quote the single quotes? I thought that
would close that<BR>hole.<BR><BR>JD<BR><BR>
<DIV><SPAN class=gmail_quote>On 10/3/06, <B class=gmail_sendername>Peter
Darley</B> <<A href="mailto:pdarley@kinesis-cem.com">
pdarley@kinesis-cem.com</A>> wrote:</SPAN>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: rgb(204,204,204) 1px solid">
<DIV>
<DIV><SPAN><FONT face=Arial color=#0000ff size=2>JD,</FONT></SPAN></DIV>
<DIV><SPAN></SPAN> </DIV>
<DIV><SPAN> <FONT face=Arial color=#0000ff size=2>The
problem with this is that it leaves you open to other types of SQL
injection, which may be an issue or not, depending on where the data is
coming from. Also, different dbs have different ways of escaping
quotes, so formatting your data by hand loses something in terms of
portability.</FONT></SPAN></DIV>
<DIV><SPAN><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV><SPAN><FONT face=Arial color=#0000ff size=2> I'd
strongly suggest using the quote function if it's good enough in MySQL
(which I don't have an opinion about), or use place holders if the quote
isn't strong enough.</FONT></SPAN></DIV>
<DIV><SPAN><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV><SPAN><FONT face=Arial color=#0000ff size=2>Thanks,<BR>Peter
Darley</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV dir=ltr align=left><FONT face=Tahoma size=2><SPAN
class=q>-----Original Message-----<BR><B>From:</B>
spug-list-bounces+pdarley=<A
onclick="return top.js.OpenExtLink(window,event,this)"
href="mailto:kinesis-cem.com@pm.org"
target=_blank>kinesis-cem.com@pm.org</A> [mailto:<A
onclick="return top.js.OpenExtLink(window,event,this)"
href="mailto:spug-list-bounces+pdarley=kinesis-cem.com@pm.org"
target=_blank>spug-list-bounces+pdarley=kinesis-cem.com@pm.org</A>]<B>On
Behalf Of </B>JD Brennan<BR><B>Sent:</B> Tuesday, October 03, 2006 10:46
AM<BR><B>To:</B> <A onclick="return top.js.OpenExtLink(window,event,this)"
href="mailto:spug-list@pm.org"
target=_blank>spug-list@pm.org</A><BR></SPAN></FONT>
<DIV><SPAN class=q id=q_10e0f567b3afbac9_2><FONT face=Tahoma
size=2><B>Subject:</B> Re: SPUG: inserting content into mysql
database<BR><BR></FONT></SPAN></DIV></DIV>
<DIV><SPAN class=q id=q_10e0f567b3afbac9_4>In SQL you have to double the
single quotes.<BR><BR>update FOO set X = 'This couldn''t happen to
you'<BR><BR>of course there'a method in Perl to do it, as
Keith<BR>mentioned.<BR><SPAN><BR>JD</SPAN>
<DIV><SPAN><BR><BR>
<DIV><SPAN class=gmail_quote>On 10/3/06, <B class=gmail_sendername>Keith
Reed</B> <<A onclick="return top.js.OpenExtLink(window,event,this)"
href="mailto:keith.reed@philips.com"
target=_blank>keith.reed@philips.com</A>> wrote:</SPAN>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: rgb(204,204,204) 1px solid"><BR><FONT
face=sans-serif size=2>Check out dbh->quote()</FONT> <BR><BR><FONT
face=sans-serif size=2>Keith</FONT> <BR><BR><BR>
<TABLE width="100%">
<TBODY>
<TR vAlign=top>
<TD width="33%"><BR><BR><BR><BR><BR><FONT face=sans-serif
size=1><B>"luis medrano" <<A
onclick="return top.js.OpenExtLink(window,event,this)"
href="mailto:lmzaldivar@gmail.com"
target=_blank>lmzaldivar@gmail.com </A>></B> </FONT>
<P><FONT face=sans-serif size=1>Sent by:</FONT> <BR><FONT
face=sans-serif size=1>spug-list-bounces+keith.reed=<A
onclick="return top.js.OpenExtLink(window,event,this)"
href="mailto:philips.com@pm.org" target=_blank>
philips.com@pm.org</A></FONT> </P>
<P><FONT face=sans-serif size=1>2006-10-03 10:35 AM</FONT> </P></TD>
<TD width="66%">
<TABLE width="100%">
<TBODY>
<TR vAlign=top>
<TD>
<DIV align=right><FONT face=sans-serif
size=1>To</FONT></DIV></TD>
<TD><FONT face=sans-serif size=1><A
onclick="return top.js.OpenExtLink(window,event,this)"
href="mailto:spug-list@pm.org"
target=_blank>spug-list@pm.org</A></FONT> </TD></TR>
<TR vAlign=top>
<TD>
<DIV align=right><FONT face=sans-serif
size=1>cc</FONT></DIV></TD>
<TD><BR></TD></TR>
<TR vAlign=top>
<TD>
<DIV align=right><FONT face=sans-serif
size=1>Subject</FONT></DIV></TD>
<TD><FONT face=sans-serif size=1>SPUG: inserting content
into mysql database</FONT> </TD></TR>
<TR>
<TD>
<DIV align=right><FONT face=sans-serif
size=1>Classification</FONT></DIV></TD>
<TD><BR></TD></TR></TBODY></TABLE><BR>
<TABLE>
<TBODY>
<TR vAlign=top>
<TD><BR></TD>
<TD><BR></TD></TR></TBODY></TABLE>
<DIV align=right><BR></DIV></TD></TR></TBODY></TABLE><BR><BR><BR><FONT
size=3></FONT>
<DIV><SPAN><FONT size=3>List,<BR><BR>I running this code feeding a
database:<BR>my $sth1=$dbh->prepare("INSERT INTO
wp_posts(post_author, post_date, post_date_gmt, post_content,post_title,
post_status, comment_status, ping_status,post_name, post_modified,
post_modified_gmt,guid) <BR>
VALUES('$post_author','$post_date','$post_date_gmt','@post_content','$post_title','$post_status','$comment_status','$ping_status','$post_name','$post_modified','$post_modified_gmt','$guid')")
or die; # "Couldnt prepare statement: " . dbh->errstr;
<BR><BR> my $rv1 =
$sth1->execute();<BR><BR><BR>but my problem is if any of the values
of @post_content or $post_title contain apostrophe the script show this
error not executing of feeding the database: <BR><BR>DBD::mysql::st
execute failed: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near 's%20secret%20visits%20to%20heiress/article.do">Goldsmith s
secret visits to heir ' at line 2 at posting-news.pl<BR><BR>anybody
knows how can I fix this without removing the
apostrophe?<BR><BR>Thanks,<BR></FONT></SPAN></DIV><FONT
size=3>Luis</FONT><FONT
size=2><TT>_____________________________________________________________<BR>Seattle
Perl Users Group Mailing List <BR> POST TO: <A
onclick="return top.js.OpenExtLink(window,event,this)"
href="mailto:spug-list@pm.org"
target=_blank>spug-list@pm.org</A><BR>SUBSCRIPTION: <A
onclick="return top.js.OpenExtLink(window,event,this)"
href="http://mail.pm.org/mailman/listinfo/spug-list"
target=_blank>http://mail.pm.org/mailman/listinfo/spug-list</A><BR>
MEETINGS: 3rd Tuesdays<BR> WEB PAGE: <A
onclick="return top.js.OpenExtLink(window,event,this)"
href="http://seattleperl.org/"
target=_blank>http://seattleperl.org/</A></TT></FONT>
<BR><BR>_____________________________________________________________<BR>Seattle
Perl Users Group Mailing List<BR> POST TO: <A
onclick="return top.js.OpenExtLink(window,event,this)"
href="mailto:spug-list@pm.org" target=_blank>spug-list@pm.org
</A><BR>SUBSCRIPTION: <A
onclick="return top.js.OpenExtLink(window,event,this)"
href="http://mail.pm.org/mailman/listinfo/spug-list"
target=_blank>http://mail.pm.org/mailman/listinfo/spug-list</A><BR>
MEETINGS: 3rd Tuesdays <BR> WEB PAGE: <A
onclick="return top.js.OpenExtLink(window,event,this)"
href="http://seattleperl.org/"
target=_blank>http://seattleperl.org/</A><BR><BR></BLOCKQUOTE></DIV><BR></SPAN></DIV></SPAN></DIV></BLOCKQUOTE></DIV></BLOCKQUOTE></DIV><BR></BLOCKQUOTE></BODY></HTML>