SPUG: inserting content into mysql database

Peter Darley pdarley at kinesis-cem.com
Tue Oct 3 12:09:05 PDT 2006


JD,
    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:


neo=# select 'test\'';select 4;';
 ?column?
----------
 test'
(1 row)

 ?column?
----------
        4
(1 row)

    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.

    There are probably other chances for sql injection with this weak
quoting as well.

Thanks,
Peter

  -----Original Message-----
  From: JD Brennan [mailto:jazzdev at gmail.com]
  Sent: Tuesday, October 03, 2006 11:09 AM
  To: Peter Darley
  Subject: Re: SPUG: inserting content into mysql database


  I agree that using a quote function specific to your DB
  is the best.   I think it is useful to know what's going on under
  the covers though, which is why I mentioned that it's
  doubling the single quotes.

  What SQL injection attacks are you still open to if you
  quote the single quotes?   I thought that would close that
  hole.

  JD


  On 10/3/06, Peter Darley < pdarley at kinesis-cem.com> wrote:
    JD,

        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.

        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.

    Thanks,
    Peter Darley
      -----Original Message-----
      From: spug-list-bounces+pdarley=kinesis-cem.com at pm.org
[mailto:spug-list-bounces+pdarley=kinesis-cem.com at pm.org]On Behalf Of JD
Brennan
      Sent: Tuesday, October 03, 2006 10:46 AM
      To: spug-list at pm.org

      Subject: Re: SPUG: inserting content into mysql database


      In SQL you have to double the single quotes.

      update FOO set X = 'This couldn''t happen to you'

      of course there'a method in Perl to do it, as Keith
      mentioned.

      JD



      On 10/3/06, Keith Reed <keith.reed at philips.com> wrote:

        Check out dbh->quote()

        Keith







              "luis medrano" <lmzaldivar at gmail.com >
              Sent by:
              spug-list-bounces+keith.reed= philips.com at pm.org

              2006-10-03 10:35 AM
             To spug-list at pm.org
                    cc

                    Subject SPUG: inserting content into mysql database
                    Classification












        List,

        I running this code feeding a database:
        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)

VALUES('$post_author','$post_date','$post_date_gmt','@post_content','$post_t
itle','$post_status','$comment_status','$ping_status','$post_name','$post_mo
dified','$post_modified_gmt','$guid')")  or die; # "Couldnt prepare
statement: " . dbh->errstr;

               my $rv1 = $sth1->execute();


        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:

        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

        anybody knows how can I fix this without removing the apostrophe?

        Thanks,

        Luis_____________________________________________________________
        Seattle Perl Users Group Mailing List
            POST TO: spug-list at pm.org
        SUBSCRIPTION: http://mail.pm.org/mailman/listinfo/spug-list
           MEETINGS: 3rd Tuesdays
           WEB PAGE: http://seattleperl.org/

        _____________________________________________________________
        Seattle Perl Users Group Mailing List
            POST TO: spug-list at pm.org
        SUBSCRIPTION: http://mail.pm.org/mailman/listinfo/spug-list
           MEETINGS: 3rd Tuesdays
           WEB PAGE: http://seattleperl.org/





-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.pm.org/pipermail/spug-list/attachments/20061003/2a64274f/attachment.html 


More information about the spug-list mailing list