Adventskalender Tag 3 – Spaß mit SQL: Kommentare untersuchen

SQL ist eigentlich eine schöne Sprache: Wir brauchen in den meisten Fällen nur ein knappes Vokabular, und die Abfragen folgen in ihrer Grammatik dicht der normalen englischen Sprache. Dennoch scheuen viele Einsteiger lange davor zurück, vielleicht weil hier das Risiko größer aussieht, etwas kaputtzumachen.

Heute wollen wir uns ein paar einfache SQL-Queries ansehen, die uns nähere Auskunft über unsere Kommentare geben. Ich empfehle dazu entweder eine Konsole oder den SQL-Editor der visuellen Datenbankverwaltung wie phpMyAdmin oder Adminer. Ein Tutorial soll dies nicht sein, sondern nur ein Ausgangspunkt für deine eigenen Recherchen.

Für die folgenden Queries greife ich auf eine Kommentardatenbank zurück, die mir Ramona Hapke freundlicherweise überlassen hat. Sie umfasst 12223 Einträge. Die Datenbank natürlich, nicht Ramona. 🙂

Wieviele dieser Einträge sind Spam?

Dazu sehen wir uns die Spalte comment_approved an. Hier speichert WordPress den Typ des Kommentars. Die Menge der unterschiedlichen Werte erfassen wir mit COUNT(*) und legen sie in der Variable amount ab. Die lassen wir uns auch ausgeben.

SELECT `comment_approved`, COUNT(*) as amount
FROM `wp_comments`
GROUP BY `comment_approved`
ORDER BY amount DESC
LIMIT 0, 30

Ergebnis:

comment_approved amount
spam              12118
1                   104
post-trashed          1

Die 1 steht für zugelassene, also echte Kommentare. Wir sehen schon jetzt: nur 0,85% aller Kommentare sind kein Spam. Das ist leider ein ganz normaler Wert. Das Gemeine ist: MySQL muss jedesmal durch die ganze Tabelle gehen, um die richtigen Kommentare für die Anzeige bei den veröffentlichten Beiträgen zu finden. Mit einem Index, einer für Abfragen optimierten Struktur, geht das schneller.

Welche Indexes gibt es?

SHOW INDEX FROM `wp_comments`

Ergebnis

Non_unique Key_name                  Seq_in_index Column_name      Collation Cardinality
0          PRIMARY                   1            comment_ID       A         13694
1          comment_post_ID           1            comment_post_ID  A           258
1          comment_approved_date_gmt 1            comment_approved A            29
1          comment_approved_date_gmt 2            comment_date_gmt A         13694
1          comment_date_gmt          1            comment_date_gmt A         13694
1          comment_parent            1            comment_parent   A             2

Gut, es gibt also einen Index für comment_post_ID und einen für Status und Datum zusammen. Darum müssen wir uns nicht kümmern. Allerdings habe ich gesehen, dass Ramona das Plugin Subscribe to Double-Opt-In Comments verwendet. Dieses fügt der Kommentardatenbank drei Spalten hinzu, jedoch keinen Index. Dann machen wir das doch einfach.

Einen Index erstellen wir in MySQL so:

ALTER TABLE TABLE_NAME ADD INDEX (COLUMN_NAME);

Wir können auch einen eigenen Namen vergeben:

ALTER TABLE TABLE_NAME ADD INDEX CUSTOM_NAME (COLUMN_NAME);

Und jetzt erzeugen wir drei Indexe für die zusätzlichen Spalten, damit die Abfragen ein wenig schneller gehen. Spezielle Namen brauchen wir nicht.

ALTER TABLE `wp_comments` ADD INDEX (`comment_subscribe_optin`);
ALTER TABLE `wp_comments` ADD INDEX (`comment_subscribe_optin_verified`);
ALTER TABLE `wp_comments` ADD INDEX (`comment_subscribe_optin_mailed`);

Der Index wird jetzt bei jedem Update der Datenbank aktualisiert. Das kostet Zeit und zusätzlichen Platz, deshalb sollten wir normalerweise nicht einfach jeden Index erzeugen, der uns in den Sinn kommt.

Welche Beiträge erleiden den meisten Spam?

Je populärer ein Beitrag ist, desto mehr Spam zieht er an. Manche Beiträge bekommen fast nie welchen ab, andere werden geradezu überrannt. Die 20 meistbespammten Beiträge bekommen wir mit diesem Query:

SELECT `comment_post_ID`, COUNT(*) as amount
FROM `wp_comments`
WHERE `comment_approved` = 'spam'
GROUP BY `comment_post_ID`
ORDER BY amount DESC
LIMIT 0, 20

Ergebnis

comment_post_ID  amount
1776               2299
2184               1840
143                1759
1366               1145
2377                960
2456                512
2536                497
683                 486
1373                236
1402                200
1522                174
2458                161
1168                127
10473               121
647                 116
2357                114
2831                111
2420                110
741                  94

Wow, was für ein Gefälle! Diese 11062 Kommentare umfassen fast den kompletten Spam. 92 Beiträge mit Kommentaren gibt es derzeit, 123 sind es insgesamt. Jetzt könnten wir für diese Beiträge die Kommentare mit einer Anmeldepflicht schützen oder ganz schließen. Beides nicht ideal.

Sehen wir uns einmal nicht die Ziele des Spams an, sondern dessen Herkunft.

Woher kommen die meisten Spamkommentare?

SELECT   `comment_author_IP`, COUNT(*) AS amount
FROM     `wp_comments`
WHERE    `comment_approved` = 'spam'
GROUP BY `comment_author_IP`
HAVING    amount > 10
ORDER BY  amount DESC
LIMIT     0, 30

Ergebnis

comment_author_IP   amount
173.242.120.58      450
198.200.37.51       386
198.200.37.59       287
192.74.240.170      268
109.163.236.87      218
192.74.230.131      181
212.59.28.172       157
198.200.37.83       153
91.236.74.117       148
212.59.28.221       147
142.0.138.77        140
142.0.136.9         117
192.74.228.242      109
142.4.117.43         91
137.175.1.235        88
142.4.97.173         82
142.4.117.162        80
198.200.33.140       80
192.74.228.161       73
193.105.210.217      63
142.4.96.66          63
91.231.40.28         55
193.105.210.216      54
142.4.96.74          54
192.74.228.164       51
192.162.19.193       48
80.93.213.249        48
192.74.231.156       46
142.0.136.12         46
91.231.40.27         46

Hier können wir ansetzen. Von manchen IP-Adressen kommt so viel Spam, dass wir sie getrost aussperren können. Das geht beispielsweise in der .htaccess so:

# IP block list
order allow,deny
deny from 173.242.120.58
allow from all

Allerdings ist das immer noch etwas umständlich; deshalb zeigt euch Christian bald, wie man Kommentare komplett verhindert, von deren IP-Adresse schon einmal Spam gesendet wurde.

Für heute habe ich euch hoffentlich ein wenig Lust darauf gemacht, mit SQL herumzuspielen. Reine SELECT-Queries sind ungefährlich. Im schlimmsten Fall fragt ihr zu viele Daten ab, und PHP oder MySQL nehmen sich eine Pause.