Are player's IDs always numeric in your games?

[Blacklava]

Hi,

working on some code and wondering if player key/ID used on your games are most of the time numeric and if you have a guess of the % of alphanumeric ones?
Just checking if my is_numeric function test won't be a problem... Perhaps some use directly nicknames as unique key?
Not that I'm lazy but it's a bit of pain to modify and complete sql injections protects if I need to change

Thanks

[bbgames]

Generally I tend to track user IDs and usernames - while usernames have to be unique, users never see or interact with their user IDs. I leave the IDs numeric, to take advantage of having an auto_increment field in my database that I can use as a primary key.

[Blacklava]

yes same here, I'm wondering if almost everybody doing so or if I need to bother with alphanumeric IDs... If 95% uses numeric IDs I'll skip the extra pain 

[Nox]

http://community.bbgamezone.net/index.php/topic,2527.0.html

[dsheroh]

working on some code and wondering if player key/ID used on your games are most of the time numeric and if you have a guess of the % of alphanumeric ones?

When I'm dealing with an SQL database directly under a relational(ish) model, I use numeric ids for everything.  It's more efficient, plus autoincrement/serial id fields are just too convenient to pass up.

In my current game project, I'm using object graph storage rather than an SQL database[1] and, for items with names, I have chosen to derive their ids from the type of object and its name.  So, for example, my test user has id "user:dave" and owns the character with id "char:dave".  (Objects without names get default ids like "4796A8BA-36C2-47D4-9916-345E263D2BC5".)

Not that I'm lazy but it's a bit of pain to modify and complete sql injections protects if I need to change

Use SQL placeholders/parameters and you don't have to worry about SQL injection.  Period.  No matter what you change.  It's 20-freaking-10 and there is no excuse for trying to escape strings instead of using placeholders.

http://bobby-tables.com/ has language-specific guidance on how to do this for most of the currently-popular web development languages.  (Although I just checked and the PHP page only has examples for the PostgrSQL and ODBC extensions.  If you regularly use parametrized queries with PDO, please fork the site on github and submit a patch adding a PDO example.)


[1]  Well, technically, KiokuDB implements object graph storage on top of an SQL database, so SQL is ultimately still involved, but I'm not interacting with the database as SQL.

[JGadrow]

Use SQL placeholders/parameters and you don't have to worry about SQL injection.  Period.  No matter what you change.  It's 20-freaking-10 and there is no excuse for trying to escape strings instead of using placeholders.

Woo hoo! Another advocate of better security! In MySQL this is called "prepared statements" look it up, you'll be glad you did. Oh, and welcome to 2005. lol

+1 dsher.

[133794m3r]

I know it's really off topic and all, but i went to that page to learn about well what it said since i'm always up for making myself more secure. and it reminded me of when i first saw the comic, i had no idea that it was really saying what it was. Till, i looked up sanitizing your inputs and came to mysql injection. Which was a little thing i saw on hackthissite. And also wow dsheroh. Never heard of that site before or new that anything like that existed... i just thought sanitizing the input was a good measure.

[codestryke]

Use SQL placeholders/parameters and you don't have to worry about SQL injection.  Period.  No matter what you change.  It's 20-freaking-10 and there is no excuse for trying to escape strings instead of using placeholders.

Woo hoo! Another advocate of better security! In MySQL this is called "prepared statements" look it up, you'll be glad you did. Oh, and welcome to 2005. lol

+1 dsher.

Unfortunately using prepared statements requires the use of the mysqli extension. Normally this would not be a problem, however, mysqli does NOT support persistent connections. Without careful testing just saying "use mysqli and prepared statements" could put someone in a database nightmare depending on there server setup and what they are pulling for traffic.

Secondly although the security is superior with prepared statements you have to know what the server is doing. When you prepare a statement that is one trip to the db server, then you execute the statement and that is another trip to the db server. So now you've doubled your load to the database server.

