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.
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.
"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".
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.
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.
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.
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.
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.
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.
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!
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 ?
> 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.
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.
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].
