Jump to content

Photo

How To Make a User Account System


  • Please log in to reply
225 replies to this topic

#1
Kyek

Kyek

    Founder of wdR

  • Administrators
  • 5371 posts
  • Joined: 20-February 10
  • LocationPhiladelphia, PA, USA
  • Expertise:HTML, CSS, PHP, Java, Javascript, Node.js, SQL

*
POPULAR

How To Make a User Account System
Written by Kyek
June 13, 2010


So you want user accounts on your site, huh?
Awesome. User accounts let you do so much more with a site, but they're also one of the most easily-exploited pieces of most websites. While most people familiar with a web language could whip up a user login system, writing one that can't be easily compromised by hackers, script kiddies, or a banned user with a grudge is far more difficult. This guide will walk you through the concepts of how to build a system that can be as loose or as secure as you want it to be, and is safe for not only your website, but even moreso your website's users. This system will be fairly advanced -- it's similar to what I used on Appulous as well as a handful of other websites. To date, it's not been broken.

Want to build your own login system? This guide is for you.
If you're looking for free code to plug into your website, turn back now. I'm assuming you have a good working knowledge of PHP and MySQL, and the drive to build and test your own system. I'll tell you what cookies to set, what to store in a session variable, what information you can cache, and how to generate secure checksums, but it's up to you to actually know how to program all of that. Sound super scary? Try starting off with something a bit easier, and work your way up to this :).

Let's talk features!
Here's what your new account system will be capable of:
  • Registering new users with or without E-mail verification
  • Logging users into your website with a "Remember Me" function (to allow them to auto-login when they visit your page in the future)
  • Assigning users to groups, with each group having a certain permission set.
  • Allowing you (the admin) to override certain permissions for specific users (to allow, for example, "Frank" -- in the normal member group -- to ban other members)
  • Allowing you to ban:
    • Individual accounts
    • Anyone who tries to sign up with a certain E-mail address (or partial E-mail address)
    • Anyone who logs in with a certain IP address, or with an IP address that falls into a range of banned IPs
  • Allowing users to be "signed in" (have auto-login set up) on a certain number of different computers at one time. You get to specify the number.
  • Allowing users to click a link and type their password to force all of their logged-in computers to log off. Useful for people who accidentally clicked "Remember Me" on a public computer.
  • All passwords are salted and encrypted on the server, so users are safe even if a hacker gets a copy of your database.
  • Cookie theft prevention. If one user steals another's cookies and attempts to hijack their login session ... they can't. And you can detect this and ban their IP if you so wish.
  • And more stuff that I might add to this list later :)

Let's start with the database tables!
You're going to need five (yes, five!) database tables for this bad boy. Here's an outline of each table and its fields:

users
  • id bigint, unsigned, auto-increment PRIMARY KEY
  • username varchar, 32 characters (or however long you want usernames to be) UNIQUE INDEX
  • email varchar, 80 characters (or however long you want the E-mail to be) UNIQUE INDEX
  • group_id bigint, unsigned
  • salt varchar, 5 characters (or however long you want the salt to be)
  • passhash varchar, 32 characters (the length of our hash)
  • perm_override_remove bigint, unsigned
  • perm_override_add bigint, unsigned
  • reg_date datetime, or however else you like to store the date
  • last_login_date datetime, or however else you like to store the date
  • reg_ip unsigned int
  • last_login_ip unsigned int
  • must_validate boolean/tinyint

autologin
  • id bigint, unsigned, auto-increment PRIMARY KEY
  • user_id bigint, unsigned INDEX
  • public_key varchar, 32 characters (or the length of your hash) INDEX
  • private_key varchar, 32 characters (or the length of your hash)
  • created_on datetime, or however else you like to store the date
  • last_used_on datetime, or however else you like to store the date
  • last_used_ip unsigned int

groups
  • id bigint, unsigned, auto-increment PRIMARY KEY
  • group_name varchar, 32 characters or the max length of your group names)
  • permissions bigint, unsigned

forced_group_ips
  • id bigint, unsigned, auto-increment PRIMARY KEY
  • group_id bigint, unsigned
  • ip_low unsigned int INDEX
  • ip_high unsigned int INDEX
  • created_on datetime, or however else you like to store the date
  • notes varchar 255 characters (or enough characters for you to make a quick note on why this IP or IP range is banned)

banned_emails
  • id bigint, unsigned, auto-increment PRIMARY KEY
  • email_regex varchar, 120 characters (or whatever you need -- 120 has been a good number for me)
  • created_on datetime, or however else you like to store the date
  • notes varchar 255 characters (or enough characters for you to make a quick note on why this email is banned)