Yes it is 2010 but the fact remains (as always with programming) one solution doesn't fit every developers  needs.

[Nox]

It does not have to be necessarily via prepaired statements, you can create your own version of replacing placeholders with given escaped values

[dsheroh]

I know it's really off topic and all, but i went to that page to learn about well what it said since i'm always up for making myself more secure. and it reminded me of when i first saw the comic, i had no idea that it was really saying what it was. Till, i looked up sanitizing your inputs and came to mysql injection. Which was a little thing i saw on hackthissite. And also wow dsheroh. Never heard of that site before or new that anything like that existed... i just thought sanitizing the input was a good measure.

You never heard of bobby-tables.com before because it's fairly new.    IIRC, Andy just set it up within the last month or so.

[133794m3r]

I know it's really off topic and all, but i went to that page to learn about well what it said since i'm always up for making myself more secure. and it reminded me of when i first saw the comic, i had no idea that it was really saying what it was. Till, i looked up sanitizing your inputs and came to mysql injection. Which was a little thing i saw on hackthissite. And also wow dsheroh. Never heard of that site before or new that anything like that existed... i just thought sanitizing the input was a good measure.

You never heard of bobby-tables.com before because it's fairly new.    IIRC, Andy just set it up within the last month or so.

i was speaking mostly of the idea of sql prepares and such things as that. And also the entire comic itself. It made no sense to me at the time till i went to hts which showed me a few things well for as far as i was able to get on how to not allow things to go over the hill to help myself be protected. And also what code said, i doubt it'll be right for me though then until i get a nice stable playerbase set up, since 2 database connections per page request is definately going to put a hamper on my performance. Especially when i'm attempting to do some semi-open testing as the performance hit on a plain old hosted site will be low enough already.

[dsheroh]

Unfortunately using prepared statements requires the use of the mysqli extension. Normally this would not be a problem, however, mysqli does NOT support persistent connections. Without careful testing just saying "use mysqli and prepared statements" could put someone in a database nightmare depending on there server setup and what they are pulling for traffic.

http://bobby-tables.com/php.html now shows examples for using prepared statements in PHP under the mysqli, ADODB, ODBC, and PDO extensions.  Do all of those options fail to support persistent connections?  (And, for that matter, does PHP really only give access to persistent connections at the database library level?  When I think about persistent db connections for web apps in Perl, I generally think of them being managed by apache, not by DBI.  But, then, I generally think in terms of persistent applications rather than persistent db connections anyhow...)

Secondly although the security is superior with prepared statements you have to know what the server is doing. When you prepare a statement that is one trip to the db server, then you execute the statement and that is another trip to the db server. So now you've doubled your load to the database server.

If you only use each prepared statement once, sure.  But database-intensive apps generally run basically the same query several times, differing only in WHERE clause conditions or values to INSERT/UPDATE, all of which can be parameters for a pre-prepared statement.  Reuse the prepared statement and the number of trips to the database only increases to n+1, not 2n.

Also take into account that, even if you don't explicitly prepare the statement before executing it, the server still has to implicitly prepare it.  By reusing the prepared statement, you avoid repeating that overhead and will, in most cases, reduce the overall load on the database server.

And also what code said, i doubt it'll be right for me though then until i get a nice stable playerbase set up, since 2 database connections per page request is definately going to put a hamper on my performance.

You misunderstood his second point.  Doing a separate prepare, then execute requires an additional request to the database server, not an additional connection.

[133794m3r]

Unfortunately using prepared statements requires the use of the mysqli extension. Normally this would not be a problem, however, mysqli does NOT support persistent connections. Without careful testing just saying "use mysqli and prepared statements" could put someone in a database nightmare depending on there server setup and what they are pulling for traffic.

