Gameboy Genius

Regarding the message on the 8bitcollective front page…

Today, I (and everyone else) woke up to see the following message on the 8bitcollective front page:

Or as text:

Dear 8bitcollective,
Yesterday, April 17, 2010, 8bc was the victim of unauthorized and illegal entry and disruption by certain individuals. We have and continue to work with authorities to identify such individuals and have in fact, determined who they are. These acts are not only malicious but criminal as well and the perpetrators are not immune because they are operating from foreign countries.
It is our policy that anyone who attempts to disrupt the operation of 8bc will not only suffer permanent banishment from the site but will be subject to criminal investigation and proceedings both in the United States and the country in which they reside.
We apologize to our members for any disruption in service caused by these individuals and we would like to assure you that we are taking every step possible to see to it that anyone intent on harming our site will suffer the full consequences of the law.
This message will remain posted for 24 hours.

Because of this, I would like to clarify a couple of things to those who may be wondering:

1. Pepi158 who posted in this thread is not a sockpuppet account of Jose Torres as I implied in that thread.
2. Instead, the account apparently belongs to José Luis Torres, a New York based attorney who is most probably Jose’s (Bleepbloop’s) father.
3. On the evening of last Friday, José Luis Torres sent me a rather threatening legal e-mail accusing me of having “maliciously trespassed and hacked” 8bitcollective. For the purpose of full disclosure:
4. I used to be an administrator of 8bitcollective, until March 4 this year when I was demoted from that position by Jose Torres (Bleepbloop). I was warned that “If [I] continue to access any resources without [Jose's] expressed permission, [they] will take legal action.”
5. Between March 4 and April 17 I did have a(n unauthorized) way to obtain limited shell access as well as database access to the 8bitcollective server.
6. Between March 4 and April 17 I did occasionally access these resources, however not for any kind of malicious or disruptive purposes, but rather to “keep an eye” on the server as well as continue to perform regular backups of the database. While this might be technically illegal I do not believe that I have done anything to the 8bitcollective server that is generally objectionable.
7. I have never, neither on April 17, nor at any other point in time performed, assisted, aided or otherwise been involved in any kind of “attack” against the 8bitcollective server. (I believe in making a point with words, not violence and silence.)

But, I was actually connected to the 8bitcollective server during the “disruption in service” that happened during the morning of April 17, as I’m sure Jose’s logs indicate. However, I was not causing it, I was troubleshooting it, which I will elaborate on below.

The symptom of the problem was that any page on the site took an extremely long time to load, and when it loaded it would display a message to the effect of “MySQL connection refused: Too many connections.”. Now this message is not new to me. In fact, it happened before on 8bitcollective (May or June 2009, if I recall correctly) and that time it happened because of a bug, more specifically a SQL query that locked the database for on average 0.5 seconds when anyone loaded the front page, and if the same query was issued again during the same time (I.e., a second person was trying to access the front page while the it was still loading for the first person) the database would go into a deadlock condition and be inaccessible for a long time, resulting in that message being displayed for anyone trying to visit the site.

The first thing I did when this happened originally was to log in to the database (Remember I was still an administrator at that time) and run the command SHOW PROCESSLIST;. This command shows a listing of the currently running queries to the database. First this message was believed to be the result of a denial of service (DoS) attack against the site, but the problem with SHOW PROCESSLIST; is that it doesn’t give you any hint as to which page or visitor IP address issued a query.

To come around this, I added this snippet of code to the database adapter:

		$sqlip = '/*' . $_SERVER["REMOTE_ADDR"] .
			str_replace(’*',”,$_SERVER["REQUEST_URI"]).
			“*/\n”;

		$sql = $sqlip . $sql;

What this code does, simply speaking is to prepend the IP address and the page being visited to each database query as a comment. (For those who may be wondering, the str_replace part is there for security.) This has no impact on performance but offers useful information in the SHOW PROCESSLIST; listing.

So if the issues that happened in the summer of 2009 were a DoS attack, you’d likely see a pattern in the IP addresses accessing the site. However, I could find no discernible pattern in the IP addresses. However, I found out the “offending” query was missing the comment tag that I added, because the query, just like most 8bc specific queries in the code, are not called through the PunBB DB adapter, $db->query, but directly through mysql_query.

Before I got to the point of rewriting every single 8bc specific query to use $db->query I actually found the problem, namely the function ebc_get_buzz (Probably written by Jose himself) which was used to show a list of the 5 latest forum posts on the front page. However, it was soon removed because it was badly written:

1. It didn’t filter the posts according to the user access masks, so posts from the hidden admin/mod forum would show up on the front page.
2. It didn’t do anything to filter the outputted text, so one could theoretically have carried out a XSS (Cross site scripting) attack by simply entering HTML and Javascript code into a forum post.
3. It sorted the outputted data by the posted column which is a non-indexed datetime field, which made it extremely slow.

Points 1 and 2 were soon discovered and the listing of threads was removed from the front page. However, the call to the function ebc_get_buzz still existed in index.php which still made the front page load extremely slowly and still occasionally caused the database to lock up when two or more people visited the front page at the same time!

When I finally found out what the problem was, I called for a refactorization and thorough bug check of the source code of 8bitcollective. None of us involved in the site at the time had much time to spend on doing it, but I briefly checked the code and actually found a plain-as-day SQL injection vulnerability in the file upload code which I fixed. But I’m pretty sure there are a lot of other bugs crawling around in the 8bc code still to this date.

So what about yesterday’s issues? As mentioned before I did log in in to the server to during these issues, however I did not cause them, I logged on to analyze the issue, exactly for the purpose of not being incriminated for the possible attacks. Among the things I did was to issue the SHOW PROCESSLIST; command in order to see if I could find anything useful. It turns out that, just like last time, there was a single query with “Copying to tmp table” status followed by a shitload of “Locked” status queries choking the database. So let’s analyze the queries that I found. Since they were not called through the DB adapter, there is no information on the calling IP, however I could still easily locate them in the source code.

• The first offending query looked like this in the process list:

  SELECT\n\t\t\t\t\t\n\t\t\t\t\tm.itemid,\n\t\t\t\t\tm.title,\n\t\t\t\t\tm.votes,\n\t\t\t\t\tm.rating,\n\t\t\t\t\tm.author,\n\t\t\t\t\tm.userid,

  While this may look like mumbo-jumbo to the untrained eye I was quickly able to locate this query to the ebc_search function which is the function used to search for media (music and images) when the user enters a search term in the search field on any media page. There are several problems with this function:

  1. It has no size limit for the search string and it’s using LIKE ‘%[search string gose here]%’, which makes it very easy to abuse that function by entering a short (one letter) search term.
  2. It calculates the view count by using a combination of COUNT and GROUP BY which creates a temporary table containing a number of rows equivalent to the sum of the views of all items in the result. This could easily lead to result sets of hundreds of thousands of rows, for a single search. Needless to say, that’s a very badly designed query. There should be no excuse for not using the cached views value in the 8bc_music/images tables!
  3. It doesn’t even have primitive throttling, meaning that a user can issue a new search query before the old one is finished.
  4. To make things even worse, there are two possible indexing problems: The index on the type column is actually likely to slow things down. Discuss this with Tim (trash80) for an explanation. title in 8bc_images is not indexed which might cause a small slowdown when searching for images.

  Jose! My suggestion is that you immediately take down the media search function until you have rewritten it to fix those bugs.

• The next query I happened to detect causing trouble looks like this:

  SELECT count(*) FROM `8bc_views` WHERE itemid=’31837′ and type=’music’

  Again, this query was easy to locate. It exists in the ebc_view function and calculates the number of views an item has. Again, this is just a horrendous way of doing things. Instead of always selecting COUNT from 8bc_views ebc_is_new returns true, you should simply insert the new view to 8bc_views and then increment the view count with SET `views` = `views` + 1 in the next query.

While I do acknowledge that my access to the server was unauthorized, and possibly technically illegal, I think you, Jose, should thank me for taking the time to find these bugs and point them out to you, so you can fix them. They would’ve existed with or without my unauthorized access, and would’ve been just as exploitable either way. The morale of this story is that you should fix your goddamn bugs before jumping to a conclusion and incriminating someone for an unfounded reason. The issues were, as demonstrated above, not caused by my access to the server but due to someone using the search function, and visiting a song page with many views, respectively.

While it’s certainly possible that someone kept reloading the search page in order to choke the database connection, to “support me” after the events that took place between me and Jose’s father, or “hurt Jose” or some bullshit like that, (Please don’t do that, people) the sad thing is that the code is so badly written that it is just as likely that someone was causing that disruption by one or more completely legitimate but unfortunate search queries.

On the grander scale of things, however, I’ve been considering the validity of the legal threats that were sent to me by the attorney who is most likely Jose’s father. (And who incidently uttered “Hey nitro2k01, get a life! Why are you always hounding Jose.” in the forums.) I’ve come to a simple conclusion: Jose, you aint got shit on me! If you do believe that you have a legal case that holds water, by all means go ahead and pursue it. However, I believe that it is much more likely that you are using legal threats in the hope that it will have a chilling effect on my nagging comments about you in the 8bc forums. For Low-Gain and Kitsch, all you could do was to delete their accounts, and we all know how that turned out. For me you do have the benefit of a possible legal threat (although I think you’ll find it hard to prove any actual damage caused by me.) If your legal threats are in fact just a scare tactic (and even if they aren’t) you should think long and hard about whether it would actually be a good thing for your already tarnished reputation to proceed with them.