Explanations for the above fields!
Most of what you see up there is pretty straightforward. But here are a few notes on the parts that might not be:
  • All IP addresses should be stored in integer form. So instead of, say, "194.247.44.146", you would be storing 194*256^3 + 247*256^2 + 44 * 256^1 + 146, which is 3270978706. PHP has native functions that convert between human-readable-IPs and integer IPs, don't worry :) They're ip2long() and long2ip. Not only does this format save space, they let you run comparison operators within mysql -- which will be super useful to determine if an IP falls within a banned range. The highest possible IP address is 255.255.255.255. Convert that and you get 4294967295, which is the maximum value for an unsigned int. Hence, ip fields are unsigned ints.
  • I use unsigned bigints for my 'id' fields, the primary key in each of my mysql tables. Before you criticize that an unsigned bigint is WAY larger than anyone would possibly need and that I'm wasting space, keep in mind that an extremely common attack on login-based sites is to flood the tables in an attempt to expire the available primary keys. An unsigned bigint is unreachable through this method. 4.2 billion for an unsigned int, however, might be attainable if you're a big enough target. Better safe and marginally more disk-heavy than sorry.
  • Never heard of a salt? read this. I'll be using md5 hashing for all the hashes here, which is a bad idea for passwords -- unless you salt them. md5 is a few orders of magnitude faster than other hashing algorithms, so using that is a small but important step to keeping your login system lightweight.
  • You can do permissions in many different ways. Recently, I've switched to a 64-bit binary method, which can be stored in an unsigned bigint. Change those fields appropriately if you'd rather track permissions differently. See the Permissions section below on what these fields are for and how to use them.
  • I am passionate about being able to track, neutralize, and punish trolls, hackers, and banned folks trying to make new names. As such, I attach an IP address and a borderline-paranoid timestamp to every single piece of data where it applies. This allows me to build ridiculously strong autodetection systems for people who shouldn't be on my site, and it's paid off in the past. You can omit many of these fields if you want to, but if you hate the idea of other people winning, then I suggest keeping them :)
  • The autologin table has both public_key and private_key fields. This does not imply encryption ;-). Read on for how that works.

How to use it!
Before you launch:
  • Determine your permissions scheme and write a list of constants that defines which permission is which. For much more detail about this, read the section on Permissions below.
  • Create an entry in the groups table for every group. One for banned users, one for users that must validate their E-mail addresses (if you choose to do that), one for guests, one for normal members, one for moderators, etc. Set their permissions accordingly.
  • Make entries in the banned E-mails table if you so desire. Do yourself a favor and use an honest-to-goodness regex string, like this: .*@mailinator.com (to block mailinator). If you want to make your interface easy and make * the wildcard character there, that's fine -- but remember to convert it to (.*) before inserting into mysql.
  • Throw session_start() at the top of any page (or any controller, if you're using the MVC pattern) that will have any user account information on it. Don't do this to every page indiscriminately -- chances are you don't want to open a session when someone looks at your RSS feed, for example.

When someone registers, you...
  • Check the database for an existing username or e-mail address (using 'like' so it's case insensitive!)
  • Check the E-mail address against every RegEx string in the banned_emails table. If you get a match, throw up an error message.
  • Generate a random five-character (or more) string for the salt
  • Hash the salt, hash the password, and hash them together. Like this: md5(md5($password) . md5($salt)). If you're super awesome, you could detect in both your login and registration form if javascript is enabled, and if it is, md5 the password before sending it to the server. Then it's just md5($passhash . md5($salt)).
  • Convert the user's IP address to an integer with ip2long($_SERVER['REMOTE_ADDR'])
  • Validate form fields (as you always should)
  • Insert it into the database!

Want the registering user to have to verify his E-mail address?
  • Set must_validate to 1.
  • Generate a key. This key should be an md5 hash of the e-mail address and the salted password hash concatenated together. Why these two things? Because if any single one of them changes before the validation link is clicked, you don't want the old validation link to work.
  • Send an E-mail to the user. In this E-mail, you need a link to your login page, which includes the validation code you just generated (in a GET variable or otherwise). Having the user log in to verify the E-mail is absolutely the most secure way of doing E-mail verification -- so read on to the login section for instructions on how to do this.

When someone tries to log in:
  • Look at the username first. Attempt to pull in a row of data with a username 'like' what was submitted. If there is no data, login failed. Otherwise, continue.
  • Using the salt pulled from the database for this user, reconstruct the passhash. Compare it to the passhash pulled from the database. If it doesn't match, login failed. Otherwise, continue.
  • Are you requiring E-mail addresses to be verified? If no, skip these indented steps :). Otherwise:
    • Check to see if must_verify is 1. If not, no verification needed! Jump out of this block and continue :) If so:
    • Check to see if a verification code has been passed in with the URL. No? Login failed, offer to send another E-mail. Otherwise:
    • Reconstruct the verification code with the stuff you pulled from the database and see if it matches the code in the URL. No match? Verification failed, offer to send another E-mail. Otherwise:
    • Change must_verify to 0, change the group id (if appropriate), and continue the usual login process :)
  • Set a session variable named "USER_ID". Believe it or not, the value of this variable should be the id of the user logged in.
  • Paranoia time! Set any additional session variables with things you want to make sure are unchanged from page load to page load. One of these should definitely be the entire user agent string, because if someone logs in with one useragent then loads a page with another useragent, chances are that someone stole their session cookie. Some folks like to check the IP address as well, but note that this can cause problems with some ISPs and mobile devices that change IP addresses between requests.
  • Is "Remember Me" checked? If not, woo we just logged in! Redirect to whatever page you like and have fun :) If it IS checked... continue :)
  • Generate a unique "public" key to be used for the autologin process for this computer. Some folks use the automatically generated PHP session ID, but I like to use something different in case the session manager is changed. Concatenate the E-mail address with the current date and time and md5 it, if you want. The data isn't important -- you just need a long, pseudo-random string that people can't possibly guess.
  • Generate a "private" key based on information that should ALWAYS be the same when this autologin is used. If any piece of data involved in the generation of this string changes, the autologin will fail. So you never want to use the IP address, since that can change constantly for some users. Using the whole useragent string is borderline paranoid, but good. You could extract just the browser type and OS for a less paranoid solution. Either way, ALWAYS INCLUDE THE USER'S CURRENT SALT! So if you're not sure what to do here, concatenate the salt with the user agent, md5 it, and call it a day.
  • Select the count of all rows in the autologin table with the user_id of the current user. If that number is equal to the maximum allowed number of simultaneously logged-in computers (you pick this number), then delete the one with the oldest last_used_on date.
  • Insert a new row into the autologin table. Fill out all the fields with the data we have, but last_used_on and last_used_ip should be NULL.
  • Set a cookie named "publickey" with the user id concatenated with the public key we just generated.
  • Redirect to a new page!

