Tag Archives: Linux

Printing relative timestamps with annotate-output.

There is a simple tool in Linux that timestamps every single line of output, it is called annotate-output and is contained in the devscripts package. Sample output is like this:

€ annotate-output +%T ping localhost -c3
09:25:24 I: Started ping localhost -c3
09:25:24 O: PING localhost (127.0.0.1) 56(84) bytes of data.
09:25:24 O: 64 bytes from localhost (127.0.0.1): icmp_seq=1 ttl=64 time=0.053 ms
09:25:25 O: 64 bytes from localhost (127.0.0.1): icmp_seq=2 ttl=64 time=0.045 ms
09:25:26 O: 64 bytes from localhost (127.0.0.1): icmp_seq=3 ttl=64 time=0.056 ms
09:25:26 O:
09:25:26 O: --- localhost ping statistics ---
09:25:26 O: 3 packets transmitted, 3 received, 0% packet loss, time 1999ms
09:25:26 O: rtt min/avg/max/mdev = 0.045/0.051/0.056/0.007 ms
09:25:26 I: Finished with exitcode 0

Every single line is prepended with its timestamp. However sometimes it comes in handy when relative timestamps would be printed, but unfortunately there is no command line flag for that. There is a little trick to fool annotate-output to print its output with relative timestamps and fiddles a bit with the timezone setting passed to it. The trick is to set the current timezone such that it compensates for the current hour and minute:

TZ=$( TZ="UTC" date -d "now" "+UTC+%H:%M:%S" ) annotate-output +%T ping localhost -c3
00:00:00 I: Started ping localhost -c3
00:00:00 O: PING localhost (127.0.0.1) 56(84) bytes of data.
00:00:00 O: 64 bytes from localhost (127.0.0.1): icmp_seq=1 ttl=64 time=0.049 ms
00:00:01 O: 64 bytes from localhost (127.0.0.1): icmp_seq=2 ttl=64 time=0.052 ms
00:00:02 O: 64 bytes from localhost (127.0.0.1): icmp_seq=3 ttl=64 time=0.050 ms
00:00:02 O:
00:00:02 O: --- localhost ping statistics ---
00:00:02 O: 3 packets transmitted, 3 received, 0% packet loss, time 1998ms
00:00:02 O: rtt min/avg/max/mdev = 0.049/0.050/0.052/0.006 ms
00:00:02 I: Finished with exitcode 0

Removing spam from a Scuttle site

I run this Scuttle based website to keep track of some of my favourite Electronics websites. Today I came home from work and found a huge amount of spam entries on the site and by the number of entries it was quickly decided that this was a good opportunity to polish up my MySQL skills. Luckily for me I haven’t put up too many links on that tag cloud lately and thus the spam was easily identified by a recent date.

I logged on to the Scuttle database and checked the tables:

mysql> show tables;
+-------------------+
| Tables_in_scuttle |
+-------------------+
| sc_bookmarks |
| sc_tags |
| sc_users |
| sc_watched |
+-------------------+
4 rows in set (0.00 sec)

Quickly checking the website itself, it occured to me that there were two separate actions:

  • disable the users so they cannot log on any more to create new entries;
  • disable the spam entries so they are no longer visible.

Disable newly created users

First I had to check the names of the various columns in the sc_users table:

mysql> describe sc_users;
+-----------+--------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------------------+----------------+
| uId | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(25) | NO | | | |
| password | varchar(40) | NO | | | |
| uDatetime | datetime | NO | | 0000-00-00 00:00:00 | |
| uModified | datetime | NO | | 0000-00-00 00:00:00 | |
| name | varchar(50) | YES | | NULL | |
| email | varchar(50) | NO | | | |
| homepage | varchar(255) | YES | | NULL | |
| uContent | text | YES | | NULL | |
+-----------+--------------+------+-----+---------------------+----------------+
9 rows in set (0.01 sec)

The dDatetime column appears to contain useful data to filter for recently added accounts, especially when filtering on todays year and month only:

