Perl and MySQL

Discussion in 'Perl Misc' started by Charles A. Landemaine, Feb 28, 2007.

  1. I have a MySQL table that is used to store comments on my blog. I just
    found out it's filled with spam. The table itself is 2 GB big, with
    more than half a million spam backlinks. I haven't found much material
    on how to interact between Perl and MySQL. What I'd like to do is do a
    simple Perl script that opens the DB table, goes through all rows and
    deletes all those which contain "<a href". I'll have to do that during
    the night, not to disrupt the server. How could I do that?
    Thanks,
     
    Charles A. Landemaine, Feb 28, 2007
    #1
    1. Advertising

  2. Charles A. Landemaine

    Guest

    "Charles A. Landemaine" <> wrote:
    > I have a MySQL table that is used to store comments on my blog. I just
    > found out it's filled with spam. The table itself is 2 GB big, with
    > more than half a million spam backlinks. I haven't found much material
    > on how to interact between Perl and MySQL. What I'd like to do is do a
    > simple Perl script that opens the DB table, goes through all rows and
    > deletes all those which contain "<a href". I'll have to do that during
    > the night, not to disrupt the server. How could I do that?
    > Thanks,


    I don't know why you would do this in Perl rather from the mysql client,
    but anyway I'd use DBI and DBD::mysql

    Xho

    --
    -------------------- http://NewsReader.Com/ --------------------
    Usenet Newsgroup Service $9.95/Month 30GB
     
    , Feb 28, 2007
    #2
    1. Advertising

  3. Thanks, yeah, maybe it's actually a little faster using the mysql
    command.
     
    Charles A. Landemaine, Feb 28, 2007
    #3
  4. Charles A. Landemaine

    Brian Wakem Guest

    Charles A. Landemaine wrote:

    > I have a MySQL table that is used to store comments on my blog. I just
    > found out it's filled with spam. The table itself is 2 GB big, with
    > more than half a million spam backlinks. I haven't found much material
    > on how to interact between Perl and MySQL. What I'd like to do is do a
    > simple Perl script that opens the DB table, goes through all rows and
    > deletes all those which contain "<a href". I'll have to do that during
    > the night, not to disrupt the server. How could I do that?
    > Thanks,


    No need for Perl.

    DELETE FROM table WHERE comments LIKE '%<a href%';


    --
    Brian Wakem
    Email: http://homepage.ntlworld.com/b.wakem/myemail.png
     
    Brian Wakem, Feb 28, 2007
    #4
  5. Charles A. Landemaine

    Guest

    On Feb 28, 11:44 am, "Charles A. Landemaine" <>
    wrote:
    > I have a MySQL table that is used to store comments on my blog. I just
    > found out it's filled with spam.
    > The table itself is 2 GB big, with
    > more than half a million spam backlinks.


    Unless a bot flooded your script with a half million comments in a day
    you should of known about this "abuse" way before you reach 10,000
    spam messages. Web Master 101 - monitoring your web site.

    > I haven't found much material
    > on how to interact between Perl and MySQL. What I'd like to do is do a
    > simple Perl script that opens the DB table, goes through all rows and
    > deletes all those which contain "<a href". I'll have to do that during
    > the night, not to disrupt the server. How could I do that?


    How could you do that! Why would you do that?! Perl is basically
    useless and irrelevant when it comes to the solution of your problem.

    Look at what you have to do...

    Loop through the table row by row deleting all rows that contain "<a
    href".

    No where in that operation does it require the use of Perl. It's all
    (and only) mySQL relevant.

    Since you have to check row by row, indexes aren't important. So, at 3
    am run this query:

    DELETE FROM myTable WHERE myColumn = '%<a href%';

    This is a mySQL solution to a mySQL problem.
     
    , Mar 2, 2007
    #5
  6. Charles A. Landemaine

    DJ Stunks Guest

    On Mar 2, 12:37 am, wrote:
    > On Feb 28, 11:44 am, "Charles A. Landemaine" <>
    > wrote:
    >
    > > I have a MySQL table that is used to store comments on my blog. I just
    > > found out it's filled with spam.
    > > The table itself is 2 GB big, with
    > > more than half a million spam backlinks.

    >
    > Since you have to check row by row, indexes aren't important. So, at 3
    > am run this query:
    >
    > DELETE FROM myTable WHERE myColumn = '%<a href%';


    ITYM "... WHERE myColumn LIKE '%<a href%';"

    -jp
     
    DJ Stunks, Mar 2, 2007
    #6
  7. Brian Wakem <> wrote:
    > Charles A. Landemaine wrote:


    > > I have a MySQL table that is used to store comments on my blog. I just
    > > found out it's filled with spam. The table itself is 2 GB big, with
    > > more than half a million spam backlinks. I haven't found much material
    > > on how to interact between Perl and MySQL. What I'd like to do is do a
    > > simple Perl script that opens the DB table, goes through all rows and
    > > deletes all those which contain "<a href". I'll have to do that during
    > > the night, not to disrupt the server. How could I do that?
    > > Thanks,


    > No need for Perl.


    > DELETE FROM table WHERE comments LIKE '%<a href%';


    Well, as long as the spammers don't get sneaky (and that's
    what spammers typically are;-) and put a bit of extra white-
    space between the '<' and 'a' or 'a' and the 'href' - in that
    case a Perl solution might become necessary. But a better
    long-term solution than throwing things out after they already
    went into the database would be to modify the script that puts
    the data in there to refuse to store data containing a link in
    the first place.
    Regards, Jens
    --
    \ Jens Thoms Toerring ___
    \__________________________ http://toerring.de
     
    Jens Thoms Toerring, Mar 2, 2007
    #7
  8. Charles A. Landemaine

    Guest

    On Mar 2, 9:20 am, "DJ Stunks" <> wrote:
    > On Mar 2, 12:37 am, wrote:
    >
    > > On Feb 28, 11:44 am, "Charles A. Landemaine" <>
    > > wrote:

    >
    > > > I have a MySQL table that is used to store comments on my blog. I just
    > > > found out it's filled with spam.
    > > > The table itself is 2 GB big, with
    > > > more than half a million spam backlinks.

    >
    > > Since you have to check row by row, indexes aren't important. So, at 3
    > > am run this query:

    >
    > > DELETE FROM myTable WHERE myColumn = '%<a href%';

    >
    > ITYM "... WHERE myColumn LIKE '%<a href%';"
    >
    > -jp


    Yes. That is correct. Thank you.
     
    , Mar 3, 2007
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jeffrey H. Coffield
    Replies:
    1
    Views:
    1,894
  2. Roedy Green
    Replies:
    0
    Views:
    1,485
    Roedy Green
    Jul 23, 2009
  3. dave
    Replies:
    4
    Views:
    1,394
    Steve Holden
    Nov 18, 2010
  4. bin liu
    Replies:
    1
    Views:
    135
    Curt Hibbs
    Dec 22, 2004
  5. Mark Meijer

    mySQL Ruby Gem and MAMP mySQL

    Mark Meijer, Feb 2, 2008, in forum: Ruby
    Replies:
    3
    Views:
    185
    louis
    Feb 3, 2008
Loading...

Share This Page