WordPressのコメントスパム。フィルターを入れているのだけれどもどうもうまく効いていない。一括でMySQLから削除。
傾向を分析
mysql> select comment_content from wp_comments limit 5;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| comment_content |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| […] OPENSOURCE HARDWARE COUNFERENCE http://oshwc.project2108.com/event/oshwc2014tokyowinter/ […] |
| I T のためにここでチェックしばらく 以降私はそれが退屈になっていたと思ったが、最後の少数 投稿です品質私が推測するように LL | bloglist私日常日常に戻り追加。友人:) |あなたは私のをそれに値する
ニューバランス 996 http://www.lanapengarsnabbt.net/includes/NB-9132.html |
| 2rBOUo <a href="http://smpytpxfwciw.com/" rel="nofollow">smpytpxfwciw</a>, [url=http://izdjwfylpfhb.com/]izdjwfylpfhb[/url], [link=http://loqkgdksfucb.com/]loqkgdksfucb[/link], http://emxracpzondt.com/ |
| Nice blog )
http://vifpdc5k.com my blog |
| cahneptixd, <a href="http://www.orxcgvwfut.com/" rel="nofollow">zaykitetgl</a> , [url=http://www.nqjwauvano.com/]lgknfwzpvc[/url], http://www.eglvcjtypd.com/ zaykitetgl |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
そもそも、どれだけあるのかな。
mysql> select count(comment_content) from wp_comments;
+------------------------+
| count(comment_content) |
+------------------------+
| 206434 |
+------------------------+
1 row in set (21.96 sec)
作戦
- なるべくSPAMではないコメントを温存したい
- http: が入っているコメントというだけで削除はしない
- <a href~ が入っていると削除
- あとはキーワードでひっかける
削除作業
hrefが含まれているのを削除
mysql> select count(comment_content) from wp_comments where comment_content LIKE '%href%';
+------------------------+
| count(comment_content) |
+------------------------+
| 170947 |
+------------------------+
1 row in set (21.89 sec)
delete from wp_comments where comment_content LIKE '%href%';
Query OK, 170947 rows affected (42.33 sec)
キーワード、Store, Sale
mysql> delete from wp_comments where comment_content LIKE '%Store%';
Query OK, 1216 rows affected (0.23 sec)
mysql> delete from wp_comments where comment_content LIKE '%Sale%';
Query OK, 2683 rows affected (0.19 sec)
長文コンテンツを消す
mysql> select comment_content from wp_comments limit 20;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| comment_content |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| […] OPENSOURCE HARDWARE COUNFERENCE http://oshwc.project2108.com/event/oshwc2014tokyowinter/ […] |
| I T のためにここでチェックしばらく 以降私はそれが退屈になっていたと思ったが、最後の少数 投稿です品質私が推測するように LL | bloglist私日常日常に戻り追加。友人:) |あなたは私のをそれに値する
ニューバランス 996 http://www.lanapengarsnabbt.net/includes/NB-9132.html |
| Nice blog )
http://vifpdc5k.com my blog |
| オープンハードカンファレンスのマーク | OSHWC |
| OHC? OSHWC? | OSHWC |
| これらはかなりはもちろん洗練されています。 あなたは次モデルでも気質である必要があり、多くの人々に非常に適している可能性があります。iPhone 4を保護する方法がたくさんあります。 これらの一つは間違いなくApple社のiPhoneの条件を利用している。 |
| Sergey Pomerantsev online gambling $2, 329 257. casino free spins http://sverigebastaonlinecasinose.eu/ - mobil online casino casino online <a>casino bonus utan insättning</a> Taxes owed on regulated markets, Jones has never been on the San Diego 0 Kansas City while casino Houston faces the first inning. |
| ??????????????????????????????????????????????????????????????????????????? ???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ?????????????????????????????????????????????????????????????????????? |
| ????????????????????????????????????????????????????????????????????????????? ??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ????????????????????????????????????????????????????????1????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ????????????????????????????????????????????????????????????????? ????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? |
| . rings measurement, time sensitive to help you get away from. in which inside big eyes associated with some,|Still|Yet|However|Though|And yet} {this is the reason|because of this . Key phrases along with wildlife. |
| Taylor Swift may have painted the world red in last year's “For Your Consideration” video for the Academy of Country Music Awards (ACMs) but this year, the singer and her Big Machine label boss Scott Borchetta opted for a sillier approach.
Prada Mens Handbags Leather Blue http://bagclothingshoes.com/prada-womens-handbags-saffiano-embellished-frame-pyramid-0837b-blue_products34577/ |
| A bundle such as higher style containe
Burberry Outlet http://www.usadebtclock.com/ |
| A bundle such as higher design and style containe
Burberry Outlet http://www.usadebtclock.com/ |
| When you're packing your underwear as well as other individual hygiene products make sure you base this on what kind of travel time you'll be experiencing in among the unique hotels you are going to be staying at. In the event the journeys will be long distances then pack in line with that, having said that; if they're shorter journeys you will be capable to purchase factors you need and can have the chance to wash your factors during your stays.Items that you just should have handy and not packed away when traveling around the bus would be books, your MP3 or iPods and
Burberry Outlet http://www.usadebtclock.com/ |
| Fantastic Present Suggestions For Winter Sports EnthusiastsIt's no surprise that winter sports are fashionable around Christmas time, with a number of regions on the nation just minutes or hours from fantastic skiing in the course of this point of year. But what do you get the person within your life that may be crazy regarding skiing or snowboarding?The excellent wardrobe may involve loose fitting, breathable fabrics which can be comfortable to wear and appear cool when worn. Summer clothing need to be picked with correct planning so that you are able to possess the most versatile type of summer time wear for the whole season. Pick clothing that may be mixed and matched easily.To prevent that from happening, do not frown an excessive amount of or raise your eye brows in surprise. Lastly, as substantially as possible try and keep indoors especially amongst 10 am and 4pm since that is the time that it can be quite hot outdoors. When you have to go out, put on a hat and in some cases a pair of sunglasses and also an umbrella to guard your arms.
Burberry Outlet http://www.usadebtclock.com/ |
| Fantastic Present Ideas For Winter Sports EnthusiastsIt's no surprise that winter sports are fashionable about Christmas time, with a number of places of the nation just minutes or hours from fantastic skiing for the duration of this point of year. But what do you get the person within your life that is crazy concerning skiing or snowboarding?The best wardrobe may well involve loose fitting, breathable fabrics that happen to be comfy to wear and appear cool when worn. Summer time clothing should be picked with right planning to ensure that you could possess the most versatile kind of summer time put on for the complete season. Pick clothing that can be mixed and matched very easily.To prevent that from happening, do not frown an excessive amount of or raise your eye brows in surprise. Lastly, as a lot as possible make an effort to keep indoors specifically among 10 am and 4pm because that is the time that it's quite hot outside. If you have to go out, wear a hat and even a pair of sunglasses along with an umbrella to safeguard your arms.
Burberry Outlet http://www.usadebtclock.com/ |
| The higher the rank, the further components they get. Ordinarily the firms who host the video video games will give a reward certificate near the winner in regards towards the nightly within the net activity. These troubles improve up more than a arranged quantity of time, one example is, six months.Find out how you can tell that they're close to. They're going to not be simple to spot simply because of their colour. Additionally they have effectively developed senses and can get spooked quickly.There are essentially elements and even though it really is shallow to really feel of, the ideal way an individual dresses up impacts how folks close to check out her or him. A well-dressed person is frequently regarded of like a self-confident person. But, other people are so timid to check out.
Burberry Outlet http://www.usadebtclock.com/ |
| Hi my friend! I want to say that this post is awesome, nice written and come with almost all significant infos. I would like to look more posts like this .
http://www.brontaylor.com/barbour/liddesdale-barbour-jacket.html http://www.brontaylor.com/barbour/liddesdale-barbour-jacket.html |
| You could certainly see your expertise in the paintings you write. The world hopes for more passionate writers like you who aren't afraid to mention how they believe. Always go after your heart.
michael kors leather replica handbags http://www.peoplefirstca.org/mk/michael-kors-leather-replica-handbags.html |
| My brother recommended I would possibly like this blog. He used to be entirely right. This put up actually made my day. You can not imagine just how so much time I had spent for this information! Thanks!
michael michael kors outlet http://www.peoplefirstca.org/mk/michael-michael-kors-outlet.html |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
20 rows in set (0.00 sec)
長文のコメントはSPAMと判定してもいいでしょうね。
mysql> select count(comment_content) from wp_comments where char_length(comment_content)>10;
+------------------------+
| count(comment_content) |
+------------------------+
| 29832 |
+------------------------+
1 row in set (0.08 sec)
mysql> select count(comment_content) from wp_comments where char_length(comment_content)>100;
+------------------------+
| count(comment_content) |
+------------------------+
| 21242 |
+------------------------+
1 row in set (0.07 sec)
mysql> select count(comment_content) from wp_comments where char_length(comment_content)>1000;
+------------------------+
| count(comment_content) |
+------------------------+
| 1732 |
+------------------------+
1 row in set (0.08 sec)
とりあえず1000以上を消しました
mysql> delete from wp_comments where char_length(comment_content)>1000;
Query OK, 1732 rows affected (0.11 sec)
mysql> select count(comment_content) from wp_comments;
+------------------------+
| count(comment_content) |
+------------------------+
| 29856 |
+------------------------+
1 row in set (0.01 sec)
まだ結構ありますね。500以上も消すことにします。
mysql> delete from wp_comments where char_length(comment_content)>500;
Query OK, 3395 rows affected (0.09 sec)
あとは、チマチマと。
mysql> delete from wp_comments where comment_content LIKE '%friend%';
Query OK, 207 rows affected (0.06 sec)
mysql> delete from wp_comments where comment_content LIKE '%brother%';
Query OK, 41 rows affected (0.06 sec)
mysql> delete from wp_comments where comment_content LIKE '%gift%';
Query OK, 35 rows affected (0.06 sec)
mysql> delete from wp_comments where comment_content LIKE '%bag%';
Query OK, 893 rows affected (0.08 sec)
mysql> delete from wp_comments where comment_content LIKE '%Outlet%';
Query OK, 1202 rows affected (0.08 sec)
mysql> select count(comment_content) from wp_comments;
+------------------------+
| count(comment_content) |
+------------------------+
| 24083 |
+------------------------+
1 row in set (0.01 sec)
mysql> delete from wp_comments where comment_content LIKE '%Apple%';
Query OK, 44 rows affected (0.07 sec)
mysql> delete from wp_comments where comment_content LIKE '%YouTube%';
Query OK, 561 rows affected (0.07 sec)
mysql> delete from wp_comments where comment_content LIKE '%wildlife%';
Query OK, 8 rows affected (0.05 sec)
mysql> delete from wp_comments where comment_content LIKE '%Sweet%';
Query OK, 35 rows affected (0.06 sec)
mysql> delete from wp_comments where comment_content LIKE '%people%';
Query OK, 454 rows affected (0.06 sec)
mysql> select comment_content from wp_comments limit 20;
mysql> delete from wp_comments where comment_content LIKE '%pretty%';
Query OK, 81 rows affected (0.05 sec)
mysql> delete from wp_comments where comment_content LIKE '%beautiful%';
Query OK, 27 rows affected (0.06 sec)
mysql> select count(comment_content) from wp_comments;
+------------------------+
| count(comment_content) |
+------------------------+
| 22874 |
+------------------------+
1 row in set (0.01 sec)
今日はここまで。まだまだ精進が必要ですな。