mysql> select username,uDatetime from sc_users where uDatetime>'2013-03-';
+-----------------+---------------------+
| username | uDatetime |
+-----------------+---------------------+
| clintayal | 2013-03-06 06:52:35 |
| utyklaudi | 2013-03-08 09:14:29 |
| jewellhue | 2013-03-08 10:48:06 |
| wilburnva | 2013-03-08 12:09:12 |
| reggieing | 2013-03-08 12:29:56 |
| janinaant | 2013-03-08 13:39:46 |
| shaunacha | 2013-03-08 14:17:29 |
| joannehas | 2013-03-08 18:01:04 |
| germanbow | 2013-03-08 18:04:52 |
| hildegard | 2013-03-08 18:18:01 |
| merediths | 2013-03-08 18:40:49 |
| czilaurie | 2013-03-08 19:11:39 |
| leoniepha | 2013-03-08 19:25:48 |
| damiondra | 2013-03-08 19:36:11 |
| lidiahear | 2013-03-08 19:56:02 |
| terrygood | 2013-03-08 20:03:32 |
| amelieover | 2013-03-08 20:04:36 |
| peggymccl | 2013-03-08 20:42:12 |
| hymannava | 2013-03-08 21:04:10 |
| tammarazi | 2013-03-08 21:33:19 |
| qsakelsey | 2013-03-08 22:05:36 |
| lynellxgw | 2013-03-08 22:27:30 |
| dillonii | 2013-03-08 22:29:12 |
| doreencre | 2013-03-08 22:39:25 |
| mavismadd | 2013-03-08 23:04:57 |
| thomasbls | 2013-03-08 23:48:37 |
| lawanna85 | 2013-03-08 23:49:45 |
| harrietts | 2013-03-08 23:49:57 |
| brent0 | 2013-03-09 00:12:41 |
| aileenstra | 2013-03-09 01:17:36 |
| trudybord | 2013-03-09 01:36:10 |
| joycemalo | 2013-03-09 02:47:57 |
| dqhwendi | 2013-03-09 04:59:44 |
| keeshafor | 2013-03-09 05:11:50 |
| priscilla | 2013-03-09 05:16:35 |
| selenamul | 2013-03-09 06:24:06 |
| darlaorr | 2013-03-09 07:23:42 |
| geoffreye | 2013-03-09 08:08:04 |
| corneliusr | 2013-03-09 08:08:37 |
| heidiradf | 2013-03-09 09:04:38 |
| charitymc | 2013-03-09 09:55:55 |
| lorenalbr | 2013-03-09 10:35:37 |
| edgarwest | 2013-03-09 10:41:40 |
| junepelti | 2013-03-09 10:43:56 |
| louienmn | 2013-03-09 10:47:06 |
| youngball | 2013-03-09 10:55:50 |
| gordonacu | 2013-03-09 11:12:29 |
| melindast | 2013-03-09 11:32:30 |
| rustylitt | 2013-03-09 11:38:02 |
| bruce93i | 2013-03-09 12:21:06 |
| dianagrif | 2013-03-09 13:41:05 |
| angel5090 | 2013-03-09 14:48:41 |
| clintonca | 2013-03-09 15:08:24 |
| finlaypurv | 2013-03-09 15:21:27 |
| xsksusann | 2013-03-09 17:16:47 |
| rhodabris | 2013-03-09 18:27:16 |
| joannepra | 2013-03-09 18:28:39 |
| jamesrohd | 2013-03-09 18:28:47 |
| roxanaste | 2013-03-09 18:29:04 |
| mirtagdfa | 2013-03-09 18:29:27 |
| fidelia49 | 2013-03-09 18:29:53 |
| stephena7 | 2013-03-09 18:30:17 |
| jeramytkk | 2013-03-09 18:31:39 |
| lorenzouk | 2013-03-09 18:51:53 |
| terrellnf | 2013-03-09 19:57:37 |
| kitnmzwea | 2013-03-09 20:12:12 |
| antoine06 | 2013-03-09 20:36:58 |
| shelby230 | 2013-03-09 21:16:32 |
| deliabarr | 2013-03-09 21:31:09 |
| lavonnesh | 2013-03-09 22:04:28 |
| andrawyli | 2013-03-09 23:37:55 |
| noexpvfyvd | 2013-03-09 23:43:06 |
| kareemwor | 2013-03-09 23:44:38 |
| bradygand | 2013-03-09 23:47:18 |
| joshmetz | 2013-03-10 00:04:26 |
| bellcorey | 2013-03-10 00:09:35 |
| debwensub | 2013-03-10 00:22:36 |
| nathanielm | 2013-03-10 01:02:43 |
| justinadu | 2013-03-10 01:40:35 |
| isiahsmxh | 2013-03-10 01:46:01 |
| hazelmcca | 2013-03-10 01:49:06 |
| oliviarit | 2013-03-10 03:20:05 |
| matthewbe | 2013-03-10 03:48:28 |
| monicaluc | 2013-03-10 04:17:48 |
| elissasee | 2013-03-10 05:16:58 |
| chadwickce | 2013-03-10 06:23:55 |
| billv | 2013-03-10 06:28:12 |
| michelsch | 2013-03-10 06:48:55 |
| christyca | 2013-03-10 08:24:38 |
| rebbecach | 2013-03-10 09:34:49 |
| oscarsaen | 2013-03-10 11:39:14 |
| marvinwof | 2013-03-10 11:53:01 |
| andersonm | 2013-03-10 12:00:31 |
| juliusgra | 2013-03-10 12:10:01 |
| lance47f | 2013-03-10 12:16:59 |
| dennisgle | 2013-03-10 12:41:50 |
| hattiel20 | 2013-03-10 12:51:12 |
| christyth | 2013-03-10 12:52:14 |
| sylvester | 2013-03-10 12:55:44 |
| allanzof | 2013-03-10 13:38:27 |
| florianoe | 2013-03-10 13:43:24 |
| spencer3h | 2013-03-10 13:50:19 |
| brendasug | 2013-03-10 17:52:22 |
| ameesampl | 2013-03-10 18:31:57 |
| lawannahw | 2013-03-10 21:06:43 |
| dannyburt | 2013-03-10 21:21:38 |
| deloraswhi | 2013-03-10 21:29:38 |
| jesusnall | 2013-03-10 21:46:50 |
| lindseymc | 2013-03-10 22:18:38 |
| leannebil | 2013-03-10 22:28:31 |
| benedictd | 2013-03-10 22:30:50 |
| candice52 | 2013-03-10 22:42:05 |
| vfrzack | 2013-03-10 23:02:52 |
| fletcherp | 2013-03-10 23:25:47 |
| alvaroozw | 2013-03-10 23:26:54 |
| raleighb6 | 2013-03-10 23:39:07 |
| deliawolc | 2013-03-10 23:56:07 |
| lxxlashon | 2013-03-11 00:14:09 |
| ginohorto | 2013-03-11 00:44:24 |
| fayohault | 2013-03-11 00:44:26 |
| nildat42 | 2013-03-11 01:04:07 |
| concetta4 | 2013-03-11 01:04:40 |
| suzannel53 | 2013-03-11 01:58:14 |
| ginaearls | 2013-03-11 02:18:26 |
| lucindalem | 2013-03-11 03:09:11 |
| indiradurr | 2013-03-11 03:48:32 |
| valentina6 | 2013-03-11 05:00:02 |
| ckpveroni | 2013-03-11 05:40:50 |
| marianamsi | 2013-03-11 05:53:09 |
| torymorel | 2013-03-11 05:56:50 |
| carolekke | 2013-03-11 06:06:29 |
| devinburdzil80 | 2013-03-11 06:07:34 |
| williemaef | 2013-03-11 06:23:38 |
| jeannejac | 2013-03-11 06:56:22 |
| emanuelxdv | 2013-03-11 07:19:57 |
| luisdugan | 2013-03-11 07:37:53 |
| maxwellgu | 2013-03-11 07:48:16 |
| hershelcl | 2013-03-11 08:06:20 |
| lavadapar | 2013-03-11 08:14:31 |
| augustus1 | 2013-03-11 08:49:51 |
| jamaalven | 2013-03-11 10:17:59 |
| katherina | 2013-03-11 10:25:47 |
| lorettama | 2013-03-11 10:25:53 |
| gxatricia | 2013-03-11 10:48:09 |
| garfieldqv | 2013-03-11 10:51:50 |
| carlosauc | 2013-03-11 12:27:08 |
| estelab04 | 2013-03-11 12:44:28 |
| libbyt10 | 2013-03-11 12:59:03 |
| mablewhit | 2013-03-11 13:10:56 |
| groveraoe | 2013-03-11 13:18:19 |
| normahiat | 2013-03-11 14:27:39 |
| kateuo | 2013-03-11 14:53:27 |
| gerardogu | 2013-03-11 15:33:09 |
| lyndonlan | 2013-03-11 16:12:20 |
| jefferyka | 2013-03-11 16:36:57 |
| merrillkallas47 | 2013-03-11 17:28:59 |
| willabeau | 2013-03-11 17:39:37 |
| yettaholli | 2013-03-11 18:01:56 |
| irvin00h | 2013-03-11 18:32:21 |
| stevecaste | 2013-03-11 18:40:44 |
| brucedaf | 2013-03-11 18:44:21 |
| martinaxba | 2013-03-11 19:02:42 |
+-----------------+---------------------+
162 rows in set, 1 warning (0.00 sec)

