Vital MySQL data fields for user account registration?

[Ultimater]

I am developing a user account registration form for users to create an account on a clan website. However I want to validate their email address as well as prevent automated registrations. So far I already used PHP to create a dynamic image http://clanhunter.frih.net/imagegenerator.php and have yet to log the random confirmation code created by it.
The user fills out the form which looks like this:
http://clanhunter.frih.net/signup.htm
Upon submit, PHP will accept 5 parameters:
username, password, confirmpassword, email, confirmationcode
However I need to first look up the confirmation code in the database.
Then I'd need another database table for confirming email addresses.
Finally after the confirmation code and email address are verified, I need to log it in the database. I'm kind wondering what the database tables should look like and what kind of extra info to log e.g. ip addresses.

[chazzy]

is the confirmation code = the image's word? if so, that shouldn't be in a database (i think) unless the image's value is going to be something like an id and you're going to check the count of id='id passed in' and value='value passed in'.

why do you need a separate table for possible email addresses? have you seen bokeh's sig... he has an email validator script that uses hosts - no db required.

and I don't think anyone here can dictate to you what you should require in teh database - if it's up to me, i would keep IP, host name, and a timestamp i don't see any use for anything else. maybe the referrer as well.

Do you program stored procedures - that could help a lot w/ the calls in php. then again if you're not using php5 and/or mysql5 it might not help as much.

[Ultimater]

Yes, the confirmation code = the image's word. And no I'm not worried about the actual coding at this point, I can do all that on my own.

I'm afraid that someone might run imagegenerator.php a few million or billion times in one day via a few http requesters so as to completely occupy most-all 16.7 million possible session combinations which will log into the database. Then they can assume that most every session id from 000000 to ffffff will be in the database and then they can start another http requester to start registering as many user names as they want each using a different session id starting from 000000 until they reach ffffff and totally avoiding having to read the image display generated by the generator and ending up having out-smarted the automated registration.

How can I prevent users from doing this? Not so much that I'm afraid of people actually doing this... but it's a sign that it is a weak system.

Also if sessions created by imagegenerator.php don't expire within a period of time, all the sessions will add-up over a longterm period of time and occupy the entrie database again 'til the point someone can just bypass the automated security by guessing session ids...

Obviously:
1. The sessions ids need to expire within a certain period of time. 24 hours should be plenty of time.
2. Need to make sure the user types in the session id shown on THEIR page not someone's session id.
3. Need to remove validated and old session ids from the database

How would the system work? Nextly, what would the database table or tables look like?

[Ultimater]

Pretty old thread but I return in order to post the solution I came up with.
The user will enter signup.php into their addressbar and a fill-in form will display in front of them with a submit button. One of the fields will be a text field named "ccode" and the user will need to read the image word next to it and fill in the "ccode" field.

The flow I came up with goes as follows:
1. User types signup.php into their addressbar and PHP gets to work
2. PHP generates a new unique "uuid" (Universally Unique Identifier) hexadecimal string and a new random "ccode" (Confirmation Code) hexadecimal string and inserts 'em into MySQL.

registerpage_sessions

Field Type Null Key Default Extra
id int(25) unsigned NO PRI auto_increment
uuid text NO
ccode text NO
time int(25) unsigned NO
remoteip text YES
proxyuser text YES
referrer text YES

3. PHP returns a FORM for the user to fill out.

...
<input type="hidden" name="uuid" value="<? echo $uuid; ?>">
...
<img src="imagegenerator.php?uuid=<? echo $uuid; ?>">

4. PHP would stop running and return to the cleint the HTML. Once the client renders the HTML, the IMG SRC will be linked to PHP with $_GET['uuid'] and PHP will look up in the database for the matching ccode for the uuid and stop running and return the "ccode" as the image word to the client so they can read the image word.
5. The user reads the image word and fills out the rest of the form and submits the form and puts PHP to work again
6. PHP looks up in the database for the uuid and pulls up the ccode in the database and validates that the ccode entered by the user matches the one in the database for the given uuid.
7. validate all form fields and make sure the username the user wants doesn't already exist.
8. delete the ccode and uuid combination in the "registerpage_sessions" table and create a new account for the user however in the "VALIDATING" state.
9. obtain the userid of the new validating user's account and create a new record in the "email_validation" table before dispatching an email.
Set "email_to_confirm" to $_POST['email'], and email_hash will be randomly generated

email_validation

Field Type Null Key Default Extra
email_id int(25) unsigned NO PRI auto_increment
email_hash text NO
email_to_confirm text NO
user_id int(25) unsigned NO
time int(25) unsigned NO 0

10. Send an email to $_POST['email'] and within the email give them the "email_hash" and a link so they can call PHP again with the "email_hash" so PHP can search the "email_validation" table for the "email_hash" and then it would be able to verify that this is so-and-so's email address and it would continue by editting the user's account "user_id" and fill-in the email they claimed "email_to_confirm" and then the record in the "email_validation" table can be deleted.