Super Meat Boy leaves database wide open(forums.somethingawful.com)
forums.somethingawful.com
Super Meat Boy leaves database wide open
http://forums.somethingawful.com/showthread.php?noseen=0&threadid=2803713&pagenumber=258#post398884189
98 comments
I should probably note that at no point did I disclose the database credentials - other users discovered those and publicly posted them. I contacted Super Meat Boy several times before going public with this but they totally disregarded my warnings.
I found this image succinct and telling: http://img820.imageshack.us/img820/1641/itsfinetrustme.png
I'm going to dangle myself on a limb here.
If you consider the constraint that the indie programmer may have had incomplete skills to set up a proper service, saw this method as faster for any number of other reasons, or didn't want to maintain a proper web service, I think this could very well have been the right approach at the time. Judging from how much money this game has made, I think that's hard to argue with since time to market should have been the number 1 concern at the time and early on he needed a way to make sure people were able to complete all the levels and measure the difficulty curve.
When the developer responded that it didn't matter, that may have been because they don't need that data any more and don't even look at it.
If you consider the constraint that the indie programmer may have had incomplete skills to set up a proper service, saw this method as faster for any number of other reasons, or didn't want to maintain a proper web service, I think this could very well have been the right approach at the time. Judging from how much money this game has made, I think that's hard to argue with since time to market should have been the number 1 concern at the time and early on he needed a way to make sure people were able to complete all the levels and measure the difficulty curve.
When the developer responded that it didn't matter, that may have been because they don't need that data any more and don't even look at it.
In good tradition the most critical comment is the top voted on HN. Yada Yada indie life is hard, but no one with a right mind would think of connecting the client directly to the remote master database. This is just so WTF on so many levels, you can't help but wonder how they even managed to produce such a good game after all.
Yes, it seems obvious to web developers and developers that are used to working with databases and CRUD apps, but that's one set of skills over a particular domain. Game development tends to emphasise a whole different set of skills — those of efficient graphic rendering, for example.
A points table is no doubt a last minute add on in a field that certainly wouldn't be the expertise of a small indie developer (no-one goes into game development for their love of CRUD apps, after all). They needed a database so they used the most popular, in a way that probably seemed the most appropriate for their application.
A points table is no doubt a last minute add on in a field that certainly wouldn't be the expertise of a small indie developer (no-one goes into game development for their love of CRUD apps, after all). They needed a database so they used the most popular, in a way that probably seemed the most appropriate for their application.
Ah, but remember, they've "done this stuff for a while now". Either they know and don't care, or they don't know and they're claiming they do. There's no good way out of this, they're messing up potentially catastrophically regardless of the truth.
Not knowing is one thing.
Not knowing, being informed about it, and arrogantly blowing off that information is inexcusable, unprofessional, and deserving of all that scorn.
It would have been very easy to say, "Thanks for bringing this up, we will look into it", instead of being condescending and ending up looking like an ignorant jackass.
Not knowing, being informed about it, and arrogantly blowing off that information is inexcusable, unprofessional, and deserving of all that scorn.
It would have been very easy to say, "Thanks for bringing this up, we will look into it", instead of being condescending and ending up looking like an ignorant jackass.
I can't imagine anyone specializing to such an extreme. If you want to make multiplayer games, for example, you have to know this stuff.
Every competent programmer should be familiar with basic security principles. It's then your responsibility to educate yourself about how to apply those principles in a given situation.
Every competent programmer should be familiar with basic security principles. It's then your responsibility to educate yourself about how to apply those principles in a given situation.
No, you don't. I work for one of the top social gaming companies around right now and programmers do specialize to a ridiculous degree. The programmers who write code for the actual game are rarely web developers. It's simply a different domain.
I can confidently say this because I'm a web developer in a studio of game developers and most of them don't even know how to run mysql locally. They aren't stupid, they could, if they spent the time to learn it. But they are much more interested in improving the efficiency of their A* pathing algorithm.
I can confidently say this because I'm a web developer in a studio of game developers and most of them don't even know how to run mysql locally. They aren't stupid, they could, if they spent the time to learn it. But they are much more interested in improving the efficiency of their A* pathing algorithm.
Well, it's fair to say that programmers specialise, but presumably you're only working on the website, and the game developers are only working on the game. TillE was right - if you are going to implement this stuff, you should know what you're doing, or at least seek advice from people who do.
Regardless, the super meat boy developer made a pretty basic mistake, which you could perhaps defend with your argument, but he then refused to engage with someone who was reporting a vulnerability and trying to help. To me, that's pretty astounding.
Regardless, the super meat boy developer made a pretty basic mistake, which you could perhaps defend with your argument, but he then refused to engage with someone who was reporting a vulnerability and trying to help. To me, that's pretty astounding.
Game development especially has a lot of well meaning customers who haven't got a clue what they're talking about (a lot of kids). Whilst he probably should have listened, it's understandable why he may have dismissed a random on twitter.
The guy took a stack trace of a segfault. If a guy comes up to me, tells me I have a glaring security flaw, and shows me a stack trace of my own code to prove it, I'd be an idiot /not/ to give him at least a few minutes of my time, no matter what community he comes from.
This is true, whilst you might not expect them build a beautifully layered J2EE or RoR solution with full transactional integrity, a few hours with some basic PHP tutorials would not have gone amiss.
This is especially spooky since MySQL contains a few scary features like System() not to mention that anybody could connect and write a script to do massive crossjoins (as indicated in the original thread) I'm sure any decent game developer would understand the implications of O(n!) but they were probably blissfully unaware that these features even exist.
I doubt that Notch would have made such a schoolboy error :)
This is especially spooky since MySQL contains a few scary features like System() not to mention that anybody could connect and write a script to do massive crossjoins (as indicated in the original thread) I'm sure any decent game developer would understand the implications of O(n!) but they were probably blissfully unaware that these features even exist.
I doubt that Notch would have made such a schoolboy error :)
> I doubt that Notch would have made such a schoolboy error :)
He made an equivalent one: he wrote his own database[1], in the age of SQLite being dumb-as-dirt-simple to use and MySQL almost a no-brainer.
[1] - http://notch.tumblr.com/post/1166302589/this-is-what-im-doin...
He made an equivalent one: he wrote his own database[1], in the age of SQLite being dumb-as-dirt-simple to use and MySQL almost a no-brainer.
[1] - http://notch.tumblr.com/post/1166302589/this-is-what-im-doin...
Well, he made the error and immediately corrected it when it became an issue.
No, he corrected it after it had been an issue for some time and was negatively impacting his customers' enjoyment of what they had purchased.
Throughout Minecraft's development, "barely good enough, and sometimes not even then" has been the externally visible modus operandi. That so many players put up with customer abuse is unfortunate, not least because others will consider Notch's slipshod development practices and infantile product management goals to which one might aspire.
Throughout Minecraft's development, "barely good enough, and sometimes not even then" has been the externally visible modus operandi. That so many players put up with customer abuse is unfortunate, not least because others will consider Notch's slipshod development practices and infantile product management goals to which one might aspire.
This is just so WTF on so many levels, you can't help but wonder how they even managed to produce such a good game after all.
When I started working in Industry™/The Real World™ I learned a lot of important lessons in How Things Are Actually Done™. One of them was how much software was just held together with duct-tape and string.
So yes, you can produce a good game and think that this sort of DB access is a good idea.
When I started working in Industry™/The Real World™ I learned a lot of important lessons in How Things Are Actually Done™. One of them was how much software was just held together with duct-tape and string.
So yes, you can produce a good game and think that this sort of DB access is a good idea.
You must be popular and attractive because these kind of mistakes get normal people fired. This thread makes my stomach hurt thinking I might work with some of you.
Oh I'm not saying it's ethical or the right way to do things, just that it's common.
For example, there are loads of companies with very poor backup policies in place, or with all their eggs in one basket. There are loads of companies without any real 24/7 'on call' system.
For every person who is fired for doing some quick hack like this, there is someone who will be fired if they don't "Get it working today" (i.e. they'll get fired if they don't do the quick hack)
For example, there are loads of companies with very poor backup policies in place, or with all their eggs in one basket. There are loads of companies without any real 24/7 'on call' system.
For every person who is fired for doing some quick hack like this, there is someone who will be fired if they don't "Get it working today" (i.e. they'll get fired if they don't do the quick hack)
I'm about halfway through my first android app, and all the research I did said the same thing; never, ever connect directly to your MySQL database. I just wrote my own PHP script to deliver the results via JSON, it took me a few days to figure out but it wasn't that difficult.
If you read Edmund McMillen's formspring page, you'll see that there are several disconnects from a development perspective about super meat boy. Such as them not being able to reproduce builds from source.
http://www.formspring.me/EdmundM
Conversely, I love me some Binding of Issac
http://www.formspring.me/EdmundM
Conversely, I love me some Binding of Issac
Exactly. There's no need to equivocate, just call this what it is -- lazy and incompetent.
The thing is, we would never have heard about this if the developer wasn't a pompous ass and tried to claim that the way he is doing it is "fine".
Maybe you could argue that it was the right approach at the time. But deciding that the data doesn't matter anymore is not cool, you have to take care of your paying customers or else you lose credibility.
Unfortunately this doesn't surprise me too much. I'm a Mac user and eagerly awaited the Mac release, only to find it had terrible performance and game breakage bugs. I emailed the developers and received two replies.
Tommy, the developer quoted here, started his email implying my complaint was faked, then saying they couldn't reproduce the issue of the game crashing every time you entered a warp zone, although they said they were working on it. His response made me quite a bit madder.
Edmund, the other developer, replied separately. He was apologetic and mentioned that the person they had originally paid to port the game fell through so it had to be done very fast. This was the kind of email I was expecting. It wasn't confrontational at all.
They later released a patch that fixed the crashing issues, but performance is still a major problem. The Windows system requirements are listed as a 1.4 GHz P4 with 768 MB of RAM. On the official blog, they list a dual core 2.6 GHz machine with 2 GB of RAM as the minimum requirements for the Mac. The game is officially supposed to support the 360 gamepad, but a note was left on the Steam forum that despite the promise it's not possible to support it correctly on the Mac, so the problem won't be fixed.
I thought the binding of Isaac looked interesting, and it has been getting good reviews. But after my experience I'm not going to play it. Super Meat Boy was a lot of fun (even though it didn't perform well on my Mac), but after my experience seeing the response to this issue doesn't surprise me.
Tommy, the developer quoted here, started his email implying my complaint was faked, then saying they couldn't reproduce the issue of the game crashing every time you entered a warp zone, although they said they were working on it. His response made me quite a bit madder.
Edmund, the other developer, replied separately. He was apologetic and mentioned that the person they had originally paid to port the game fell through so it had to be done very fast. This was the kind of email I was expecting. It wasn't confrontational at all.
They later released a patch that fixed the crashing issues, but performance is still a major problem. The Windows system requirements are listed as a 1.4 GHz P4 with 768 MB of RAM. On the official blog, they list a dual core 2.6 GHz machine with 2 GB of RAM as the minimum requirements for the Mac. The game is officially supposed to support the 360 gamepad, but a note was left on the Steam forum that despite the promise it's not possible to support it correctly on the Mac, so the problem won't be fixed.
I thought the binding of Isaac looked interesting, and it has been getting good reviews. But after my experience I'm not going to play it. Super Meat Boy was a lot of fun (even though it didn't perform well on my Mac), but after my experience seeing the response to this issue doesn't surprise me.
Hmm, Super Meat Boy seems to be totally fine on my mac. (2010 Air 11").
However, Binding of Isaac is pretty lagtastic. (But, quite possibly the most fun I've had with a game this year.)
However, Binding of Isaac is pretty lagtastic. (But, quite possibly the most fun I've had with a game this year.)
I considered buying it on XBLA a few times, but I wanted to support a developer for supporting the Mac, and I didn't want to have to boot my XBox to play it.
The individual levels are generally quite fluid, unless you die an awful lot of times. But the map screens and some of the bosses are slower (ESPECIALLY the one in World 4/Hell). I wonder if it's the extra visual effects (like the fire in Hell).
I wish I had bought it on XBLA. Microsoft's quality control meant it would have been a much better experience. I was actually disappointed with Steam, which is the first time that happened. I guess I assumed they ran strong QC, which they must not.
PS: MacBook Pro, early 2010, 2.53 GHz, 8 GB. Graphics card (integrated vs nVidia) never seemed to make a difference.
The individual levels are generally quite fluid, unless you die an awful lot of times. But the map screens and some of the bosses are slower (ESPECIALLY the one in World 4/Hell). I wonder if it's the extra visual effects (like the fire in Hell).
I wish I had bought it on XBLA. Microsoft's quality control meant it would have been a much better experience. I was actually disappointed with Steam, which is the first time that happened. I guess I assumed they ran strong QC, which they must not.
PS: MacBook Pro, early 2010, 2.53 GHz, 8 GB. Graphics card (integrated vs nVidia) never seemed to make a difference.
BoI is Edmund's game, Tommy wasn't on it.
The idea of doing an UPDATE on the custom levels table every time someone dies in order to increment the deaths counter terrifies the database administrator in me. That'll scale REAL well. Yikes.
Asking the database administrator in you, what's the most efficient way to do it? Queue and push updates every 10 minutes?
The easiest way to have a counter that is both correct and fast is to split things up into multiple separate counters and then count them. So for a simple example: User ID mod 50 = 7 so lock row, 7 increment row, 7 unlock row 7. Want the total? sum (all rows).
You can also get fancy with lock row 1, lock total, update total = total + value of row 1, unlock total, set row 1 to 0, unlock row 1. Repeat row 2...50. The advantage being even lower latency access to a number that is withing X seconds of being correct.
PS: Both of the above can be split across multiple cores / machines. But, for a less accurate but still reasonable have the client a = (rand (0,100)), b = rand (0, 100 + a), if b == 7 update death count by (a + 100). This only increments the counter 1 time in ~150 times while avoiding counters that are always multiples of some number. Just be careful of off by one errors.
You can also get fancy with lock row 1, lock total, update total = total + value of row 1, unlock total, set row 1 to 0, unlock row 1. Repeat row 2...50. The advantage being even lower latency access to a number that is withing X seconds of being correct.
PS: Both of the above can be split across multiple cores / machines. But, for a less accurate but still reasonable have the client a = (rand (0,100)), b = rand (0, 100 + a), if b == 7 update death count by (a + 100). This only increments the counter 1 time in ~150 times while avoiding counters that are always multiples of some number. Just be careful of off by one errors.
We do millions of operations like that a minute by queuing, aggregating and then committing. SQL Server's MERGE is particularly useful for it, although on our MongoDB stuff (and ironically, for player created levels) we do $incs.
I know next to nothing about this kind of stuff. If I wanted to create a stats + user generated level database system akin to Super Meat Boy (and I do) do you have any recommended resources to read?
It depends on how deep you want to go yourself. At its easiest you could just drop Playtomic [1] in, we have support for most gaming platforms.... obviously I'm quite biased towards this option. :)
If you want something more flexible and you're doing mobile you can check out Parse [2], they're a custom database with a REST, iOS and Android APIs. If you're using Flash, HTML5 or Unity3d we have a bridge that lets you use Parse through our own APIs.
If you want to get right down to the guts of it I would get a simple Heroku [3], PHPFog [4] or AppHarbor [5] account depending on what languages you're most comfortable with or learning and set up a MongoDB database over at MongoHQ [6], MongoDB lends itself very well to user created levels in my experience.
Basically you need:
1) Scripts to save, rate, count plays and list levels. You want to either authenticate the user, or more simply just obfuscate the data you're transmitting to make tampering harder
2) Some kind of logging or queueing system where you will store the plays
3) Something that will go through your logs or queues and perform the $inc operations on your levels in bulk batches rather than doing it all individually
4) Indexes on your database that match your listing requirements
[1] http://playtomic.com/ [2] http://parse.com/ [3] http://heroku.com/ [4] http://phpfog.com/ [5] http://appharbor.com/ [6] http://mongohq.com/
If you want something more flexible and you're doing mobile you can check out Parse [2], they're a custom database with a REST, iOS and Android APIs. If you're using Flash, HTML5 or Unity3d we have a bridge that lets you use Parse through our own APIs.
If you want to get right down to the guts of it I would get a simple Heroku [3], PHPFog [4] or AppHarbor [5] account depending on what languages you're most comfortable with or learning and set up a MongoDB database over at MongoHQ [6], MongoDB lends itself very well to user created levels in my experience.
Basically you need:
1) Scripts to save, rate, count plays and list levels. You want to either authenticate the user, or more simply just obfuscate the data you're transmitting to make tampering harder
2) Some kind of logging or queueing system where you will store the plays
3) Something that will go through your logs or queues and perform the $inc operations on your levels in bulk batches rather than doing it all individually
4) Indexes on your database that match your listing requirements
[1] http://playtomic.com/ [2] http://parse.com/ [3] http://heroku.com/ [4] http://phpfog.com/ [5] http://appharbor.com/ [6] http://mongohq.com/
Great response, lots of good information, thank you!
I'd strongly suggest looking in to Redis - it's fantastic for stats collection and very easy to work with.
The sorted sets make leaderboards pretty trivial to implement, too.
I've got one set up in MySQL and I'm pretty scared of how it'll cope if we get a surge in usage, and that's with Memcache sat in front of it.
I've got one set up in MySQL and I'm pretty scared of how it'll cope if we get a surge in usage, and that's with Memcache sat in front of it.
On our platform the only problem has been having to perform count operations, the way we do it scores can be listed in unpredictable fashions and MongoDB is inherently bad at counts.
Aside from that the read:write ratio massively favors reading for us and caching makes that a negligible operation most of the time, and (unless like in our case you're providing leaderboards for games you don't control) MySQL and Memcache should carry you fine.
Aside from that the read:write ratio massively favors reading for us and caching makes that a negligible operation most of the time, and (unless like in our case you're providing leaderboards for games you don't control) MySQL and Memcache should carry you fine.
Yes, but redis is just so nice for certain applications that can be a pain in a RDBMS. Not that it can't be done, but the simplicity and performance of redis atomic counter increment/decrement is often enough for magical vertical scaling sauce. Redis is chock full of these little use cases, even when you're just using it as a "cache".
Off the top of my head, one way to do this without rearchitecting everything would be to insert a new record into a very simple in-memory temp table. Every minute, update the appropriate record in the real table by count(*) of the temp table and clear the temp table. As it's being used for a non-critical counter, data loss in the event of a MySQL restart isn't a concern.
That should be sufficient for something of this scale and only requires a minor update to the client SQL, ensuring the temp table is created when MySQL starts, and executing one stored procedure on a schedule.
That should be sufficient for something of this scale and only requires a minor update to the client SQL, ensuring the temp table is created when MySQL starts, and executing one stored procedure on a schedule.
If I had to use the database, I'd probably aggressively shard the data so that it's spread across multiple databases/tables. That way the load of counter updates could be spread across machines. I'd probably also store the counters in a separate table from the rest of the levels, for the same reason, but also to ensure that the rest of the information about a level isn't locked during every increment.
Of course, this is given that IIRC, MySQL uses row locking and used to even use table locks at times during writes. The last thing you want is for your spew of counter updates to lock out other, more important operations (like deleting a level). MySQL locking behavior might be better now.
The real 'right' solution to a high-traffic counter like a death count is to store the realtime counter(s) in something like memcached: High throughput, low overhead, and built in support for operations like increment.
As counter traffic increases, you could partition the counter itself (and approximate the actual value by taking partition_value * num_partitions) or only record a percentage of the time (say, +4 the counter on 25% of deaths). Both solutions reduce load and could produce 'good enough' numbers as long as you don't have wildly different behavior on each partition.
Of course, this is given that IIRC, MySQL uses row locking and used to even use table locks at times during writes. The last thing you want is for your spew of counter updates to lock out other, more important operations (like deleting a level). MySQL locking behavior might be better now.
The real 'right' solution to a high-traffic counter like a death count is to store the realtime counter(s) in something like memcached: High throughput, low overhead, and built in support for operations like increment.
As counter traffic increases, you could partition the counter itself (and approximate the actual value by taking partition_value * num_partitions) or only record a percentage of the time (say, +4 the counter on 25% of deaths). Both solutions reduce load and could produce 'good enough' numbers as long as you don't have wildly different behavior on each partition.
MySQL locking behavior depends on the table engine/type you use. The old MyISAM format is table-level locking. InnoDB is row-level and supports all the "big boy" RDBMS features you'd expect to be there (like foreign keys...).
Also ideal is to have the client not update the number of deaths on each attempt but only either on completion of the level or at a timed interval. No need for that date to be real-time, so it can be aggregated.
Also ideal is to have the client not update the number of deaths on each attempt but only either on completion of the level or at a timed interval. No need for that date to be real-time, so it can be aggregated.
One is: don't use the database for this. Lock/read/write/unlock is very slow when you need a round-trip over the Internet for each phase. (Remember, with optimistic locking, many transactions are in the Lock/read state as the same time. When someone writes the new death count, all transactions in the lock/read state are rolled back and started from the beginning, as the data read is now invalid. And that's assuming MySQL is not in read uncommitted mode; then you just throw away data randomly.)
The correct way to count something in a relational database is to add a relation (row) for each event, and then COUNT them all. How do you count log message in log files; by having syslogd update a count file? Nope. wc -l.
(And no, I'm not a fan of auto-incrementing integer primary keys, because read/calculate/write is slow. Databases have ACID so that read/calculate/rewrite is safe. But that doesn't mean that type of workload is going to run fast. Of course, autoincrementing primary keys is specially optimized by most databases, so you never notice in this particular case.)
The correct way to count something in a relational database is to add a relation (row) for each event, and then COUNT them all. How do you count log message in log files; by having syslogd update a count file? Nope. wc -l.
(And no, I'm not a fan of auto-incrementing integer primary keys, because read/calculate/write is slow. Databases have ACID so that read/calculate/rewrite is safe. But that doesn't mean that type of workload is going to run fast. Of course, autoincrementing primary keys is specially optimized by most databases, so you never notice in this particular case.)
[deleted]
How many deaths per second does it take (INSERTs or UPDATEs per second) to bring your database to its knees? Maybe you are using the wrong database?
Hi, I registered because I had read this story earlier today and decided to contact Team Meat's developer and ask him a question. His response prompted me to post this here. My question is of a political nature, but you can ignore that part. The part that struck me as odd, and the part that matters for this conversation, was where he claimed nothing happened.
https://lh5.googleusercontent.com/-kc0f6ZQZebY/TvWDhFSHYEI/A...
Note that I sent him the tweet at exactly midnight my time. A short time later, a thread on the Steam forums was updated with:
https://lh5.googleusercontent.com/-itY306KgvLk/TvWGR_cQkiI/A...
To give that a little context, once the details of this vulnerability were in the wild the shenanigans everyone ran on their database had affected everybody's ability to play the game. It would crash upon launch.
Anyway, he responded ~two hours later, just after that post on the Steam forums noted that the game was playable again.
Does "nothing happened" seem accurate here? And for anyone that has the skills to access that database, is it still accessible and...modifyable?
https://lh5.googleusercontent.com/-kc0f6ZQZebY/TvWDhFSHYEI/A...
Note that I sent him the tweet at exactly midnight my time. A short time later, a thread on the Steam forums was updated with:
https://lh5.googleusercontent.com/-itY306KgvLk/TvWGR_cQkiI/A...
To give that a little context, once the details of this vulnerability were in the wild the shenanigans everyone ran on their database had affected everybody's ability to play the game. It would crash upon launch.
Anyway, he responded ~two hours later, just after that post on the Steam forums noted that the game was playable again.
Does "nothing happened" seem accurate here? And for anyone that has the skills to access that database, is it still accessible and...modifyable?
"My question is of a political nature, but you can ignore that part."
No, your question was of a rude nature, which is pretty hard to ignore. It's not surprising that he responded curtly.
No, your question was of a rude nature, which is pretty hard to ignore. It's not surprising that he responded curtly.
I was trying not to focus on that aspect of the conversation, but this is what my comment was based on:
https://twitter.com/#!/SuperMeatBoy/status/14761253025520435...
In my opinion, this isn't the sort of behavior that should be advocated. If people pirate the software produced by these companies that support SOPA, then that just validates SOPA. Additionally, he doesn't exactly give his games away for free so it's a strange stance to take in my eyes.
Anyway, my thoughts and feelings on copyright infringement weren't relevant discussion for this thread. I instead chose to focus on him acting as if nothing had happened, when the rest of the Internet proved otherwise.
https://twitter.com/#!/SuperMeatBoy/status/14761253025520435...
In my opinion, this isn't the sort of behavior that should be advocated. If people pirate the software produced by these companies that support SOPA, then that just validates SOPA. Additionally, he doesn't exactly give his games away for free so it's a strange stance to take in my eyes.
Anyway, my thoughts and feelings on copyright infringement weren't relevant discussion for this thread. I instead chose to focus on him acting as if nothing had happened, when the rest of the Internet proved otherwise.
So what would be the solution? He used a password protected database connection and put the password compiled in binary. If I was doing it I would have probably done the same. How else can it be done? Use web service? That would still look "open" to someone digging inside the compiled binary and getting the keys.
Fair question. A web service would indeed be a better solution. With a web service, you have a server-side application layer, and all database reading and writing is done by that layer. Sure, you might be able to authenticate and send bogus info to the web service. Even that can be made very difficult, e.g. by cryptographically signing requests or encrypting the data on the wire.
So if you do that, worst case scenario is someone reverse-engineers the protocol, including whatever cryptography you're using. And that person then sends phony data of the sort the game would really send, e.g. messages saying "I completed this level" or "I scored X points." Which, in the big scheme of things, isn't a huge security breach.
The difference between that and having an open MySQL server is that the open MySQL server allows you to read and write anything stored in the database. That's much worse than being able to spoof normal data the game would be sending anyway. In this situation, the worst case scenario would be total corruption of the database, as opposed to the player falsely accruing points or something like that.
So if you do that, worst case scenario is someone reverse-engineers the protocol, including whatever cryptography you're using. And that person then sends phony data of the sort the game would really send, e.g. messages saying "I completed this level" or "I scored X points." Which, in the big scheme of things, isn't a huge security breach.
The difference between that and having an open MySQL server is that the open MySQL server allows you to read and write anything stored in the database. That's much worse than being able to spoof normal data the game would be sending anyway. In this situation, the worst case scenario would be total corruption of the database, as opposed to the player falsely accruing points or something like that.
There is a misunderstanding. Someone connecting to a "open" MySQL server will only be able to run those type of queries (select,update or delete) that he is explicitly permitted to run and only on those database and tables where the admin has granted him access.
Not too different from a web service.
Also no amount of encryption will secure the system because it's not about man in the middle attack. It's about decompiling the binary. The attacker has full access to all your encryption functions, routines and everything you put there. See it as like he has all your client side source code.
The only benefit I see in using a web service is restricting one user from accessing other users data in the same table. It's about only 20% improvement in security as I see it. And that's only because MySQL doesn't support row level authorization.
Other issues that are not any more secure through a web service:
1. Crash the MySQL server by running rouge queries. On the other side an attacker can DOS a web service too.
2. One can run Insert/Update/Delete queries on MySQL. Well web services has to run insert/update/delete queries too based on user input --- where the attacker modifies the source code that generates the input.
3. One can validate input using web service. One can validate input using MySQL trigger/SP too.
Just trying to show that I don't see much improvement in security using a web service when the attacker is controlling your client side source code. Basically MySQL is also a service that you can control, but it doesn't run on port 80.
Not too different from a web service.
Also no amount of encryption will secure the system because it's not about man in the middle attack. It's about decompiling the binary. The attacker has full access to all your encryption functions, routines and everything you put there. See it as like he has all your client side source code.
The only benefit I see in using a web service is restricting one user from accessing other users data in the same table. It's about only 20% improvement in security as I see it. And that's only because MySQL doesn't support row level authorization.
Other issues that are not any more secure through a web service:
1. Crash the MySQL server by running rouge queries. On the other side an attacker can DOS a web service too.
2. One can run Insert/Update/Delete queries on MySQL. Well web services has to run insert/update/delete queries too based on user input --- where the attacker modifies the source code that generates the input.
3. One can validate input using web service. One can validate input using MySQL trigger/SP too.
Just trying to show that I don't see much improvement in security using a web service when the attacker is controlling your client side source code. Basically MySQL is also a service that you can control, but it doesn't run on port 80.
"Someone connecting to a "open" MySQL server will only be able to run those type of queries (select,update or delete) that he is explicitly permitted to run"
... bar any security vulnerability in your sql engine, which is bound to exist -- relational databases are historically less hardened than web servers, because they're less subject to abuse (they mostly run in safe intranets, not on the big bad Internet).
An application layer inbetween malicious users and your databases will provide proper input validation and security in addition to flaky db security, and (in most cases) it will guarantee that your database will remain intact should malicious users crash your front-end code -- which, when properly secured, will also have minimal rights on the schema, hence insuring that attackers don't get more rights than absolutely necessary for the app to run. At the very minimum, it will provide an additional barrier that malicious users will have to overcome before getting at your data goodies, giving you additional time to get on top of things.
The internet is a permanent war zone. Running an open relational database on the internet is like having an HQ not surrounded by tanks, because "after all, nobody can get through our glass doors unless they have the right papers".
... bar any security vulnerability in your sql engine, which is bound to exist -- relational databases are historically less hardened than web servers, because they're less subject to abuse (they mostly run in safe intranets, not on the big bad Internet).
An application layer inbetween malicious users and your databases will provide proper input validation and security in addition to flaky db security, and (in most cases) it will guarantee that your database will remain intact should malicious users crash your front-end code -- which, when properly secured, will also have minimal rights on the schema, hence insuring that attackers don't get more rights than absolutely necessary for the app to run. At the very minimum, it will provide an additional barrier that malicious users will have to overcome before getting at your data goodies, giving you additional time to get on top of things.
The internet is a permanent war zone. Running an open relational database on the internet is like having an HQ not surrounded by tanks, because "after all, nobody can get through our glass doors unless they have the right papers".
Someone connecting to a "open" MySQL server will only be able to run those type of queries (select,update or delete) that he is explicitly permitted to run and only on those database and tables where the admin has granted him access.
Yes that is true in theory. However in practice, the kind of person who thinks direct access to the database is a good idea is probably not the kind of person who knows about/knows how to restrict the database access to only certain queries/tables.
Yes that is true in theory. However in practice, the kind of person who thinks direct access to the database is a good idea is probably not the kind of person who knows about/knows how to restrict the database access to only certain queries/tables.
Generally, an app would need select, update, and delete on most or all tables. Therefore, simple MySQL permissions would not be sufficient to prevent an attacker from corrupting huge amounts of data belonging to other people's accounts.
"Well web services has to run insert/update/delete queries too based on user input --- where the attacker modifies the source code that generates the input." -- If you sanitize your inputs on the server side, no attacker will ever be able to run arbitrary SQL. This is crucial.
"One can validate input using MySQL trigger/SP too" -- I certainly wouldn't look forward to writing a trigger that, for example, prevents user A from editing records belonging to user B. This is so much easier to do in the application layer.
"Basically MySQL is also a service that you can control, but it doesn't run on port 80." -- You should use the right tool for the job. Sure, you could, for some applications, make an open MySQL server secure. But this is picking the wrong tool for the job. An application layer is far, far better suited to this task than a MySQL server all by itself. This is because your application layer can be written in the language and framework of your choice, giving you much richer ways to express business logic than MySQL can offer.
"Well web services has to run insert/update/delete queries too based on user input --- where the attacker modifies the source code that generates the input." -- If you sanitize your inputs on the server side, no attacker will ever be able to run arbitrary SQL. This is crucial.
"One can validate input using MySQL trigger/SP too" -- I certainly wouldn't look forward to writing a trigger that, for example, prevents user A from editing records belonging to user B. This is so much easier to do in the application layer.
"Basically MySQL is also a service that you can control, but it doesn't run on port 80." -- You should use the right tool for the job. Sure, you could, for some applications, make an open MySQL server secure. But this is picking the wrong tool for the job. An application layer is far, far better suited to this task than a MySQL server all by itself. This is because your application layer can be written in the language and framework of your choice, giving you much richer ways to express business logic than MySQL can offer.
You would design a web service to only allow updates to rows created by that same user ID. That's a critical difference with setting MySQL permissions which would allow you to update an entire table, potentially destroying the scores and levels of others.
With a web service, majority of the potential abuses would be related to being able to manipulate your own scores and records in the db. That isn't that big of a deal in this use case since we are dealing with games.
Most simply the web service can require a 20 char unique key every time it is taking a request from a client. You can find yours using a sniffer. But it'll be hard to guess others'. This is the most basic implementation.
Most simply the web service can require a 20 char unique key every time it is taking a request from a client. You can find yours using a sniffer. But it'll be hard to guess others'. This is the most basic implementation.
Use a web service, and make sure that service only allows precisely the requests you want to allow, rather than arbitrary SQL.
Perhaps generate individual keys using some secure random process. Then you can check your logs now and again and if a few particular keys are submitting obviously bogus data you can just remove them from your records and block them in future.
As has been mentioned elsewhere there are other risks by the fact that your allowing people to enter data into your system using an actual language (SQL) as opposed to a few POST vars (assuming your webservice sanitizes input properly).
There are just many more possible attack vectors with mysql , one of which would be sending massive crossjoins or similar to the DB to crash it.
As has been mentioned elsewhere there are other risks by the fact that your allowing people to enter data into your system using an actual language (SQL) as opposed to a few POST vars (assuming your webservice sanitizes input properly).
There are just many more possible attack vectors with mysql , one of which would be sending massive crossjoins or similar to the DB to crash it.
I think your question is very valid and I think the answer is that the "solution" depends on what your goals are. The author may even consider it to be perfectly fine if he has to reset these statistics regularly, or even move to a more protected system in a later version if it gets hacked.
His main goal was probably something like "put a system in to send me some analytic information, but spend as little time as possible on it and make no concern for security or performance."
Especially considering the developer may have had decent knowledge in some things like mysql c connectors, but not in php or something else he could use for the web service, his approach may have been the best approach to take. There's no way we can judge. I'm pretty sure that was your point. It seems like the mysql approach could have even come from an Agile methodology where the "story" makes no concern for security or performance.
His main goal was probably something like "put a system in to send me some analytic information, but spend as little time as possible on it and make no concern for security or performance."
Especially considering the developer may have had decent knowledge in some things like mysql c connectors, but not in php or something else he could use for the web service, his approach may have been the best approach to take. There's no way we can judge. I'm pretty sure that was your point. It seems like the mysql approach could have even come from an Agile methodology where the "story" makes no concern for security or performance.
There's no real reason to have the end users connecting to the database directly. Any advantages of this approach are far outweighed by the disadvantages. Having a server process that handles input from the game and then write the results to the DB is far simpler and more secure in the end.
a "story" that doesn't consider security over the internet is doomed to get your rooted sooner or later.
I can't believe how many people here think this is okay. YES, a web service! Please never apply to a company that I am working for!
Maybe the new FizzBuzz should be to show prospective devs some DailyWTF articles and see whether they laugh, cry, or think it's a great idea....
Well they say when all you have is a hammer... Actually, a web service doesn't seem like a bad idea to me--it's easy to implement and would allow finer grained permissions than direct database access. It's also easy to move to SSL. What would you do instead?
The less one knows about a system the more layers of firewall he will put up to protect it.
People get astonished when I insert a virus infected USB disk into my Windows machine and use Explorer to safely copy files from it. And when they ask what anti-virus I use, I say "None, never used any anti-virus in my life. I reversed engineered a lot of viruses and I know how they work."
> Please never apply to a company that I am working for!
Sure. Thanks.
People get astonished when I insert a virus infected USB disk into my Windows machine and use Explorer to safely copy files from it. And when they ask what anti-virus I use, I say "None, never used any anti-virus in my life. I reversed engineered a lot of viruses and I know how they work."
> Please never apply to a company that I am working for!
Sure. Thanks.
I'd honestly like to know how you can "safely copy files" with Explorer.
You know a lot of viruses, you don't know all the viruses. How can you discount the possibility that, one day, the USB interface itself will be subverted to spread viruses ?
You know a lot of viruses, you don't know all the viruses. How can you discount the possibility that, one day, the USB interface itself will be subverted to spread viruses ?
> the USB interface itself will be subverted to spread viruses ?
It can. But then I would know about it as soon the AV companies know. And I can take the precaution accordingly. If that is a zero day [remote] exploit, then I am toast, with or without anti virus.
The point is: anti-viruses would probably make me 10% more secure over what I already am. Therefore it's not worth it when one considers its cons.
It can. But then I would know about it as soon the AV companies know. And I can take the precaution accordingly. If that is a zero day [remote] exploit, then I am toast, with or without anti virus.
The point is: anti-viruses would probably make me 10% more secure over what I already am. Therefore it's not worth it when one considers its cons.
If you really can enjoy the same level of semi-instantaneous knowledge of "virusdom" as AV companies, then you're the 0.00001% of the population. Your solution simply doesn't scale in the real world.
In fact this was one of the propagation methods of Stuxnet. It exploited a vulnerability in MS's .lnk file to achieve code execution when removable USB storage was inserted. See p. 29 of http://www.symantec.com/content/en/us/enterprise/media/secur...
I have a command file on my desktop, double clicking on which deletes all .pif .lnk .inf .com and -shr attributed files in USB drive [plus other things].
The game ships a libmysql library. Unless they got a commercial license for it, that's a GPL violation in the wild.
It isn't a GPL violation because the game is just interfacing with a master MySQL database, not using one to store local content.
libmysql is GPL licensed as well.
MySQL's own licensing page states that a license is needed for software that has MySQL distributed as a part of it.
Even if they DID need a license you are out of line to suggest they are automatically infringing and didn't just purchase the commercial license.
Even if they DID need a license you are out of line to suggest they are automatically infringing and didn't just purchase the commercial license.
> Even if they DID need a license you are out of line to suggest they are automatically infringing and didn't just purchase the commercial license.
Well. You do need a license. No question about that. I did not say they are infringing the license, I was saying that unless they bought a commercial license for it they would be. Maybe bad wording on my part but when I read that article the security implications did not nearly strike me as much as the fact that the game has to ship libmysql.
I find that much more surprising than the fact that it uses a world writable MySQL database for the editor support.
Well. You do need a license. No question about that. I did not say they are infringing the license, I was saying that unless they bought a commercial license for it they would be. Maybe bad wording on my part but when I read that article the security implications did not nearly strike me as much as the fact that the game has to ship libmysql.
I find that much more surprising than the fact that it uses a world writable MySQL database for the editor support.
http://www.formspring.me/EdmundM/q/274876035885957785
I find it disturbing that people would go through and even attempt deleting levels and altering things en masse. It's okay to point out the bug, and maybe change one thing to display this fact...but the people changing everything? It's a selfish thing to do this to thousands of creative works (the user-created levels), in the motivation of just showing one developer something he did wrong. Thankfully, there were backups.
Parts of this thread, the somewhat arrogant reaction of the Team Meat programmer, and the motives and subsequent actions of some of the people modifying the database, made me a little sick. If anything it's a showing (on both sides) of the lack of maturity of some programmers.
Look, I know that this could be justified in saying "well, someone would have done it sooner or later." In that case, I suppose whether or not this was okay is left up to whether Tommy (the programmer - Edmund did design) would have patched it. And with his defensive response, I'm sure he knew and was going to do so, but probably didn't want to admit it (from interviews he seems to have bit of a bitter ego)
I find it disturbing that people would go through and even attempt deleting levels and altering things en masse. It's okay to point out the bug, and maybe change one thing to display this fact...but the people changing everything? It's a selfish thing to do this to thousands of creative works (the user-created levels), in the motivation of just showing one developer something he did wrong. Thankfully, there were backups.
Parts of this thread, the somewhat arrogant reaction of the Team Meat programmer, and the motives and subsequent actions of some of the people modifying the database, made me a little sick. If anything it's a showing (on both sides) of the lack of maturity of some programmers.
Look, I know that this could be justified in saying "well, someone would have done it sooner or later." In that case, I suppose whether or not this was okay is left up to whether Tommy (the programmer - Edmund did design) would have patched it. And with his defensive response, I'm sure he knew and was going to do so, but probably didn't want to admit it (from interviews he seems to have bit of a bitter ego)
SELECT * FROM smb_editor_author CROSS JOIN smb_editor_leveldata
You should try locking the tables first. Maybe other people connecting is slowing it down, so a full table lock would be a good idea.
Remember to keep the lock alive after the query finishes, just in case you need to query it again...
Remember to keep the lock alive after the query finishes, just in case you need to query it again...
Maybe he built a way to detect mischievous updates or he's baiting to sue.
They don't really care, could've even been intentional: http://img820.imageshack.us/img820/1641/itsfinetrustme.png
Well, this seems to be fixed - the user posted in the image now only has SELECT privileges to the database. Unless my mysql is reeeally rusty and I misunderstood the output...
From what I read, the database isn't "wide open". It's read-only and there's nothing juicy in it.
Also, a bit of NSFW stuff going on here if you dig too far.
Edit: It's not read-only, INSERT/UPDATE works.
Also, a bit of NSFW stuff going on here if you dig too far.
Edit: It's not read-only, INSERT/UPDATE works.
It's not read-only though. He mentions that INSERT and UPDATE work.
http://forums.somethingawful.com/showthread.php?noseen=0&...
This is a really embarrassing mistake on their part.
http://forums.somethingawful.com/showthread.php?noseen=0&...
This is a really embarrassing mistake on their part.
Ah, missed that.
Sorry, if you read the page linked you'd see that that is not the case. It is open to SELECT, UPDATE, and INSERT. if it were SELECT only that would be read-only.
As it is, that's just stupid as any user can wantonly edit anything. I could trivially edit every level's author to be myself or do intensive operations which result in a DOS.
The only smart way to give clients access to a database is through some sort of frontend entirely under your control which prevents them from having the user/pass and sanitizes the queries.
Edit: Whoops, while I typed this multiple other people did the same. Sorry for the redundancy.
As it is, that's just stupid as any user can wantonly edit anything. I could trivially edit every level's author to be myself or do intensive operations which result in a DOS.
The only smart way to give clients access to a database is through some sort of frontend entirely under your control which prevents them from having the user/pass and sanitizes the queries.
Edit: Whoops, while I typed this multiple other people did the same. Sorry for the redundancy.
Yes, I replied to my comment and even updated the original comment indicating that I was incorrect.
> The only smart way to give clients access to a database is through some sort of frontend entirely under your control which prevents them from having the user/pass and sanitizes the queries.
MySQL maybe, but enterprise DBs (think Oracle, DB2, Postgres) support a very fine-grained access model.
MySQL maybe, but enterprise DBs (think Oracle, DB2, Postgres) support a very fine-grained access model.
I'd argue that even then, they are less hardened against network-layer exploits than your average webserver. Network security is bread & butter for a webserver, not for your enterprise DB running in safe intranets with only cursory penetration testing.
I agree, though if you hardcode username and password into your application there's no need for fancy exploits.
via smosher above: http://img820.imageshack.us/img820/1641/itsfinetrustme.png
It's not read only, it's read, insert and update.
supermeatboy.com seems to be down
So it could be that they create a mysql "database" for each user, and give them all the privileges needed there, and no privileges anywhere else.
And conceivably you could have some kind of proxy that looked like mysql but actually sanitized/logged/whatever any queries, before passing them on to the real server.
And conceivably you could have some kind of proxy that looked like mysql but actually sanitized/logged/whatever any queries, before passing them on to the real server.