Registration that Needs Activation via Email

CREATE TABLE Member
(
    memberID    int IDENTITY (2480,39) PRIMARY KEY,
    memberName  nvarchar(70) NOT NULL,
    password    nvarchar(30) NOT NULL,
    eMail       nvarchar(100) NOT NULL,
    notify      bit NOT NULL,
    isActive    bit NOT NULL,
    lastLogin   datetime DEFAULT GetDate(),
    dateCreated datetime DEFAULT GetDate() NOT NULL
);

Once user fill registration form that consists of:

memberName, password, email and check/uncheck notify box then click submit. The values will be stored in the above table with isActive equals to False.

Email will be sent to user for activation, once activated, isActive will equal to true.

1. How can i send an activation link to the email and then change isActive to True once link is clicked? I tried to solve it, i just didn't get it yet. Can you help please.

2. Do i have to add a new colum开发者_C百科n/change schema to achieve step 1. If so, please advise.

---

One simple way is to have a GUID activation code in this table, and include that activation code in the email (either as a query string parameter in the link, or have the user copy/paste when they get to the site).

When that GUID is posted, then you know the email was received, and you can activate the user.

---

Basically we don't want to add fields in the table that are only used once, therefore we will reuse the fields already present.

I made an activation system some years ago where I created a "guid" key to put in the password field (to save numbers of fields in the database). I created the key by concatenating some random numbers, date and time. A normal GUID key should do the trick as well.

When you create a new account, the LastLogin field will be empty (NULL) because the user have not logged in yet. I used this to indicate that the password field held a key instead of a password.

The mail that was sent out had a link to a confirmation page where the user had to set a new password, hence overwrite the "key" and login (setting the last date). The right account to update was of course found with the key in the mail-link, matching the key in the password field.

<a href="www.MyDomain.com/confirm.php?key=65351368571357316687341763">Confirm</a>

To spice it up a more I used CreateDate to verify that the registration link hadn't expired. If the user tried to use the link 48 hours after the creation date, the account expired. I could have used the date & time in the key to check this, but then a user potentially could inject another date & time.

---

If you would like to use your table as defined here without changing the schema or setting up special rules while still remaining secure, you may consider using some form of encryption to identify the user.

I would take 2 or 3 of the fields - memberID, dateCreated, and perhaps eMail and concatenate them together with a random secret key and use an encryption algorithm like RSA or 3DES to encrypt the whole string and pass it as a parameter in the link you include in the activation email. The link should point to your server side script which will authenticate anyone that passes a valid encrypted activation string.

The script should decrypt the passed string and then parse out memberID and secret key, and any other fields you include (e.g. dateCreated, eMail) and use this data to determine a valid user. Then just set isActive = 1 for your user.