Perl and MySQL

[Charles A. Landemaine]

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,

 

[xhoster]

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

 

[Charles A. Landemaine]

Thanks, yeah, maybe it's actually a little faster using the mysql
command.

 

[Brian Wakem]

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%';

 

[krakle]

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.

 

[DJ Stunks]

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

 

[Jens Thoms Toerring]

Charles A. Landemaine wrote:

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

 

[krakle]

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

-jp

Yes. That is correct. Thank you.