When someone logs out:
  • Unset the session variable USER_ID. Optionally, you can session_destroy(), but note that this will also kill any session data your site sets that's not associated with the user account.
  • Delete the publickey cookie if it exists.
  • Redirect to a new page :)

When someone loads a plain old normal page that you need the user account for:
  • Call session_start(). (You're already doing this, right? Right? :D)
  • Check to see whether the session variable USER_ID exists.
  • USER_ID exists:
    • Check any other information you have saved against the current request. At minimum, you should have the useragent saved in a session variable. Check it against the current useragent. If it's different, logout. Optionally, redirect to an error page. If it's a match, continue.
    • Query all the user data you need from the database, using the User ID in the session variable.
    • Convert the user's IP address to an integer. Run a query against the forced_group_ips table to select a row in which ip_low is less than or equal to the current IP, and ip_high is greater than or equal to the current IP. Join the groups table on the group_id so that you get the group's permissions. If you get a result, use this group's permissions instead of the ones for the user's group and the user's overrides. For more information, see "The Permissions System" below.
    • You're all set, and you should have everything you need :) Be sure to read the section on how to calculate permissions.
  • USER_ID does not exist:
    • Check for the 'publickey' cookie. If it doesn't exist, query the database for the Guest group permissions (or just hardcode these permissions in your app), and you're all set. If the cookie exists, continue:
    • Verify that the cookie is, at minimum, 33 characters. Why? Because if you remember from above, we concatenated the user ID number with the 32-character md5 hash, so a valid cookie should be greater than or equal to 33 characters. If it's less, go to the last step and treat it like this cookie didn't exist. Otherwise, continue.
    • Split the last 32 characters off of the cookie. Now you have the actual public key you generated before, as well as the user ID. Query your autologin table for a row matching this User ID and publickey. If no such row is found, go back and do what you'd do if the cookie didn't even exist. If a row WAS found, continue:
    • Run the exact same function you used to generate the private key back when you created it the first time. Compare the result with the private key you got from the database. If they don't match, you know what to do ... go back and treat the current user as a guest. If they DO match, continue:
    • At this point, treat the user as though they just submitted a correct username and password. Set the USER_ID session variable, set the useragent session variable (and any others that you might be using to make sure there's no session-thievery going on), update the last_login_ip and last_login_date in the user table, update the last_used_on and last_used_ip on the autologin row that you found, etc.
    • Convert the user's IP address to an integer. Run a query against the forced_group_ips table to select a row in which ip_low is less than or equal to the current IP, and ip_high is greater than or equal to the current IP. Join the groups table on the group_id so that you get the group's permissions. If you get a result, use this group's permissions instead of the ones for the user's group and the user's overrides. For more information, see "The Permissions System" below.
    • Load the requested page as usual :). The user is now logged in. Be sure to read through the section on how to calculate permissions!

To ban/inhibit an IP or IP range:
  • Decide on a group to force on a user matching the IP(s). Usually this is either a "Banned" group (with few or no permissions), or a group that at least prevents some sort of abuse-- a "read-only" group, a group that has no search permissions, etc.
  • Convert the IP address you want to ban into an integer with ip2long(). If you want to ban a range, convert the lowest and highest IP addresses to integers.
  • Insert a new row into the forced_group_ips table. Use the group ID you selected, and set ip_low to the lower-boundary IP in the range, and ip_high to the upper boundary. If you're banning a single IP address, set the low and high to that same address.
  • Now you can check to see if an IP is banned by converting it to an integer and running a query to find any rows in the table with ip_low <= the IP in question, AND ip_high >= the IP in question. No need to do separate searches to see if it falls within a range or anything like that. If you get a result from this query, the IP address is in the table.

When someone forgets their username or password:
  • Ask for their E-mail address and E-mail address only. Keep asking until you get an E-mail that matches one of them in the database. When you get a match, retrieve that user's information.
  • Construct a hash with any information you need to remain exactly the same between the time the user requests their username/password, and the time they click the Password Reset link. I recommend concatenating the user ID, username, E-mail address, and salt, then md5'ing that.
  • Send an E-mail to the address, giving the username (no need to protect that) and a link to a page that allows the user to set a new password. Include the user's ID as well as the hash as part of this URL, as a GET variables or otherwise.
  • When the user loads the password reset page and attempts to submit a new password:
  • Load the information for the user ID in the URL.
  • Reconstruct the above hash, verify it matches the hash in the URL. If not, error out. If so, continue:
  • Generate a new salt. This will prevent any computers set to auto-login from.. well, auto-logging-in. When you're changing the password because it's been forgotten, this is always a good idea.
  • Generate a new salted passhash (like you did during registration), and update the row in the users table with this and the new salt.
  • Your call on whether to log the user in at this point, or redirect them to a login page on which to use their new password.

To allow a user to cancel any autologins on other computers:
  • Just ask for the user's password. When you get it, verify that it's the real, working password.
  • Generate a new salt.
  • Generate a new passhash using the new salt.
  • Update the user's row in the user table with the new salt and passhash.


The Permissions System
How it works
Each member of your site is assigned to a "group", and each group has a set of permissions assigned to it. 99% of the time, that's all you'll need. But there are those few rare cases where you need to give special permissions to just one user, or take one away. It's a waste to create a whole new group just for one person every time this scenario comes up, so this permissions system allows you to assign "overrides" to specific users.

Each user has a perm_override_remove and perm_override_add column, where you specify which permissions you want to either take away or add to the permissions from this user's group. Now, each time a user loads a page, we get their group permissions, removed permissions, and added permissions, and mash them all together to find that specific user's permission set.

How permissions are stored
In this system, each permission field is a 64-bit number. For the uninitiated, a 'bit' is a 1 or a 0 -- on or off. They can be combined to make bigger numbers. 0100, for example, is four. 0101 is five. If you're extremely interested in how binary works, Google has much to teach :). For now, just know that you have 64 on/off switches for your permissions -- and if you need more, you can use a bigger bigint ;-).

So what you need to do is come up with a list of simple on/off permissions. Be very small and specific with these-- so rather than IS_MODERATOR, for example, a forum might use DELETE_OWN_POSTS, DELETE_OTHERS_POSTS, POST_NEW_THREADS, POST_NEW_REPLIES, READ_THREADS, etc. Once you have that list, write a very simple PHP class that lists each permission as a constant, equal to a single bit (1, 2, 4, 8, 16, 32, 64, 128, 256, and keep multiplying by 2 from there). Here's a very quick example:

<?php

class UserPermissions {

	const READ_POSTS = 1;
	const POST_NEW_THREADS = 2;
	const POST_NEW_REPLIES = 4;
	const EDIT_OWN_POSTS = 8;
	const EDIT_OTHERS_POSTS = 16;
	const DELETE_OWN_POSTS = 32;
	const DELETE_OTHERS_POSTS = 64;
	const MOVE_THREADS = 128;
	const SPLIT_THREADS = 256;
	const MERGE_THREADS = 512;
	const BAN_USERS = 1024;
	const WARN_USERS = 2048;
	const ACCESS_ADMIN_PANEL = 4096;
	// And so on and so on
	
	protected $perms;
	
	function __construct($permissions) {
		$this->perms = $permissions;
	}
	
	function hasPermission($perm) {
		return ($this->perms & $perm) === $perm;
	}
}

?>

There are three main elements, here. You have the list of constants, which I explained above. Keep multiplying the value by two for each new constant. The significance of those numbers is that they can all be represented with a single "on" bit. 1 is 1. 2 is 10. 4 is 100. 8 is 1000. 16 is 10000. 32 is 100000 -- and so on. This just tells our system which bit of that huge 64-bit number belongs to which permission.

Followed by the constants is the constructor. This function just lets us say $perms = new UserPermissions($permissionFromDB); to get a new UserPermissions object. And the whole reason it helps to have a new object like that is because of...

The next function, hasPermissions, checks the stored permission set against one of our constants. So to see if a user can edit his own post, it's just $perms->hasPermission(UserPermissions::EDIT_OWN_POST); -- and you'll get back true or false. For the record, that function uses a "bitwise AND" to figure that out. It will help if you take a minute to google and learn what that is :)

