Forum Moderators: open
I have two questions!
First here is what PHPMyAdmin gave to me as a query after creating a MySQL table for my database...
SQL Query
CREATE TABLE `users` (
`userid` MEDIUMINT( 16 ) NOT NULL ,
`username` VARCHAR( 16 ) NOT NULL ,
`password` VARCHAR( 16 ) NOT NULL ,
`email` VARCHAR( 64 ) NOT NULL ,
`namefirst` VARCHAR( 16 ) NOT NULL ,
`namelast` VARCHAR( 32 ) NOT NULL ,
`audio` TINYINT( 1 ) NOT NULL ,
`bandwidth` TINYINT( 1 ) NOT NULL ,
`dhtml` TINYINT( 1 ) NOT NULL ,
`dtd` TINYINT( 1 ) NOT NULL ,
`ieccss` TINYINT( 1 ) NOT NULL ,
`powerkeys` TINYINT( 1 ) NOT NULL ,
`theme` VARCHAR( 16 ) NOT NULL
) ENGINE = MYISAM ;
Now I'm not even really good with PHP so I'm just trying to get this to function locally without problems and then eventually test it on some other servers (other issues pending such as my second question).
I'm going off of Larry Ullman's PHP and MySQL book and I'm looking at chapter 4 page 131 where he has type but not a length in an ASCII table example. I'm assuming because the numbers represent how many characters in each field I'd use are the length values and I pretty much matched them. If I'm wrong about that please point me out.
Ok so I'm going to try and talk developer (design is my first language but I'm trying!) but giving a few brief examples of how I will use these as variables in PHP. That way whoever is reading this will have an idea if I should change anything or leave the table as it is.
$userid - I figure when they register it will start with a value of either 0 or 1. I would like to start it with 1 instead and add up from there incrementing by 1. That is probably a PHP script issue if anything but this is what I have...
`userid` MEDIUMINT( 16 ) NOT NULL ,
username Four to 16 characters in length (no more or no less.
`username` VARCHAR( 16 ) NOT NULL ,
password
Do I need to do anything special? I know it's a little early for security suggestions in general but I'm open to suggestions on this column (this is a user's column right)?
`password` VARCHAR( 16 ) NOT NULL ,
audio
These will only be 0, 1, or on occasion 2, 3, or even the value of 4. They are mostly yes or no options (like what version of XHTML doctype should be served, three versions in example). I figure this only requires one number and INT on TINYINT stands for integer so it seems like the most logical choice from my perspective.
`audio` TINYINT( 1 ) NOT NULL ,
theme
I plan on inserting values like "classic", "cityblue" and other short alpha only values.
`theme` VARCHAR( 16 ) NOT NULL
I'm just looking for suggestions on my setup for how I will be using the database.
I'm using PHPMyAdmin if I haven't mentioned it and I'm getting pretty used to getting around and modifying types, fieldnames and etcetera.
Secondly XAMPP setups up fresh installs without passwords, oh joy just what I need when I can see people scanning my ports for goodness sakes! (router is closed, works only internally on a wired only network)
Anyway it's saying I don't have any root password set, how the heck do I set one? It's a complete noob question but we all have to start somewhere (if we start at all that is!)
Thanks for any replies!
- John
$userid - I figure when they register it will start with a value of either 0 or 1. I would like to start it with 1 instead and add up from there incrementing by 1. That is probably a PHP script issue if anything but this is what I have...`userid` MEDIUMINT( 16 ) NOT NULL ,username Four to 16 characters in length (no more or no less.
`username` VARCHAR( 16 ) NOT NULL ,
Are you intending for your users to log in to your site with the userid and password, or the username and password, or can they do either?
If they can't do both, what is the purpose of holding both a userid and a username?
Is the intention for usernames to be unique? If so, username ought to be the primary key on this table and you could ditch userid completely...
password
Do I need to do anything special? I know it's a little early for security suggestions in general but I'm open to suggestions on this column (this is a user's column right)?
`password` VARCHAR( 16 ) NOT NULL ,
I would make the password field CHAR(32) and store an MD5 hash of the password. Sites that can send me my password make me uneasy. The password should not be stored unencrypted to start with. On login take the password, hash it and compare against the stored hash. If somebody compromises the database at least they don't have immediate access to every password.
I would make the password field CHAR(32) and store an MD5 hash of the password
If you're going to store a password hash, at least use SHA1 - cryptographers seem to have serious worries about MD5 these days...
Before you write you authentication code, you might like to read up on challenge/response - perhaps RFC 2195 [faqs.org] might be a good place to start?
It would be cool to allow visitors to use their userid or username. I suppose I could force user names to start with a letter and detect based on the first letter which row in the database to compare with.
I don't know squat about hash functions though it looks like something I should start reading. I assume at the moment though that once I make a choice those who have their passwords stored would not be able to be "re-hashed"? Thanks again for the replies.
- John
It would be cool to allow visitors to use their userid or username.
IMHO it would cool for you to give your visitors ONE unique identifier.
What's the reason behind having both a userid and a username? Can you think of other sites that do this? (I can't...)
I assume at the moment though that once I make a choice those who have their passwords stored would not be able to be "re-hashed"?
Once you've hashed a value, it stays hashed (at least you hope it does - look up 'Rainbow Tables' if you want to know a good way to crack hashes). So you'd better get your authentication code right first time or you're going to inconvenience your visitors if you change it later.
I have to ask - most of this kind of stuff has been done before many many times over. Are you sure you aren't about to reinvent the wheel? Why not pick an open-source project you like that does all this and adapt the code they use?
- John
