As written in my previous blog post, Carders.cc database is in the wild and the database content give you interesting informations.
First we will take a look to the “user” table. This table content 8 425 entries with a first registration date the “Sep-17-2008 16:09” and the last registration date the “Dec-06-2010 00:12“. Actually we can suppose that the dump was made the 6 December, and that the server was owned before the 6 December.
With a simple SQL query we will export a CSV file in order to create a Google visualization gadget for the number of registrations (joindate table field) by day’s.
SELECT date(from_unixtime(joindate)) as bydate, count(*) as byday FROM user group by bydate order by bydate asc
Here you will get the Google visualization result, in the “UsersRegistrations1” tab.
As you maybe remember Carders.cc was the target of the “Owned and Exposed” team at the beginning of May 2010. The complete site was “rm’ed“. But as you can see that the 22 May, the website was back online and the registrations have re-begin. Also you can see a second pick of registrations beginning the 23 October.
Now we will check the lastvisit table field how represent the timestamp of the users lastvisits. The first lastvisit date is the “May-25-2010 00:05” and the last one the “Dec-06-2010 00:12“. Here we can see that the Carders.cc had a backup of the forum database before the “rm’ed” of the server by the “Owned and Exposed” team. Some Carders.cc users how had register before the May 2010, have continu to use the same account after the restoration of the forum, for example KRON0S and Vitali.
Also with a SQL query we will export all the lastvisits timestamp to get an overview of last visits by day’s.
SELECT date(from_unixtime(lastvisit)) as bydate, count(*) as byday FROM user group by bydate order by bydate asc
Here you will get the Google visualization result, in the “UsersLastVisits1” tab.
You can see a pick of visits beginning the 2 to the 5 December. An abnormal activity how should be investigated into another post. The pick of new registrations between the 23 October to the 14 November is surely related.
To see the real number of users how have join Carders.cc forum since the May restoration of the forum, we will execute this query.
SELECT count(*) FROM `user` WHERE `joindate` > '1274738400'
And the result is 6 700 new users. So before the May “rm’ed” we got only 1 725 active users. Is the buzz around the first “Owned and Exposed” hack the reason of the crazy increase number of users ? We could think that the buzz had work, and that a lot of people how didn’t know Carders.cc before the May hack, have discover and join this community after the “Owned and Exposed” hack.
To have a clear view of the number of new registrations by day’s after the May 2010 hack, we will execute a new query and create another Google visualization in “UsersRegistrations2” tab.
SELECT date(from_unixtime(joindate)) as bydate, count(*) as byday FROM user WHERE joindate > '1274738400' group by bydate order by bydate asc
Clearly you can see that directly after the forum restauration, the number of new registration by day’s have increase comparing to the pre May 2010 hack. Before May 2010, 1 725 users in 591 day’s, so an average of 2,9 new users per day. After May 2010, 6 700 users in 166 day’s, so an average of 40,6 new users per day.
Just to be fair we will do the same calculation from the 25 May to the 23 October just before the registration pick.
SELECT count(*) FROM user WHERE joindate between '1274738400' and '1287784800'
We have 4068 users in 152 day’s, so an average of 26 new users per day. What is interesting is to see that after the 23 October registration pick we have 2632 new users in 42 day’s, so an average of 62,66 new users per day ! The 23 October registration pick is really confirmed as an abnormal forum lifecycle.
Carders.cc could say’s thank you to “Owned and Exposed” team for the buzz created by the hack, having before the May 10 hack an average of 2,9 new users per day, and after the hack an average of 26 new users per day.