How to calculate permissions overrides
So you have your fancy UserPermissions class above, and you can plug group permissions into that and it'll work. But now you have to make the overrides assigned to each individual user work.

In these examples, I'm going to take the permissions down to a 4-bit binary number like this: b0110. In fact, let's say our group's permissions are b0110 (which is 6). If we want to give that last permission (whatever it might be) to a specific user, we'd set that user's perm_override_add to b0001 (which is 1). Then the system needs to know to add any "on" bits in that to the group permissions we already have, to make it b0111.

But let's say, for the same user, we want to take away that first 1. We'd set their perm_override_remove to b0100. Then the system should know to take any "on" bits from that, and turn them OFF in the group permissions. Still following me? So our group permissions: b0111 minus the perm_override_remove of b0100 equals b0011.

But you'll run into a ton of issues if you try to use simple addition and subtraction like that. Adding b0001 and b0010 will result in b0011, and that's great. But what if you have a group permission of b0010 and you try to add the same bit in your override -- so another b0010? You'd end up with b0100, which isn't right at all! So instead of minus and plus, we use bitwise logic -- OR, AND, and XOR.

Again, I defer to google if you want to learn what each of those things are -- and you should ;-). But just so you don't have to sit down and rip out your hair trying to figure out the combination of AND and OR and all that junk to use, here's a handy dandy instruction sheet:

Added Permissions = Group OR perm_override_add
Permissions to Remove = Added Permissions AND perm_override_remove
Final User Permissions = Permissions to Remove XOR Added Permissions


Now you just need to build that logic into the constructor for your UserPermissions class :D

How to set a permission
You'll want to add another function to your UserPermissions class-- this one, setPermission, should take an integer (a permissions constant) and add it to the permissions number. But remember what we covered above! You can't just "add" it, because if you try to "add" (as in plus sign) a permission that's already on, you're going to be screwing up the permissions block. Instead, you want to set $perms equal to $perms OR the permission. That way, nothing changes if the permission is already set, and it's turned on if not :). Note that if you're using my above code, I have $perms "protected" so that it can't be edited directly. You'll want to add a function called getPermissions to get that number back so you can insert it into the database when necessary.


Extend it!
This system can do so much more. Building a forum? You'll want to have permissions per forum -- you can build another set of permissions overrides into each specific forum. Want people to be able to belong to multiple groups? Throw in another database table that links up user_ids to group_ids, then you can select them all and OR together the permissions. Need more permissions than just 64? Use an unsigned INT(10) for the permission fields, or higher!

This system will work for most types of sites, but the same basic idea can be used no matter how you need to customize it.


Enjoy!
And good luck :)

#2
Sephern

Sephern
  • Moderators
  • 1051 posts
  • Joined: 04-June 10
  • LocationLeeds, UK
  • Expertise:HTML, CSS, PHP, Javascript, Python
This looks like it'll be really, really useful.

Horrible place to put a "To be continued..." though. D;

#3
Cheddam

Cheddam
  • Members
  • 191 posts
  • Joined: 01-April 10
  • LocationNew Zealand
  • Expertise:HTML, CSS, PHP, Graphics
I agree, looking forward to the ACTUAL tutorial haha.

Me


#4
Mack

Mack
  • Members
  • 2090 posts
  • Joined: 08-March 10
Hmm, I have my site store 2 cookies. One is a random 32 character string to lookup their information, and the other is another random string hashed together with their username and IP.

Is that secure enough? The password isn't stored in the cookie at all

#5
Daniel15

Daniel15

    dan.cx

  • Moderators
  • 3689 posts
  • Joined: 17-April 10
  • LocationMountain View, CA
  • Expertise:HTML, CSS, PHP, Java, Javascript, Node.js, SQL
Looking forward to the actual tutorial! xD

One is a random 32 character string to lookup their information

That's a session ID and PHP already does that with sessions. :P

and the other is another random string hashed together with their username and IP.

