SPUG: inserting content into mysql database

[Peter Darley]

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/6c216ed5/attachment-0001.html