http://bobby-tables.com/php.html now shows examples for using prepared statements in PHP under the mysqli, ADODB, ODBC, and PDO extensions.  Do all of those options fail to support persistent connections?  (And, for that matter, does PHP really only give access to persistent connections at the database library level?  When I think about persistent db connections for web apps in Perl, I generally think of them being managed by apache, not by DBI.  But, then, I generally think in terms of persistent applications rather than persistent db connections anyhow...)

Secondly although the security is superior with prepared statements you have to know what the server is doing. When you prepare a statement that is one trip to the db server, then you execute the statement and that is another trip to the db server. So now you've doubled your load to the database server.

If you only use each prepared statement once, sure.  But database-intensive apps generally run basically the same query several times, differing only in WHERE clause conditions or values to INSERT/UPDATE, all of which can be parameters for a pre-prepared statement.  Reuse the prepared statement and the number of trips to the database only increases to n+1, not 2n.

Also take into account that, even if you don't explicitly prepare the statement before executing it, the server still has to implicitly prepare it.  By reusing the prepared statement, you avoid repeating that overhead and will, in most cases, reduce the overall load on the database server.

And also what code said, i doubt it'll be right for me though then until i get a nice stable playerbase set up, since 2 database connections per page request is definately going to put a hamper on my performance.

You misunderstood his second point.  Doing a separate prepare, then execute requires an additional request to the database server, not an additional connection.

well, even if it's only using 2 requests per page though, that page would then have to do additional read/writes to the database. And if it's something such as a page where someone buys an item perse from the item shop. Then it'd normally have then buying one or how many they wish to input, it'd then go pull the cost of the item from the one table and get that, then it'd subtract the  amount from the players current amount in their inventory in the database section. So using prepared statements in such a situation such as that one which'll be the most common type of database requests i have. Would they result in n+1 requests? Since it's mostly going to be select from where, and also i'm going to of course going to have to pull in some data from it and the update for the player. In that situation in your professional opinion how much of an increase of requests should i expect?

Also my main worry for this is that i've yet to benchmark any of this on my server yet so i have no idea what type of performance i should expect. And if it can only do let's say 20 requests per second, and if i have 200 players online(also a high estimation for this purpose and a man can dream), and if they're randomly clicking around doing things. This'd mean that i currently with x number of requests per page be able to serve y people. If it's 2x the amount of requests. Then that'd mean that i'd only be able to server y/2 number of people per second which would then result in decreased performance which may then make them leave my game.

[Nox]

As I said - make your own placeholder replacement/whatever and you'll have no request increase
Say something like this http://dibiphp.com/en/quick-start

[Chris]

Also my main worry for this is that i've yet to benchmark any of this on my server yet so i have no idea what type of performance i should expect. And if it can only do let's say 20 requests per second, and if i have 200 players online(also a high estimation for this purpose and a man can dream), and if they're randomly clicking around doing things. This'd mean that i currently with x number of requests per page be able to serve y people. If it's 2x the amount of requests. Then that'd mean that i'd only be able to server y/2 number of people per second which would then result in decreased performance which may then make them leave my game.

SQL performance is not about site speed but server stability. It is not that if you have plenty of DB requests it will slow down your site, it will crash it. If you have more requests SQL can process it starts queueing them, queues uses memory, it makes the server slower and slower... and then it crashes
At least that's how it works in my case, I do not recall any slowdowns, but crashes yes. It is either blazing fast or total crash, never anything inbetween.
Also the number of players that cause this is not linear. With 2500 I got everything working smoothly, but one day I got 2700 (less than 5% increase) and everything crashed. It's similar to a drop of water that over-something (forgot the word) the ocean

[JGadrow]

@codestryke
I'm surprised, sir, you've seen me countless times state that there is never a "single solution" to any problem. Nowhere did I state that it was the only thing that you should ever do. Only that it was preferable and that some research should be conducted into it by those who are not "in the know."

It is the fact that there is not a single solution to every problem that keeps me interested in programming. If every question only had a single answer, then it wouldn't really be necessary to have more than a handful of developers.