Some users will be on a dynamic IP address that could change between requests (I've seen it happen). IMO a better approach (if you want a hash like that) is a hash of the username and user-agent. The user isn't going to change browser in the middle of browsing the site, and a user would have to guess what browser they're using to hijack their session :P

Having said that, a session ID is normally fine, the extra hash isn't needed in most situations.
Daniel15! :D
Posted Image

Repeat after me: jQuery is not JavaScript. It is not the answer to every JavaScript-related question. When you have to write some JavaScript, do not instantly react with "Oh, I'll do that with jQuery!"

Spoiler

#6
Mack

Mack
  • Members
  • 2090 posts
  • Joined: 08-March 10

Looking forward to the actual tutorial! xD


That's a session ID and PHP already does that with sessions. :P


Some users will be on a dynamic IP address that could change between requests (I've seen it happen). IMO a better approach (if you want a hash like that) is a hash of the username and user-agent. The user isn't going to change browser in the middle of browsing the site, and a user would have to guess what browser they're using to hijack their session :P

Having said that, a session ID is normally fine, the extra hash isn't needed in most situations.


No, the 32 character ID is generated at signup :P I keep it in the database and make a cookie called user with that in it, not the PHP one :P

Since they're cookies, the hijacker would have to know the browser to get the cookie, wouldn't they?

#7
Nico

Nico
  • Members
  • 263 posts
  • Joined: 09-March 10
  • LocationSpain
  • Expertise:HTML, CSS, PHP, Javascript, SQL
This is going to be very insightful. :)

If you're super awesome, you could detect in both your login and registration form if javascript is enabled, and if it is, md5 the password before sending it to the server.


I like this!

I actually posted a little something about this not too long ago on my blog. :)

http://nic0.me/post/...g-before-log-in

#8
Kyek

Kyek

    Founder of wdR

  • Administrators
  • 5371 posts
  • Joined: 20-February 10
  • LocationPhiladelphia, PA, USA
  • Expertise:HTML, CSS, PHP, Java, Javascript, Node.js, SQL
Ok guys, I think it's done :) Let me know if I left anything out, or didn't explain something well enough.

#9
Daniel15

Daniel15

    dan.cx

  • Moderators
  • 3689 posts
  • Joined: 17-April 10
  • LocationMountain View, CA
  • Expertise:HTML, CSS, PHP, Java, Javascript, Node.js, SQL
Had a quick skim (don't have time to read it fully at the moment :(, but it looks pretty good! :)

All IP addresses should be stored in integer form. So instead of, say, "194.247.44.146", you would be storing 194*256^3 + 247*256^2 + 44 * 256^1 + 146, which is 3270978706. PHP has native functions that convert between human-readable-IPs and integer IPs, don't worry They're ip2long() and long2ip. Not only does this format save space, they let you run comparison operators within mysql -- which will be super useful to determine if an IP falls within a banned range.

That doesn't support IPv6! D:
MySQL has functions for that too, INET_ATON() and INET_NTOA(). So you could do the conversion in MySQL if you reeeeeeally wanted to. :P

For the permissions stuff, I love the idea of using a bitmask. Another approach is to use a MySQL SET data type instead. It use a bitmask internally (just as you've done), but uses comma separated values. The column has a SET type:
permissions SET('admin', 'read_posts', 'post_new_threads', 'post_new_replies', ...) NOT NULL 

And when you do a SELECT, you get them back as a comma separated list (or a numeric value where each bit is a different value, by converting it to a number). The main advantage of this is you can do lookups nicely:
-- Search for admins
SELECT * FROM users WHERE FIND_IN_SET('admin', permissions)
-- This also works, assuming 'admin' is the first value in your SET(...) type
SELECT * FROM users WHERE permissions & 1
And you can set them like so:
-- Using names
UPDATE users SET permissions = 'admin,read_posts' WHERE username = 'Daniel15';
-- Using values, 3 == admin + read_posts
UPDATE users SET permissions = 3 WHERE username = 'Daniel15'

This approach would involve either doing all permissions stuff in the database, or keeping a PHP class in sync with the list of permissions on the data type of the database field, so Kyek's approach is better. I just thought I'd write about this. :P

ote that the 'const' keyword is PHP 5.3+ only -- change those to normal veriables if you're on an earlier version:

const is available on earlier versions (I'm using it on 5.2), what was introduced in PHP 5.3 was using const outside of classes instead of define(). :)
Daniel15! :D
Posted Image

Repeat after me: jQuery is not JavaScript. It is not the answer to every JavaScript-related question. When you have to write some JavaScript, do not instantly react with "Oh, I'll do that with jQuery!"

Spoiler

#10
Kyek

Kyek

    Founder of wdR

  • Administrators
  • 5371 posts
  • Joined: 20-February 10
  • LocationPhiladelphia, PA, USA
  • Expertise:HTML, CSS, PHP, Java, Javascript, Node.js, SQL

That doesn't support IPv6! D:

osht! Ha, that never actually occurred to me because I've never been on a server that supported it. I suppose I'll update this a bit once IPv6 gains a little more prevalence :)

This approach would involve [...] keeping a PHP class in sync with the list of permissions

That's the entire reason I don't use SET :). That's such an absurdly cool feature, but I always seem to run into the issue where I have to add a permission at a later date. In AppDB, since there weren't a ton of permissions to begin with, I tried the approach of having a bunch of TINYINT fields so I could just tack on a column every time I needed a new permission, without transforming an existing field. But every time, it comes back to needing to keep PHP code in sync with database details, which is an enormous pain. Handling the bits on the PHP side lets you add permissions without ever informing the database :) The other benefit is that, since not all SQLs support SET, this makes code more portable as well.

