Auto generate link for newly registered user

Question

I want to make auto generated link for newly created

user that mywebapp will send to the user's email. When

the user clicks the link i should update the user as

'activated'.

So I've got some questions and I want to understand if

from what I've read so far my logic is right.

1. user fills in a form and press enter

2.My web service process the data and create an account......

So is here the part where i hash the typed password with some unique salt and store it in the database.

so i will have a table like

user_ID   username         password                 active 

1           taniamm20     12346645556665566666       0

can i use this hashed password in the verification link for example

http://localhost:9092/localbusscat/services/localbusscat/UpdateDB?choID=12346645556665566666       

Is it a good practice ?

Or at this point i should not save the password of the user and just save user_id and generate long unique key using java.UUIID for that user and save it in the table and use this unique key for verification.

I think my UpdateDBService should look likethis

public  String  UpdateDB(int choID ) throws ClassNotFoundException 
        {

            String strDelReturn = "UPDATE_FAIL";

            Class.forName("org.postgresql.Driver");
            try 
            {
                conn = DriverManager.getConnection("....");
            }

            catch (SQLException ex) 
            {

                ex.printStackTrace();
            }


            PreparedStatement pstmt = null;
            String selectQuery = "update user_table set active=active+1 WHERE password="+ choID ;

            try {

                pstmt = conn.prepareStatement(selectQuery);
                int rowss = pstmt.executeUpdate();
                if (rowss != 0) 
                    strDelReturn = "UPDATE_OK";

            } 
            catch (Exception ex) 
            {

            }
            return strDelReturn;
}

Answer 1

My suggestion makes use of a second table, called pending.

It's basically an 'extension' of the users table, or a copy of the main data, depending on your structure.

This table, in it's purest form, would look like this:

UID (int), ActCode (Varchar[x]), ValidUntil (int)

UID and ActCode are both unique, meaning that only one ActCode may be issued per UID at a time, and each ActCode can only be associated once. ValidUntil contains the return value of UNIX_TIMESTAMP() + x seconds...whatever you want your validity period to be.

This structure suggests, that the actual user table has id, userdata, activated as columns.

If the link isn't used for x seconds, the link becomes invalid (UNIX_TIMESTAMP() > ValidUntil) and a new activation link may be generated, while the old one gets deleted.

Alternatively, you can fetch (with each activation related request) the overdue activations and remove the userdata associated with it.

If the link is clicked within the interval, you would remove the activation code and update your user flag.

Another option would be the extended user table:

ActCode, ValidUntil, Userdata

This way, your UserTable is kept out of the selects entirely, you would insert UserData only if the ActCode is used while it's still valid. This protects your AutoIncrement from non-activated accounts and you don't need a Activated field in your user table either...because as soon as it's in your user table, it's activated.