A while ago already I had figured out that setting the password column to -1 was sufficient to keep the user from logging in again. Since there were 162 new users created in the past few days I figured I had to execute a smart MySQL statement instead of clicking around in phpmyadmin:

mysql> update sc_users set password=-1 where uDatetime>'2013-03-';
Query OK, 162 rows affected, 1 warning (0.01 sec)
Rows matched: 162 Changed: 162 Warnings: 1

Removing the spam

Next step was to remove the actual spam from the website. The bookmarks were also created during this month and again filtering was pretty easy. First I had to check the columns in the related table:

mysql> describe sc_bookmarks;
+--------------+--------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------------------+----------------+
| bId | int(11) | NO | PRI | NULL | auto_increment |
| uId | int(11) | NO | MUL | 0 | |
| bIp | varchar(40) | YES | | NULL | |
| bStatus | tinyint(1) | NO | | 0 | |
| bDatetime | datetime | NO | MUL | 0000-00-00 00:00:00 | |
| bModified | datetime | NO | | 0000-00-00 00:00:00 | |
| bTitle | varchar(255) | NO | | | |
| bAddress | text | NO | | NULL | |
| bDescription | varchar(255) | YES | | NULL | |
| bHash | varchar(32) | NO | MUL | | |
+--------------+--------------+------+-----+---------------------+----------------+
10 rows in set (0.00 sec)