const is available on earlier versions (I'm using it on 5.2), what was introduced in PHP 5.3 was using const outside of classes instead of define(). :)

Holy crap, I had no idea! Thanks for the heads up-- updating the guide now :)

#11
Mack

Mack
  • Members
  • 2090 posts
  • Joined: 08-March 10

osht! Ha, that never actually occurred to me because I've never been on a server that supported it. I suppose I'll update this a bit once IPv6 gains a little more prevalence :)


That's the entire reason I don't use SET :). That's such an absurdly cool feature, but I always seem to run into the issue where I have to add a permission at a later date. In AppDB, since there weren't a ton of permissions to begin with, I tried the approach of having a bunch of TINYINT fields so I could just tack on a column every time I needed a new permission, without transforming an existing field. But every time, it comes back to needing to keep PHP code in sync with database details, which is an enormous pain. Handling the bits on the PHP side lets you add permissions without ever informing the database :) The other benefit is that, since not all SQLs support SET, this makes code more portable as well.


Holy crap, I had no idea! Thanks for the heads up-- updating the guide now :)


I use IPv6 :o

#12
Quinn

Quinn

    More pew-pew, less QQ

  • Members
  • 1307 posts
  • Joined: 08-March 10
  • LocationPalmyra, PA, USA
  • Expertise:HTML, PHP, Javascript
I've been missing so much in my little user databases. Thanks Kyek.
<Imp> [F3ar 40]  [PWNbear 17]  [magik 15]  [dissident 10]  [mark 7]

Though anyone who thinks Europe is a country should be smacked in the face. By a train.


#13
Mack

Mack
  • Members
  • 2090 posts
  • Joined: 08-March 10
Hmm, in my databases I have the auto login stuff in the users table

#14
Daniel15

Daniel15

    dan.cx

  • Moderators
  • 3689 posts
  • Joined: 17-April 10
  • LocationMountain View, CA
  • Expertise:HTML, CSS, PHP, Java, Javascript, Node.js, SQL
Does anyone have a server that supports IPv6? Mine doesn't :(

In AppDB, since there weren't a ton of permissions to begin with, I tried the approach of having a bunch of TINYINT fields so I could just tack on a column every time I needed a new permission, without transforming an existing field.

Mmm, that's also a good approach if there's only a few permissions. Kohana's in-built user system has a roles table that contains the roles ("login" and "admin" by default), and a roles_users table (association table, relation table, whatever you call it) to map the relationships. Adding those relationships is really easy in its ORM system:
// Add role login (1) and admin (2) to the user with ID 123 
$user = ORM::factory('user', 123);
$user->roles = array(1, 2);
$user->save();

Although you'd normally NOT use magic numbers (the "1" and "2"), or constants (as it'd involve keeping PHP in sync with the DB once more), as they don't really fit in with ORM. Instead you'd probably do something like:
// Add login and admin roles to user with ID 123
$user = ORM::factory('user', 123);
$user->add(ORM::factory('role', 'login'));
$user->add(ORM::factory('role', 'admin'));
$user->save();
But, of course, this adds two extra database queries. It's not really significant if you're using caching, anyways. You could even cache the full list of roles and use that to get the role IDs. :P
Daniel15! :D
Posted Image

Repeat after me: jQuery is not JavaScript. It is not the answer to every JavaScript-related question. When you have to write some JavaScript, do not instantly react with "Oh, I'll do that with jQuery!"

Spoiler

#15
KiNG

KiNG
  • Members
  • 637 posts
  • Joined: 22-May 10
  • LocationTehran, Iran
  • Expertise:HTML, CSS, PHP, Javascript, SQL, Flash

Does anyone have a server that supports IPv6? Mine doesn't :(


What do you guys mean of supporting IPv6?
I think that this should be an OS related feature...am I right?
I've Windows Server 2008r2 and CentOS servers and I think they should support that!
Kind Regards,
Shahab [KiNG]


مهاجرت به کانادا - طراحی سایت


I Love Iran

* I really rarely visit wdR these days. If you want to contact me, send me a pm.

* Are you curious on what is :-" ?! Here it is: Posted Image

#16
Daniel15

