Scuttle のスパム投稿を削除する

Scuttle を設置していたら、スパム投稿がやたら多くなってきた。スパムを投稿するために登録された「迷惑なユーザ」を削除し、そのユーザが投稿したブックマーク、タグを削除したい。

対象

Scuttle 0.7.1

MySQL 4.0

PHP 4

方法

まず、phpMyAdmin で、sc_users テーブル における「迷惑なユーザ」を削除した。

Scuttle のデータベースを見たら、以下のような構造になっていた。(接頭辞 sc_ は省略してある。)

迷惑なユーザを消したので、次に、bookmarks テーブル の要素において、users テーブルの要素に対する参照を持たないレコードを削除しようとした。

DELETE FROM sc_bookmarks WHERE NOT EXISTS (SELECT * FROM sc_users WHERE bookmarks.uId = users.uId)

しかし、この DELETE 文は、実行できなかった。

MySQL AB :: MySQL 4.1 リファレンスマニュアル :: 6.4.2.11 初期の MySQL バージョンに合わせたサブクエリの書き換え によると、

バージョン 4.0 までは、ネストされたクエリのサポートは、INSERT ... SELECT ... 形式と REPLACE ... SELECT ... 形式だけに限定されています。...

SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2); SELECT * FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id);

次のように書き換えることができます。

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id
                        WHERE table2.id IS NULL;

上記の文は「t1 の要素の中で、t2 と結合しない要素を取得する」を意味している。これを「table1 に table2 を外部結合したとき、table2 の中で table1 に対する参照を持たないものを取得する」に読み替えている。 これを参考にして、次のように記述を変更した。

SELECT sc_bookmarks.bId FROM sc_bookmarks LEFT JOIN sc_users ON sc_bookmarks.uId = sc_users.uId WHERE sc_users.uId IS NULL

また、同上によると、

より複雑なサブクエリでは、多くの場合、サブクエリを保持するためのテンポラリテーブルを作成することができます。しかし、一部のケースでは、この方法は有効ではありません。このようなケースの多くは、DELETE ステートメントで発生します。この場合、標準 SQL では、結合をサポートしていません（サブクエリ内を除く）。この状況には、次の 3 つの方法のいずれかで対処することができます。

• 第 1 の選択肢: MySQL バージョン 4.1 にアップグレードする。
• 第 2 の選択肢: 手続き型プログラミング言語（Perl や PHP など）を使用し、SELECT クエリを送信して削除対象のレコードの主キーを取得し、その後、それらの値を使用して DELETE ステートメント（DELETE FROM ... WHERE ... IN (key1, key2, ...)）を構築する。
• 第 3 の選択肢: 対話式 SQL で、MySQL の拡張 CONCAT() を使用して(標準の || 演算子の代わりに）、DELETE ステートメントのセットを自動で構築する。

第2の選択肢を取ることにした。

以下のコードを index.php というファイルにして保存し、サーバの適当なディレクトリに配置し、ブラウザでアクセスした。 DELETE 文は、バスンと一気に削除するので、気をつけて使用した。

<?php
$server     = "";       // サーバ
$userName   = "";       // ユーザ名
$passwd     = "";       // パスワード
$dbName     = "";       // データベース名

// データベースに接続する。
if ($db = mysql_connect($server, $userName, $passwd)){
   mysql_select_db($dbName, $db);
  
   // 迷惑なユーザを削除してあるので、sc_bookmarks に sc_users を外部結合させると、
   // sc_usres の uID が NULL のブックマークが見つかる。
   //
   // ※ このように記述したのは、MySQL のバージョンが 4.1 よりも古いため。
   // 参考: http://dev.mysql.com/doc/refman/4.1/ja/rewriting-subqueries.html
   $sql_bm = "SELECT sc_bookmarks.bId FROM sc_bookmarks LEFT JOIN sc_users ON sc_bookmarks.uId = sc_users.uId WHERE sc_users.uId IS NULL";
   // sc_users に存在しない uID を持つブックマークの配列を取得する。
   $rows = mysql_query($sql_bm, $db);
   $counter_bm = 0;
   while ($row = mysql_fetch_array($rows)){
       // ブックマークを削除する。
       mysql_query("DELETE FROM sc_bookmarks WHERE bId = '" . $row["bId"] ."'", $db);
       $counter_bm++;
   }
   echo "$counter_bm 件のブックマークを削除しました。";
  
   // 迷惑なユーザのブックマークを削除してあるので、sc_tags に sc_bookmarks を外部結合させると、
   // sc_bookmarks の bID が NULL のブックマークが見つかる。
   $sql_tag = "SELECT sc_tags.id FROM sc_tags LEFT JOIN sc_bookmarks ON sc_tags.bid = sc_bookmarks.bId WHERE sc_bookmarks.bId IS NULL";
   // sc_bookmarks  に存在しない bID を持つタグの配列を取得する。
   $rows = mysql_query($sql_tag, $db);
   $counter_tag = 0;
   while ($row = mysql_fetch_array($rows)){
       $counter_tag++;
       // ブックマークについているタグを削除する。
       mysql_query("DELETE FROM sc_tags WHERE id='" . $row["id"] . "'", $db);
   }
   echo "$counter_tag 件のタグを削除しました。";
} else {
   echo "データベースに接続できませんでした。";
}
?>