Computer Science Canada :: View topic - SQL MMORPG game security

Computer Science Canada

SQL MMORPG game security

Author:	mirhagk [ Sun May 15, 2011 12:06 am ]
Post subject:	SQL MMORPG game security
I wanna make a text adventure style game that updates via a SQL database, kinda to learn SQL, kinda for fun. What I wanna know is how insecure is this? For instance I know about code injection, so I'm assuming I'll have to fight that. I also wanna know how difficult it would be for people to potentially crack the database password from the program, and so be able to connect directly to the database, doing whatever they want. And if there are any other major security problems, can someone tell me, and maybe help me try to solve them (if it's possible)

Author:	DemonWasp [ Sun May 15, 2011 9:22 am ]
Post subject:	RE:SQL MMORPG game security
Using a database has the potential to be very secure. SQL injection attacks can be absolutely defended against with just a little bit of discipline. Depending on your language, you will want "prepared statements" or the equivalent. These will let you enter any value, even SQL text, as entries without it being interpreted as SQL. Though not technically necessary on any query that doesn't use values from a potentially malicious source (such as the user), it's best to use them everywhere, just in case. Depending on the password involved, cracking a password can be either easy or hard. I suspect most databases implement an exponential-timeout algorithm for successive login attempts (first crack attempt waits 1ms, then 2ms, then 4ms, then...) from the same address, but I don't know that for sure. There are ways around that (such as botnets) but then there are other defenses. It is possible to put the database on a machine that isn't accessible from the outside world. Instead, the database can be connected to only from the "server" machine -- the one running your application. Users talk to the server, the server talks to the DB, and vice-versa. However, Users and DB are unable to connect to each other. Otherwise, the database may be put on the same physical machine as the server, but can be configured to only accept connections from localhost. This means that an attacker would have to compromise the server to get access to the database. Note that if they compromise the server they will have your database, no matter what setup you use. For a web service, the easiest security hole to open is to have your webserver misconfigured so that it serves more data than you wanted it to (or think it does). For example, if the webserver serves certain PHP pages, it can make your entire system open to attack along any number of trivial and very destructive paths. Less bad, but still extremely bad, is the case where your webserver serves your database configuration file...including a password. For a standalone program, the biggest issue will be having some kind of feature that exposes passwords, or allows arbitrary code execution, especially remotely. Even if you do practice all that good security above, you should make sure the database doesn't hold any sensitive data. You have to assume that an attacker could compromise the database and gain read access to it, and you have to protect sensitive data from being divulged. This means that passwords should be salted and hashed, preferably a few thousand times. Credit card numbers, if they absolutely MUST be stored, should be encrypted with respect to some key based on, for example, the user password. Preferably, don't store sensitive information at all.

Author:	Amailer [ Sun May 15, 2011 10:40 am ]
Post subject:	RE:SQL MMORPG game security
Use white lists rather than black lists when filtering user input whenever possible. Also use a known framework if there is 1 available. For your MySQL server setup, don't allow any remote connections to your database. If you have the DB server on a separate server, use a private network rather than connect over the public network. And please don't store CC information (Sony's a great example of why not to do that ).

Author:	mirhagk [ Sun May 15, 2011 1:52 pm ]
Post subject:	RE:SQL MMORPG game security
As for sensitive data, well only the people's passwords and usernames would be, everything else is simply game data. Oh and there is no server, that's kinda the point of it, I will have an admin program where I can update and change things freely, but every client program connects directly to the database. I know it's not secure, but I'm curious as to how well it will work and such. The biggest issue is that the database password is hardcoded into the client programs, so it may be possible to retrieve the password and have full access to the database. It wouldn't exposes sensitive info (other than passwords for users), but they could really fudge with the database. Oh and thanks for the tip concerning storing encrypted passwords, it's a good idea, and with a wierd enough algorithm I should be safe. The database password is computer generated, about 15 characters long with symbols and numbers, so it's secure, the only issue is the program knows it. I think it'd be a neat project to make a MMORPG that is completely serverless, just a central database to store values. (For text based it's easy, for others it may get difficult)

Author:	Amailer [ Sun May 15, 2011 1:58 pm ]
Post subject:	RE:SQL MMORPG game security
"The biggest issue is that the database password is hardcoded into the client programs" That is very bad lol. I'm not sure why you need to do this. If you need to access a database I see no point in avoiding an intermediary application which communicates with the database. Having the client connect directly to the DB (thus storing the password to the DB on the client side) is sooo bad.

Author:	mirhagk [ Sun May 15, 2011 2:22 pm ]
Post subject:	RE:SQL MMORPG game security
Lol I know, it is not exactly the safest, that's why I brought this up lol, if there was anyway I could make it more secure somehow. However I want to retain my system of direct connection with the database lol. Basically I'm experimenting with a series of trust applications. Everyone needs to be trusted to not fudge things up lol. This is gonna be a P2P MMORPG. Every client can run their own processing, and store their own temporary variables, and a global database is used for keeping track of all the data. Maybe might even make the clients connect directly to each other, and send all their info back and forth. I can even picture a game where the database is used only to keep track of who's online/who to connect to. When 2 clients connect they can check to see if the other has newer map info, or quest info or w/e lol. It's more of a fun research project than an actual application.

Author:	2goto1 [ Sun May 15, 2011 4:30 pm ]
Post subject:	RE:SQL MMORPG game security
Perhaps you can use an SSL connection between client and server, in addition to prepared statements for all SQL queries as DemonWasp suggested. MySQL has support for SSL clients, see http://dev.mysql.com/doc/refman/5.1/en/secure-using-ssl.html

Author:	Dan [ Sun May 15, 2011 4:36 pm ]
Post subject:	Re: RE:SQL MMORPG game security
mirhagk @ 15th May 2011, 2:22 pm wrote: Lol I know, it is not exactly the safest, Acutaly its proably the least safe way possible. mirhagk wrote: that's why I brought this up lol, if there was anyway I could make it more secure somehow. However I want to retain my system of direct connection with the database lol. There is no sane way to make it secure if the client is going to have direct access to the database. Rember that it is trival for an attacker to read the ram of there own computer and the packets going in/out from it. mirhagk wrote: Basically I'm experimenting with a series of trust applications. Everyone needs to be trusted to not fudge things up lol. It only takes one jerk to issue a drop table command. mirhagk wrote: This is gonna be a P2P MMORPG. Every client can run their own processing, and store their own temporary variables, and a global database is used for keeping track of all the data. Using a database like this is not P2P. It is just replacing a real server with the database which it was never designed to function as. mirhagk wrote: Maybe might even make the clients connect directly to each other, and send all their info back and forth. This is possible to do, however it will take some real research and thought to do right. You are far better off to start with a server-client based model intill you have a better understading of the topic. mirhagk wrote: I can even picture a game where the database is used only to keep track of who's online/who to connect to. When 2 clients connect they can check to see if the other has newer map info, or quest info or w/e lol. This is a bad idea for so many reasons. If you want to go with a P2P model then get ride of the cenertal database, if you want to go with a server-client model go with a real server that abstracts the database away from the clients. If you are going to go P2P you can do the updates easily enough so long as you implment a means of singing them for authenticity (otherwise any one could push an update which would end badly). Cheating is much harder to pervent in the P2P model tho there has been some research done in this area. mirhagk wrote: It's more of a fun research project than an actual application. Only if you acutactly do the research and don't just starting coding a bunch of database clients :p

Author:	Dan [ Sun May 15, 2011 4:40 pm ]
Post subject:	Re: RE:SQL MMORPG game security
2goto1 @ 15th May 2011, 4:30 pm wrote: Perhaps you can use an SSL connection between client and server, in addition to prepared statements for all SQL queries as DemonWasp suggested. MySQL has support for SSL clients, see http://dev.mysql.com/doc/refman/5.1/en/secure-using-ssl.html Thats not going to work when the connection is starting on the client's (an potential attacker's) computer as they will have the data before the SSL connection. SSL is only good for secureing the communcation between two nodes, not on the nodes them self.

Author:	2goto1 [ Sun May 15, 2011 4:57 pm ]
Post subject:	RE:SQL MMORPG game security
If the packets were all encrypted, would the attacker know what SQL statements were getting issued? They would know the location of the database server though.

Author:	Amailer [ Sun May 15, 2011 5:06 pm ]
Post subject:	RE:SQL MMORPG game security
They would since they are being generated from their computer, they don't even need to intercept the packets - just reverse the client. Maybe try and create something along the lines of what diaspora is trying to do for social networking. I have no idea however how you could prevent cheating.

Author:	2goto1 [ Sun May 15, 2011 5:11 pm ]
Post subject:	RE:SQL MMORPG game security
Yeah true...I guess obfuscation and compile time (not runtime) encryption of all SQL statements could help to thwart attackers...for a bit

Author:	Dan [ Sun May 15, 2011 6:04 pm ]
Post subject:	Re: RE:SQL MMORPG game security
2goto1 @ 15th May 2011, 5:11 pm wrote: .I guess obfuscation and compile time (not runtime) encryption of all SQL statements could help to thwart attackers...for a bit All the attacker would need to do is use a ram editor and change a value in the game. The game would then update database with that value. There are many programs for doing this to games wich are simple enough for your average gamer to use. Also all the attacker would need to get at the database is the password. It would be hard to obfuscate the database login. Plus any kind of white list woud be useless as all clients need access to the database in this kind of setup. Not sure what you mean by comple time encryption or how that would be possible for SQL statmetns. I dought any DBMS supports somthing like that and i fail to see how you could then put values into the SQL statments. Any kind of encryption on the client side woud be hard as the client would have both the key and the plain text. You could use public key encryption to sign updates and the code but that just allows the client to validate that the update or client software is legit and does not stop them from messing with there own client or making there own (affter all the comuncations are just SQL statments in this model). The only way to make this safe is to make your own server (for client-server setup) or get ride of the centeral database (if you are going P2P).

Author:	2goto1 [ Sun May 15, 2011 6:35 pm ]
Post subject:	RE:SQL MMORPG game security
Yeah there's only so much you could do. You could use compile time encryption to encrypt your SQL statements, and runtime encryption for your SQL parameter values. DBMS's that support certain encryption / decryption methods such as MySQL, http://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html could support this. You could create MySQL stored procs that accepted encrypted SQL as their input parameters. But then you're running a ton of dynamic SQL. It's not good security, nor the best architecture, but if Mirhagk absolutely wants to investigate it, those would be about the most secure things that I can think of for future research using that type of design.

Author:	mirhagk [ Sun May 15, 2011 6:49 pm ]
Post subject:	RE:SQL MMORPG game security
In the P2P model, the database would serve only to list IP addresses to connect to. Yes it could be messed up, but there's no sensitive data that could be stolen. The only bad thing that could happen is dropping in IP addresses for bad servers, which is why update packets would need some sort of validation. Besides, this is just for a fun side project, not like a serious game, and def not for profit.

Author:	Tony [ Sun May 15, 2011 6:52 pm ]
Post subject:	RE:SQL MMORPG game security
If the client only needs to read some simple data, why not return a file back from a web server?

Author:	mirhagk [ Sun May 15, 2011 6:56 pm ]
Post subject:	RE:SQL MMORPG game security
yeah but it needs write access. I just wanna do all of this for free, and without too much difficulty.

Author:	Amailer [ Sun May 15, 2011 7:34 pm ]
Post subject:	RE:SQL MMORPG game security
Not sure what you mean by it needs write access (what does? client?) but if you are already planning on hosting a central database server then it should be pretty cheap to get a web hosting service as well. You can even try and start hosting it off your ow n computer.

Author:	Dan [ Sun May 15, 2011 7:52 pm ]
Post subject:	Re: RE:SQL MMORPG game security
2goto1 @ 15th May 2011, 6:35 pm wrote: Yeah there's only so much you could do. You could use compile time encryption to encrypt your SQL statements, and runtime encryption for your SQL parameter values. Thats not going to work. The runtime encrypted SQL updates will not be secure at all due to the client having access to the key and plain text and the pre encrypted statments would not work as the client could just send the same encrypted statment for the same result. If the result is encrypted the client would allready have the key to decrypt it. Quote: DBMS's that support certain encryption / decryption methods such as MySQL, http://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html could support this. You could create MySQL stored procs that accepted encrypted SQL as their input parameters. But then you're running a ton of dynamic SQL. What happens if i just echo the same ecnrypted sql statment? I would get the same result set. Also what point is there in ecrypting select statments only? The results would be in the cleints ram anyway. Updates would be imposbile to preencrypted. Also thoes functions will not work for the login, proably the most important part. Quote: It's not good security, nor the best architecture, but if Mirhagk absolutely wants to investigate it, those would be about the most secure things that I can think of for future research using that type of design. There not secure at all, they just have the illusion of seucrity. All it takes is one user to make/use a game trainer or find the mysql login password. mirhagk wrote: In the P2P model, the database would serve only to list IP addresses to connect to. Yes it could be messed up, but there's no sensitive data that could be stolen. The only bad thing that could happen is dropping in IP addresses for bad servers, which is why update packets would need some sort of validation. For P2P you do not need to list the IP of evey single clinet, a client would only need to know the IP of one other client to join the network. There is no reason why you could not make this a web service like tony sugests. It would be trvial to make a web app that simply logs IPs and checks that some subset are still online and valid and returns them to clients requesting them. There are far more complicated issues with P2P networking that you will run into then this. I strongly sugest you start with a server-client model intilly you have a stronger understanding of the basics. Quote: Besides, this is just for a fun side project, not like a serious game, and def not for profit. You will not learn much from it if you take the easyest and least usefull route. Creating a simple server is not that hard, you could even make it in PHP using HTTP requests (not recomened but it's easy and far safer). mirhagk wrote: yeah but it needs write access. I just wanna do all of this for free, and without too much difficulty. In the long run the P2P model will have far more difficulty invloded then the client-server model. If this is only a text based RPG then easyest route would be a web app of some kind.

Author:	mirhagk [ Mon May 16, 2011 8:03 am ]
Post subject:	RE:SQL MMORPG game security
I can't host off my own computer (first of all it's illegal under my ISP's rules), and I kinda didn't want to use HTTP requests with PHP (I don't know PHP either, so I'd have to use ASP.NET or learn a new language). The purpose of this is not to learn how to make multiplayer games, I understand the concepts behind them and the thing holding me back from making one is the cost of a server. It's meant to be for fun, and a little side project. It's also meant to show how much better things could be if clients were trusted, like each client handling things itself means a MUCH cheaper server, it's just not safe.

Author:	2goto1 [ Mon May 16, 2011 9:05 am ]
Post subject:	RE:SQL MMORPG game security
If your database only exposes certain rights to a given database login, then the login cannot perform certain things, such as dropping a table, see http://dev.mysql.com/doc/refman/5.1/en/grant-table-structure.html You can also grant rights to certain stored routines, http://dev.mysql.com/doc/refman/5.0/en/grant.html#grant-routine-privileges Your database server then begins to look more like an application server. By limiting what a user can execute within a database, you can effectively get a more secure solution. I don't see it being much different from a web/application server . Your web service would expose certain web services, any of which could be happily hacked away at, just as a stored procedure could be invoked at will. I've never personally designed applications this way, other than corporate client / server applications in the past, but, it is feasible that it would be at least as secure as a web server that exposes web services. If you did take this approach, I'd suggest that you encapsulate all of your database operations into stored procedures. This would likely make it easier to port your stored procedures into web services at a later point.

Author:	2goto1 [ Mon May 16, 2011 9:15 am ]
Post subject:	RE:SQL MMORPG game security
I worked in a company that designed this exact architecture for their "flagship" application (deployed in hundreds of client installations). It was wacky - the system was originally a traditional client server architecture that used Oracle as the backing RDBMS. It became a service oriented architecture, with different clients accessing Oracle and Oracle acting as the application server and database server. It was pretty strange. Client user bases were pretty small...I'm not sure how well it would have scaled. But it did work for their needs, and was likely their path of least resistance to evolve into.

Author:	Amailer [ Mon May 16, 2011 2:41 pm ]
Post subject:	RE:SQL MMORPG game security
I don't get it though. If you cannot host a server, and you aren't planning on buying 1. Where exactly is the central database (SERVER) being hosted? Also, even if you restrict access to the MySQL user that each client will use to connect, at the end they all need READ access. So long as they have that they are able to access the DB passwords and other information ( what's stopping me from altering it so that instead of my client requesting data pertaining to my account, I make it request data pertaining to someone elses account and etc ).

Author:	Dan [ Mon May 16, 2011 3:07 pm ]
Post subject:	Re: RE:SQL MMORPG game security
mirhagk @ 16th May 2011, 8:03 am wrote: It's meant to be for fun, and a little side project. It's also meant to show how much better things could be if clients were trusted, like each client handling things itself means a MUCH cheaper server, it's just not safe. It's not just malicious users you have to worry about but issues that could cause an undresired state in one of the clients. For example if some bug or issue on a client causes a bunch of values to be filled with bad data, it is going to update that directly to the database. Let be honest this is not to "show how much better things could be if clients were trusted" it's about being lazy and not wanting to code a server application. As Tony has pointed out web hosting is just as cheap (if not cheaper) then database hosting, including many free hosts that allow for free PHP and ASP hosting. 2goto1 wrote: I don't see it being much different from a web/application server . Your web service would expose certain web services, any of which could be happily hacked away at, just as a stored procedure could be invoked at will. This is not what DBMS where desinged for and it is nothing like a web application server. Since the user has to have acces to any table they can update they can see maniuplate data. Just think about the table that will store all the users for the game. The mysql user the game client is using will have to be able to select this table or the game users will not be able to login. A malicious users coud steal the mysql password and gain access to this table listing all game users, there passwords, e-mails, etc. Even if the passwords where hashed, the malicious user could still use them to login or could update to there own hash as they would need to have update access to this table as well. You would be putting all user data in the open which is acautactly illgeal in some countries and provices data privacie laws. Stored procduecs are not a magic soultion for this. If you are sugesting replacing every single mysql call in the program with a storaged procdueer call, thats just madness and horrible design. Plus you still don't have any mechanism for logging in the game user. You may have a stored procedure to check if a plain text password matches the hash stored in the database but thats not going to stop a malicious client from ignoring the result and just calling other procedures. You would be basicly giving every one the same level of access control to every procedure needed for the game even if they call it under some other user. If there was a procedurer to allow a user to delete there character in the game, a malicious client could call that for any user. If there was a procedurer to allow a user to change there password, a malicious client could change any ones password and so on. 2goto1 wrote: I've never personally designed applications this way, other than corporate client / server applications in the past, but, it is feasible that it would be at least as secure as a web server that exposes web services. We all do stuipd things when we are inexpried with a tehcnogloly, but it does not mean we should encourage others to do the same. As i have stated this is not as secure as a web service. Web services have access control, this would be closer to a web service in which any one can call any opeartion even if that operation reverals senstive information, has a destructive result or changes another users information. No one would intentionally create such a web service. Since the game client would only have a single MySQL user and password hardcoded into it there would be no way for the DBMS to differentiate between diffrent game users and would give equal access to all procedures for that database user dispite the procedure being called or the data it is updating or returing. If you add a new databse user for each game user, then your not even making the game in the client any more but in MySQL and the game client just becomes a MySQL client with introduces a hole new level of WTF. Making a game purerly in MySQL might be intresting if thats your goal as some what of a joke or just seening if it's possible but for real world applications it just madness. Quote: If you did take this approach, I'd suggest that you encapsulate all of your database operations into stored procedures. This would likely make it easier to port your stored procedures into web services at a later point. Why not just make a web services to start with? You can get free php, asp and somtimes even jsp hosting and everything will be secure from the get go (tho still rather inefficient, you should be making a steam lined TCP protocol between clients and a server not basing it on HTTP requests). 2goto1 wrote: I worked in a company that designed this exact architecture for their "flagship" application (deployed in hundreds of client installations). It was wacky - the system was originally a traditional client server architecture that used Oracle as the backing RDBMS. It became a service oriented architecture, with different clients accessing Oracle and Oracle acting as the application server and database server. It was pretty strange. Client user bases were pretty small...I'm not sure how well it would have scaled. But it did work for their needs, and was likely their path of least resistance to evolve into. Thats not a service oriented architecture, thats just madness. RDBMS are not web services alone nor are they inintend to be used as such. I do not have much expirece with Oracle, so it is possible they have some added functinonality or application services beyond that of a noraml DBMS to make this function seucrely and sainly, but in most cases this would be a masive WTF, especially if all the clients are using the same databse username/password. There are many great software packages and frameworks for creating real web services with database backends, i don't understand the need to try and merge it into the DBMS. In the long run it will almost allways be more work and less secure then taking a few minutes at the start to research your options and think about the design of your system. I think we should define a new anti-pattern as the "client-databse model of network comuncations" in which all comuncations between clients in some system are done thoruhg a DBMS directly rather then through a server or P2P model.

Author:	2goto1 [ Mon May 16, 2011 3:48 pm ]
Post subject:	Re: SQL MMORPG game security
I agree, this is not something that I would personally want to do, but if someone really wants to experiment, by all means....knock yourself out! Encapsulating all database operations behind stored procedures is not madness, in fact it can be a good thing in certain scenarios. Many large organizations with large enterprise systems do exactly this, because of the benefits of the extra layer of indirection provided, such as data model security, audit logging, and more data-model control by the DBAs. Large banks for example might completely shield their database tables from client developers for security purposes. If your database exposes only stored procedures to users, you can effectively hide your entire data model. Quote: "For example if some bug or issue on a client causes a bunch of values to be filled with bad data, it is going to update that directly to the database" - A buggy client accessing a buggy web service can cause data integrity issues, so this point is moot Quote: "it's about being lazy and not wanting to code a server application" - Since I don't know mirghagk, I defer to your knowledge of him. I would have assumed from what he wrote that he's just curious and would like to investigate this purely for personal purposes. It's not the ideal design but, this discussion is a good exercise in understanding the roles of different system boundaries. Quote: "Since the user has to have acces to any table they can update they can see maniuplate data." - You can hide database tables from users, and encapsulate all client access through stored procedures. Quote: "The mysql user the game client is using will have to be able to select this table or the game users will not be able to login. A malicious users coud steal the mysql password and gain access to this table listing all game users, there passwords, e-mails, etc." - A single RDBMS database login could be created for all users. Then, each user could have their own user profile (with a user name / password of their own) for storing game data. This user profile's user name and password (something hashed and stored on server) could be passed to the database server not as an RDBMS database login, but primarily as a foreign key reference to update / get statistics Quote: "We all do stuipd things when we are inexpried with a tehcnogloly, but it does not mean we should encourage others to do the same. As i have stated this is not as secure as a web service" - Ultimately the decision is up to mirghgak. Discussions like these can help to better understand the roles of system boundaries Quote: "I think we should define a new anti-pattern as the "client-databse model of network comuncations" in which all comuncations between clients in some system are done thoruhg a DBMS directly rather then through a server or P2P model." - Fowler, Booch et al would agree

Author:	2goto1 [ Mon May 16, 2011 3:55 pm ]
Post subject:	Re: RE:SQL MMORPG game security
Amailer @ Mon May 16, 2011 2:41 pm wrote: I don't get it though. If you cannot host a server, and you aren't planning on buying 1. Where exactly is the central database (SERVER) being hosted? Also, even if you restrict access to the MySQL user that each client will use to connect, at the end they all need READ access. So long as they have that they are able to access the DB passwords and other information ( what's stopping me from altering it so that instead of my client requesting data pertaining to my account, I make it request data pertaining to someone elses account and etc ). I think most residential ISPs disallow hosting websites. I've hosted small websites this way without any issue and never incurred additional fees. You're right though, their terms of use would probably include any server technology run from home, such as a database server, etc. I would think that they wouldn't really care though unless there was serious traffic. It's not like ISPs are banning their clients for running bittorrent clients 24/7. If all data requests include the user id and their hashed password, and you encrypt your database parameters, then you're probably getting close to being as safe as a web app that requests a user name / password over SSL. Although I wouldn't want to do that, it's obviously not what anyone would consider a sound architecture...it probably could be reasonably secure

Author:	Tony [ Mon May 16, 2011 5:13 pm ]
Post subject:	Re: SQL MMORPG game security
I'm not entirely keeping up with this discussion, but I'd mostly side with Dan on this one. There are a lot of points that need to be addressed, but a couple that jump out the most at me 2goto1 wrote: ... stored procedures ... My interpretation of your arguments is that one could implement most of application server's functionality in DBMS itself, via stored procedures. This "almost as good" approach is not ideal, as it's more difficult to implement, maintain, and make sense of. The anecdotal enterprise examples are not applicable, as the usage patterns and accountability of paying B2B users are completely different from arbitrary public users. 2goto1 @ Mon May 16, 2011 3:48 pm wrote: - A buggy client accessing a buggy web service can cause data integrity issues, so this point is moot It's not moot, as this requires 2 related points of failure, instead of just one. And since the clients are public, they are inherently "buggy" (untrusted), leaving the application server as the layer that validates the data.

Author:	Dan [ Mon May 16, 2011 5:55 pm ]
Post subject:	Re: SQL MMORPG game security
2goto1 @ 16th May 2011, 3:48 pm wrote: Encapsulating all database operations behind stored procedures is not madness, in fact it can be a good thing in certain scenarios. It is when you do it in the name of seucrity becues you gave out your database password to all of your clients..... Quote: Many large organizations with large enterprise systems do exactly this, because of the benefits of the extra layer of indirection provided, such as data model security, audit logging, and more data-model control by the DBAs. Large banks for example might completely shield their database tables from client developers for security purposes. No where did i say stored procedures where madness, i side using them as security becuse you are to lazy to make a server or web service is. Stored procedures are not going to protect your data when they change it and you give every one access to run the procedures. Like the example in my last post, if you have a procedure to update a users password and then let any one run it, it's going to let any one update any ones password. Quote: If your database exposes only stored procedures to users, you can effectively hide your entire data model. Hidding your data model from your devlopers is a bad idea, exposing your stored procedures directly to your clients (and in the case of this game any person online) is a bad idea. Doing both is an even worse idea. It is very easy to give your devlopers the data model with out giving them the data it contains. This is why we have test and devlopment servers, unit testing and don't write code on production servers. Devlopers need to understand the underlying data models if they are going to make efficient and safe code. As for clients, it is madness to give every one access to your DBMS, even if it is just stored procedures. In the case we have been discussing in this thread, the game client will use a single databse user and password pair, this means every on who has the game client is going to have equal access to all stored procedures. Even if you set access controlls on those stored procedures you are not going to be able to stop one user from effecting anothers data by using them. It may stop them from doing somthing like droping the table or editing a table that users would not noramly have access to like the maps but it would not stop them from obtaining other users information or making changes to other users data. It would also not stop them from running procedures with bad data that would warp them to a new map location, give them some item, etc. Quote: Quote: "For example if some bug or issue on a client causes a bunch of values to be filled with bad data, it is going to update that directly to the database" - A buggy client accessing a buggy web service can cause data integrity issues, so this point is moot It's not. In this case you have a large number of clients distrubted accross a large number of systems all updating the database. Any issues are multipled by the number of clients. It would not even have to be a bug in the code but an issue with the clients hardware or other software. For example what if there exists some client who has bad ram and some values get croupted and then updated? The odds of this happening are rather low for a single server but when you push it to the client side you are rolling the dice far more times. Adimitly this could be checked thorugh data validation in stored procescedures which would be an O.K. use of stored procedures noramly but in this case your basicly moving all game logic and data meaning to the database which is very much aginsted better programming partices. Databases are for storing data, not for sotring and executing all of the logic for your program. Quote: Quote: "it's about being lazy and not wanting to code a server application" - Since I don't know mirghagk, I defer to your knowledge of him. I would have assumed from what he wrote that he's just curious and would like to investigate this purely for personal purposes. It's not the ideal design but, this discussion is a good exercise in understanding the roles of different system boundaries. If we wanted to implment a MMORPG in MySQL just to see if he could, or for fun, then i would adgree. But based on everything he has posted the reasons for doing it this way are to 1. be cheap and 2. be easy. You can see this in this quote: mirhagk wrote: yeah but it needs write access. I just wanna do all of this for free, and without too much difficulty. Also if he is intrested in this for any kind of learning or educational experience, he should set out to do it following valid design patterns and not anti-patterns. Following anti-patterns becues they seem easy, is only going to teach bad habits and be much harder down the road. Quote: Quote: "Since the user has to have acces to any table they can update they can see maniuplate data." - You can hide database tables from users, and encapsulate all client access through stored procedures. Ok now they can just call the stored procedure to the same effect. How does that help? If there is a stored procedure to update a game users password they can still call it for any user. If there is a stored procedure to return a set of data about a user, they can still call that for any user. If there is a stored procedure to give a user an item, they can still call that with any item they like. Using only one database user, there is no good way to enforce the login of a game user. Quote: Quote: "The mysql user the game client is using will have to be able to select this table or the game users will not be able to login. A malicious users coud steal the mysql password and gain access to this table listing all game users, there passwords, e-mails, etc." - A single RDBMS database login could be created for all users. Then, each user could have their own user profile (with a user name / password of their own) for storing game data. This user profile's user name and password (something hashed and stored on server) could be passed to the database server not as an RDBMS database login, but primarily as a foreign key reference to update / get statistics If it is used as the primay key for the user, then what will be sent to the client when intracting with other users? It can't be this hash or the client would then have it and could use it to impersonate another user. If we are only going to use this hash when the client is calling stored procedures to idenfity them self then we still have the issue of the client being able to call any stored procedure realting to them self, allowing for things like warping, giving them self items, doing imposible attacks, etc. Also that is effecitly loging in each and every time they send a request to the DBMS, which is horaibly unefficient. Each and every stored procedure a client can call will now need to check that the hash is valid. Sure you could make the sotred procedures even more masive and complex and validate every single part of a request. E.g. only allow the user to move to x,y if x,y is in range of how far the user's charcter move in a turn or only allow a user to use item z if they have item z. But you will now have moved every single part of the game logic to the DBMS. The game client effectively becomes just a client for the DBMS, now it's not even just client-database but just database. Also the DBMS you are using is not going to handel this efficiently as it simply was not designed for it and you will run in to scablity issues rather fast. Plus it will be very hard to use master-slave or sharding database setups in this design to help with the efficeincey issues (this is meant to be a MASIVELY mulitiplayer online role playing game). Also for sake of arumgent if we did want to go this mad route, it would make more sence to make the login procedure create a session row in a session table and return a session key rather then sending the same hash each time. This way you could at least introduce the conecpt of sessions in to the model and not login on each request. Then you could leave the user table the way it is with a noraml id and safely refure to users by there noraml primary key rather then a hash. Quote: Quote: "We all do stuipd things when we are inexpried with a tehcnogloly, but it does not mean we should encourage others to do the same. As i have stated this is not as secure as a web service" - Ultimately the decision is up to mirghgak. Discussions like these can help to better understand the roles of system boundaries How does totaly ignoring the systems boundaries and merging everything togher in to an ungodly ball of databse, data model, game logic, server logic, authentication and data logic help any one understand that role? The only way this can teach some one, is in what not to do. Quote: Quote: "I think we should define a new anti-pattern as the "client-databse model of network comuncations" in which all comuncations between clients in some system are done thoruhg a DBMS directly rather then through a server or P2P model." - Fowler, Booch et al would agree [/quote] Then why are you still aruging for the model? Honestly at this point we are at: To lazy to make server so we just use the database. Database not secure so we use sotred procedures. Stored procedures not secure so we use hashing scmece to emulate a login on each request. Login on each request still does not protect user from running procedures on there own account (as they know there own hash) so we add a crazy number of validation steps to the procedure intill the hole game logic is in the database. Would it have not been easyer to just make the server? This is just going down a rabit hole of introducing new adbuses of the DBMS at each level and ending up with an unefficient, unscable, hard to understand, hard to maintain, unportable to a new DBMS, and complex soultion. Simply taking a few hours to learn about network sockets would slove all of this. Also a note about using web services for a game server: This maybe a good desing if all you can aford is cheap webhosting or if you want to do somthing creative and make it easy for any one to implment there own game client. However for a partical game server it would be rather ineffective. SOAP and/or HTTP requests are going to have alot of overhead which will slow things down signifactily. I highly recomend trying to implment your down protocol ontop of TCP, you will learn alot and end up with somthing potentaly faster and easier to secure. You could also try using UDP for updates of frequently chaning unimporant paramters such as the characters current posstion in a game. P.S. Sorry if i sound overly mad in theses posts, i just don't like the idea of using anti-patterns for anything but showing why not to use them. Don't even get me started on using the waterfall design pattern...

Author:	mirhagk [ Mon May 16, 2011 7:16 pm ]
Post subject:	RE:SQL MMORPG game security
So I just want to be clear on the point of this, to have fun and learn. I don't care about making a functional game, and I don't want to learn about server-client models, I've read many books, and articles on many different models. The point of this is to learn about SQL, while experimenting with something kinda ridiculous. Many websites offer free database hosting, and the only other thing that is free is something that interacts with HTTP, ie too slow for anything other than a text based game. I don't have money, so actually creating a server is not exactly an option. (I mean I have created client-server applications, and even games, but nothing outside of my LAN, since it's illegal). Also I want to point out that this whole discussion makes me want to do this project alot more, if only to learn SQL, and challenge myself lol. I don't know much about stored procedures, but under my knowledge it seems it would be trivial to have each entry have some sort of hash of the password and username and use only that for updating. Basically the procedure would end with WHERE @hash so that the client can access it's own entry, but no others, there's no extra validation here (as the client would need a WHERE clause anyways.) Anyways, if I were serious about making a game, or just lazy and didn't want to make a server (or didn't know), then I would quit now. But I'm not lazy, I just like experimenting, and this all seems possible, and very intriguing. I think I am now going to code the game pretty much entirely in SQL, just for fun, didn't know it was actually possible lol, but the more I learn the more possible it seems. I didn't mean to anger anyone, and I apologize if I have lol, I just wanted to experiment. And PS your all wrong about the success of this project, it's already more than served it's purpose, I have learned more about SQL that I ever could have developing a web service. For instance when would I ever have to use stored procedures? Not until I made a very advanced web service, which is likely never, and not for a long time otherwise. Thank you all, this is why I love compsci.

Author:	Dan [ Mon May 16, 2011 7:21 pm ]
Post subject:	RE:SQL MMORPG game security
Well you have learned how not to use them.... and how not to make a server... Post the game when it's done, i whould like to have some "fun" with it

Author:	2goto1 [ Mon May 16, 2011 8:12 pm ]
Post subject:	RE:SQL MMORPG game security
All healthy discussion. I'm not pro the architecture, but, I do think it would be feasible based on my experience. Feasible but not an ideal architecture

Author:	mirhagk [ Mon May 16, 2011 8:13 pm ]
Post subject:	RE:SQL MMORPG game security
The point is that I have learned not only that they exist lol, but I will learn how to implement them. It's not like this experience will make me worse at SQL lol, I know how stupid/insecure it is. LOL I will def post it dan, maybe me and you can have a little competition, I will try to make it as secure as possible, and you try to fudge it up as much as possible. I'd very much enjoy that, if your up for it.

Author:	2goto1 [ Tue May 17, 2011 8:25 am ]
Post subject:	RE:SQL MMORPG game security
Knock yourself out, not sure if will be a resume-worthy project though

Author:	mirhagk [ Tue May 17, 2011 9:28 am ]
Post subject:	RE:SQL MMORPG game security
Oh lol def not, so it's kinda low priority for me. It's not something I would put on my resume (unless I made it really secure somehow, and then bragged about how I could make something totally insecure secure.)

:

You can syndicate this boards posts using the file backend.php or view the topic map using sitemap.php.

Terms of Use | Privacy Policy