Daniel15

    dan.cx

  • Moderators
  • 3689 posts
  • Joined: 17-April 10
  • LocationMountain View, CA
  • Expertise:HTML, CSS, PHP, Java, Javascript, Node.js, SQL
What I meant to say was does anyone have a server in a data centre that supports IPv6. I don't think HiVelocity or PhotonVPS offer IPv6 addresses. I could use a tunnel, but I can't be bothered setting that up :P

You're definitely right, it's supported by most OSes these days :)
Daniel15! :D
Posted Image

Repeat after me: jQuery is not JavaScript. It is not the answer to every JavaScript-related question. When you have to write some JavaScript, do not instantly react with "Oh, I'll do that with jQuery!"

Spoiler

#17
KiNG

KiNG
  • Members
  • 637 posts
  • Joined: 22-May 10
  • LocationTehran, Iran
  • Expertise:HTML, CSS, PHP, Javascript, SQL, Flash
Aha! I got it!
I'm not sure about hostgator, but I think iWeb (my CentOS server) datacenter should support that!
I'm not sure at all! Posted Image
I really don't neeed that! Posted Image
Kind Regards,
Shahab [KiNG]


مهاجرت به کانادا - طراحی سایت


I Love Iran

* I really rarely visit wdR these days. If you want to contact me, send me a pm.

* Are you curious on what is :-" ?! Here it is: Posted Image

#18
Kyek

Kyek

    Founder of wdR

  • Administrators
  • 5371 posts
  • Joined: 20-February 10
  • LocationPhiladelphia, PA, USA
  • Expertise:HTML, CSS, PHP, Java, Javascript, Node.js, SQL

// Add role login (1) and admin (2) to the user with ID 123 
$user = ORM::factory('user', 123);
$user->roles = array(1, 2);
$user->save();

// Add login and admin roles to user with ID 123
$user = ORM::factory('user', 123);
$user->add(ORM::factory('role', 'login'));
$user->add(ORM::factory('role', 'admin'));
$user->save();

But, of course, this adds two extra database queries.

THIS is what kills me about traditional ORMs. That should totally be 3 queries, not 4 ;-)

#19
Daniel15

Daniel15

    dan.cx

  • Moderators
  • 3689 posts
  • Joined: 17-April 10
  • LocationMountain View, CA
  • Expertise:HTML, CSS, PHP, Java, Javascript, Node.js, SQL
Well, I tried. First I chat to technical support:

Please wait for a site operator to respond.
You are now chatting with 'Karl'
Karl: Hello, welcome to HiVelocity's Impressive Support™. How may I assist you today?
Daniel: Hello, I was wondering if you offer IPv6?
Karl: We request you to please contact our Sales Department regarding this
Daniel: Okay, thank you.
Karl: they will update you accordingly
Daniel: Alright, I'll do that. Thank you :)

:huh: So I tried a sales live chat instead:

Please wait for a site operator to respond.
You are now chatting with 'Karl'
Karl: Hello. Welcome to our live chat service. How may I assist you?
Daniel: I assume you're the same Karl I was talking to before in tech support and that sales is not available at the moment? :P
Karl: yes, you are right..


Doh :lol:. Their support is 24-hours but sales isn't. He said he'd forward the query to sales and get them to email me about it. I guess I'll wait and see.

Anyways, back on topic. :P
Anyone used a system like this user system on their site? I feel OpenID support would be a good improvement. I implemented OpenID on a site once, I used Plaxo's A Recipe for OpenID-Enabling Your Site guide and implemented it that way (one user can have multiple OpenIDs) :)
Daniel15! :D
Posted Image

Repeat after me: jQuery is not JavaScript. It is not the answer to every JavaScript-related question. When you have to write some JavaScript, do not instantly react with "Oh, I'll do that with jQuery!"

Spoiler

#20
Mack

Mack
  • Members
  • 2090 posts
  • Joined: 08-March 10

What I meant to say was does anyone have a server in a data centre that supports IPv6. I don't think HiVelocity or PhotonVPS offer IPv6 addresses. I could use a tunnel, but I can't be bothered setting that up :P

You're definitely right, it's supported by most OSes these days :)


My server does, but its not in a datacenter. I'm currently using a tunnel to get my IPv6, and suggest you do too if you're looking for one. Hurricane Electric offers a free static IPv6 tunnel, which is what I use. (http://ipv6.mackgoodstein.com/)


Anyways, back on topic. :P
Anyone used a system like this user system on their site? I feel OpenID support would be a good improvement. I implemented OpenID on a site once, I used Plaxo's A Recipe for OpenID-Enabling Your Site guide and implemented it that way (one user can have multiple OpenIDs) :)


I've used that before, but now I just stick to local users. Never really considered it worth it, but maybe I'll look into that again




2 user(s) are reading this topic

0 members, 2 guests, 0 anonymous users