To double check the selection that I was about to disable, I checked it with a select statement:

mysql> select bIp,bDatetime from sc_bookmarks where bDatetime>'2013-03-';
+-----------------+---------------------+
| bIp | bDatetime |
+-----------------+---------------------+
| 194.71.223.204 | 2013-03-08 12:30:09 |
| 94.23.180.200 | 2013-03-06 06:52:37 |
| 219.234.82.86 | 2013-03-08 09:14:37 |
| 198.143.144.148 | 2013-03-08 10:48:09 |
| 192.119.148.228 | 2013-03-08 12:09:18 |
| 50.31.107.54 | 2013-03-08 13:40:27 |
| 147.255.162.169 | 2013-03-08 14:17:34 |
| 173.0.51.155 | 2013-03-08 18:01:09 |
| 173.0.51.155 | 2013-03-08 18:05:01 |
| 173.234.196.153 | 2013-03-08 18:18:14 |
| 50.115.168.164 | 2013-03-08 18:41:00 |
| 198.143.145.198 | 2013-03-08 19:11:40 |
| 173.0.51.155 | 2013-03-08 19:36:14 |
| 173.0.51.155 | 2013-03-08 19:38:04 |
| 147.255.162.207 | 2013-03-08 19:56:11 |
| 46.105.189.183 | 2013-03-08 20:04:40 |
| 69.197.190.56 | 2013-03-08 20:42:22 |
| 147.255.162.124 | 2013-03-08 21:04:16 |
| 173.234.196.153 | 2013-03-08 21:35:58 |
| 200.8.218.78 | 2013-03-08 22:06:09 |
| 5.34.241.70 | 2013-03-08 22:27:38 |
| 184.154.90.74 | 2013-03-08 22:29:54 |
| 125.39.68.130 | 2013-03-08 23:05:00 |
| 188.40.253.69 | 2013-03-08 23:48:47 |
| 188.40.253.69 | 2013-03-08 23:49:53 |
| 173.213.99.153 | 2013-03-08 23:50:05 |
| 198.167.136.235 | 2013-03-09 00:13:24 |
| 151.237.188.73 | 2013-03-09 01:18:19 |
| 66.219.27.158 | 2013-03-09 01:36:12 |
| 81.17.30.179 | 2013-03-09 02:48:12 |
| 194.71.224.237 | 2013-03-09 04:59:47 |
| 147.255.162.207 | 2013-03-09 05:16:40 |
| 109.175.8.42 | 2013-03-09 06:26:57 |
| 147.255.162.207 | 2013-03-09 07:23:49 |
| 96.127.137.252 | 2013-03-09 08:08:17 |
| 46.4.241.85 | 2013-03-09 09:04:48 |
| 108.178.5.12 | 2013-03-09 09:56:01 |
| 200.121.15.112 | 2013-03-09 10:36:06 |
| 198.143.159.110 | 2013-03-09 10:42:02 |
| 198.143.159.110 | 2013-03-09 10:43:59 |
| 198.143.159.110 | 2013-03-09 10:47:07 |
| 173.0.50.13 | 2013-03-09 10:55:51 |
| 94.242.241.204 | 2013-03-09 11:12:29 |
| 220.134.184.161 | 2013-03-09 11:32:35 |
| 220.134.184.161 | 2013-03-09 11:38:08 |
| 91.120.127.127 | 2013-03-09 12:21:29 |
| 184.82.222.218 | 2013-03-09 13:41:05 |
| 173.234.196.193 | 2013-03-09 14:48:56 |
| 200.151.208.58 | 2013-03-09 15:09:50 |
| 184.154.255.58 | 2013-03-09 15:21:41 |
| 37.203.214.129 | 2013-03-09 17:16:48 |
| 37.59.69.68 | 2013-03-09 18:27:25 |
| 37.59.69.68 | 2013-03-09 18:28:43 |
| 37.59.69.68 | 2013-03-09 18:28:51 |
| 37.59.69.68 | 2013-03-09 18:29:08 |
| 37.59.69.68 | 2013-03-09 18:29:30 |
| 37.59.69.68 | 2013-03-09 18:29:56 |
| 37.59.69.68 | 2013-03-09 18:30:22 |
| 37.59.69.68 | 2013-03-09 18:31:43 |
| 123.125.116.241 | 2013-03-09 18:52:15 |
| 46.227.71.177 | 2013-03-09 19:58:19 |
| 219.234.82.55 | 2013-03-09 20:12:20 |
| 219.234.82.72 | 2013-03-09 20:37:13 |
| 194.71.222.103 | 2013-03-09 21:16:37 |
| 219.234.82.76 | 2013-03-09 21:31:20 |
| 219.234.82.53 | 2013-03-09 22:04:35 |
| 123.125.116.242 | 2013-03-09 23:38:01 |
| 66.248.200.17 | 2013-03-09 23:43:19 |
| 66.248.193.131 | 2013-03-09 23:45:08 |
| 64.120.239.132 | 2013-03-09 23:47:23 |
| 219.234.82.81 | 2013-03-10 00:04:34 |
| 173.234.57.198 | 2013-03-10 00:09:37 |
| 219.234.82.72 | 2013-03-10 00:22:50 |
| 198.143.165.198 | 2013-03-10 01:03:05 |
| 188.136.216.130 | 2013-03-10 01:46:08 |
| 188.136.216.130 | 2013-03-10 01:49:13 |
| 219.234.82.88 | 2013-03-10 03:13:30 |
| 82.200.254.138 | 2013-03-10 03:20:46 |
| 46.227.70.244 | 2013-03-10 03:48:32 |
| 219.234.82.83 | 2013-03-10 04:17:53 |
| 219.234.82.83 | 2013-03-10 05:17:17 |
| 91.108.177.186 | 2013-03-10 06:24:00 |
| 96.127.137.250 | 2013-03-10 06:28:57 |
| 173.0.51.225 | 2013-03-10 06:52:57 |
| 173.0.51.225 | 2013-03-10 06:53:21 |
| 173.0.51.225 | 2013-03-10 07:38:12 |
| 173.0.51.225 | 2013-03-10 07:49:57 |
| 173.0.51.225 | 2013-03-10 07:57:38 |
| 173.234.196.251 | 2013-03-10 08:14:25 |
| 66.248.202.146 | 2013-03-10 08:24:41 |
| 69.197.190.56 | 2013-03-10 08:35:29 |
| 173.234.196.251 | 2013-03-10 09:35:01 |
| 147.255.162.177 | 2013-03-10 11:16:37 |
| 69.175.97.76 | 2013-03-10 11:39:16 |
| 173.234.196.83 | 2013-03-10 11:44:06 |
| 5.135.11.113 | 2013-03-10 11:53:01 |
| 219.234.82.84 | 2013-03-10 12:00:45 |
| 219.234.82.73 | 2013-03-10 12:10:09 |
| 66.248.193.180 | 2013-03-10 12:17:02 |
| 173.234.196.153 | 2013-03-10 12:20:58 |
| 66.248.193.251 | 2013-03-10 12:41:53 |
| 192.119.154.206 | 2013-03-10 12:51:14 |
| 192.119.154.206 | 2013-03-10 12:52:17 |
| 192.119.154.206 | 2013-03-10 12:55:47 |
| 69.175.25.2 | 2013-03-10 13:38:30 |
| 186.219.25.226 | 2013-03-10 13:47:43 |
| 37.72.190.105 | 2013-03-10 13:51:14 |
| 219.234.82.55 | 2013-03-10 17:52:30 |
| 69.175.29.69 | 2013-03-10 18:32:03 |
| 173.0.51.141 | 2013-03-10 21:06:51 |
| 69.175.112.21 | 2013-03-10 21:21:44 |
| 91.108.177.187 | 2013-03-10 21:31:23 |
| 173.234.196.193 | 2013-03-10 21:39:41 |
| 5.135.4.16 | 2013-03-10 21:46:54 |
| 192.119.151.232 | 2013-03-10 22:18:54 |
| 66.248.193.171 | 2013-03-10 22:28:38 |
| 5.144.176.5 | 2013-03-10 22:30:52 |
| 88.80.21.218 | 2013-03-10 22:42:06 |
| 219.234.82.82 | 2013-03-10 23:03:18 |
| 195.190.13.117 | 2013-03-10 23:25:47 |
| 178.33.83.66 | 2013-03-10 23:27:00 |
| 176.31.3.242 | 2013-03-10 23:39:09 |
| 192.119.148.223 | 2013-03-10 23:56:13 |
| 197.220.163.75 | 2013-03-11 00:14:17 |
| 147.255.162.177 | 2013-03-11 00:16:59 |
| 94.23.118.183 | 2013-03-11 00:44:30 |
| 199.192.205.77 | 2013-03-11 00:44:30 |
| 96.127.137.250 | 2013-03-11 01:04:24 |
| 91.108.177.170 | 2013-03-11 01:04:48 |
| 147.255.162.207 | 2013-03-11 01:26:18 |
| 69.175.58.62 | 2013-03-11 01:58:20 |
| 69.175.97.75 | 2013-03-11 02:18:29 |
| 66.248.193.118 | 2013-03-11 03:09:16 |
| 147.255.162.195 | 2013-03-11 03:38:00 |
| 66.248.194.60 | 2013-03-11 03:48:38 |
| 199.101.102.24 | 2013-03-11 04:09:24 |
| 66.248.193.50 | 2013-03-11 05:00:07 |
| 199.119.227.220 | 2013-03-11 05:40:53 |
| 66.248.194.60 | 2013-03-11 05:53:18 |
| 110.77.213.186 | 2013-03-11 05:58:46 |
| 110.77.213.186 | 2013-03-11 06:06:33 |
| 109.169.73.116 | 2013-03-11 06:07:34 |
| 69.175.112.21 | 2013-03-11 06:23:44 |
| 74.221.208.145 | 2013-03-11 06:56:27 |
| 184.154.174.10 | 2013-03-11 07:20:04 |
| 37.72.190.105 | 2013-03-11 07:38:08 |
| 88.80.20.191 | 2013-03-11 07:48:17 |
| 5.144.176.11 | 2013-03-11 08:06:28 |
| 192.119.144.219 | 2013-03-11 08:14:36 |
| 78.46.70.86 | 2013-03-11 08:49:55 |
| 178.73.202.147 | 2013-03-11 10:18:00 |
| 125.110.223.248 | 2013-03-11 10:25:49 |
| 125.110.223.248 | 2013-03-11 10:25:54 |
| 216.151.139.241 | 2013-03-11 10:48:11 |
| 199.116.113.236 | 2013-03-11 10:51:59 |
| 5.34.241.69 | 2013-03-11 10:59:29 |
| 5.135.53.225 | 2013-03-11 12:27:13 |
| 88.80.21.98 | 2013-03-11 12:44:45 |
| 192.119.144.202 | 2013-03-11 12:59:09 |
| 192.119.148.190 | 2013-03-11 13:11:01 |
| 194.71.223.57 | 2013-03-11 13:18:25 |
| 88.80.21.92 | 2013-03-11 14:28:24 |
| 173.236.40.142 | 2013-03-11 14:53:46 |
| 119.191.60.150 | 2013-03-11 15:33:46 |
| 50.115.173.137 | 2013-03-11 15:37:47 |
| 5.135.53.232 | 2013-03-11 16:12:27 |
| 173.234.196.251 | 2013-03-11 16:19:13 |
| 89.237.134.10 | 2013-03-11 16:37:00 |
| 173.234.196.153 | 2013-03-11 17:14:10 |
| 109.169.73.116 | 2013-03-11 17:29:00 |
| 222.218.152.92 | 2013-03-11 17:40:36 |
| 184.154.254.174 | 2013-03-11 18:02:08 |
| 80.191.78.251 | 2013-03-11 18:32:34 |
| 66.248.193.118 | 2013-03-11 18:40:52 |
| 173.213.96.64 | 2013-03-11 18:44:26 |
| 173.236.35.146 | 2013-03-11 19:02:55 |
| 198.167.136.240 | 2013-03-11 19:36:28 |
+-----------------+---------------------+
177 rows in set, 2 warnings (0.00 sec)

To finally actually disable the bookmarks with a similar trick as disabling the users: putting -1 to the bId column:

mysql> update sc_bookmarks set bStatus=-1 where bDatetime>'2013-03-';
Query OK, 177 rows affected, 3 warnings (0.01 sec)
Rows matched: 177 Changed: 177 Warnings: 3

Adding the IP-addresses to the firewall

To prevent these misbehaving IP-addresses to deface my webserver again, I put up a iptables firewall to easily block them with an ipset command. It is outside the scope of this article to explain the iptables firewall, but this is the related line in iptables:

-A INPUT -m set --match-set brute_force src -m comment --comment "misbehaving IPs" -j DROP

And this is how I inserted the IP-addesses all at once from the Linux command line:

mysql -username -password database -s -e \
"select distinct bIp from sc_bookmarks where bDatetime>'2013-03-';" |
while read ip; do sudo /usr/sbin/ipset --add brute_force $ip; done

It feels good for once to be able to remove spam more quickly than it took them to put it up there.