Sending authentication mails

[Bert Bos]

Hi,

when users are register on our website, their username, encrpyted
password and so on are stored in the mySQL database.

Many phpbb sites though send an activation mail to the email address
specified by the user. The user then needs to select a link to get his
account activated.

I would like to implement this process, can anyone tell me how this is done?

Thanks,
Bert.

[Pedro Graca]

Re: Sending authentication mails

Bert Bos wrote:[color=blue]
> when users are register on our website, their username, encrpyted
> password and so on are stored in the mySQL database.
>
> Many phpbb sites though send an activation mail to the email address
> specified by the user. The user then needs to select a link to get his
> account activated.
>
> I would like to implement this process, can anyone tell me how this is done?[/color]

After the registration data is saved to the database, send them a link
to a validate.php script

http://www.example.com/validate.php?code=78Jh5qM0

The code in the link could be random and saved to the database.
When, later, the user access the script, you search the database for the
code and update the record indicating that this particular user has
validated.

$sql = "update user_table set validated=1 where code='{$_GET['code']}'";

Make sure every (unvalidated) code is unique in the database.

Also try to prevent people from validating random accounts by locking
out a 'connection' that fails after three (or whatever) attempts.

Hope this helps.

--
If you're posting through Google read <http://cfaj.freeshell. org/google>

[Andy Hassall]

Re: Sending authentication mails

On 19 Jan 2006 20:17:02 GMT, Pedro Graca <hexkid@dodgeit .com> wrote:
[color=blue]
> $sql = "update user_table set validated=1 where code='{$_GET['code']}'";[/color]

I'm sure you know better than to do this :-) SQL injection ahoy - remember to
escape appropriately, or use a library that implements (or at least emulates)
placeholders.

This is actually quite a good demonstration of SQL injection risks; you could
call the script as:

validate.php?co de='+or+'a'%3d' a

... and it'll set validated=1 without the right code, as you end up with the
SQL as:

update user_table set validated=1 where code='' or 'a'='a'

--
Andy Hassall :: andy@andyh.co.u k :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[Justin Koivisto]

Re: Sending authentication mails

Pedro Graca wrote:[color=blue]
> Bert Bos wrote:[color=green]
>> when users are register on our website, their username, encrpyted
>> password and so on are stored in the mySQL database.
>>
>> Many phpbb sites though send an activation mail to the email address
>> specified by the user. The user then needs to select a link to get his
>> account activated.
>>
>> I would like to implement this process, can anyone tell me how this is done?[/color]
>
> After the registration data is saved to the database, send them a link
> to a validate.php script
>
> http://www.example.com/validate.php?code=78Jh5qM0
>
> The code in the link could be random and saved to the database.
> When, later, the user access the script, you search the database for the
> code and update the record indicating that this particular user has
> validated.
>
> $sql = "update user_table set validated=1 where code='{$_GET['code']}'";
>
> Make sure every (unvalidated) code is unique in the database.
>
> Also try to prevent people from validating random accounts by locking
> out a 'connection' that fails after three (or whatever) attempts.[/color]

For some reason, basing something like this with just a single
credential to the database makes me squirmish. I personally would
validate by asking for their email (whether it's in the URL or
what-have-you) and issue a query like this:

select user_id from user_table where user_email = '$escpaed_usern ame'
and code = '$escaped_code'

If no results were returned, then either the code doesn't match with the
email, and therefore isn't really an account validation after all...

--
Justin Koivisto, ZCE - justin@koivi.co m

http://koivi.com

[Pedro Graca]

Re: Sending authentication mails

Andy Hassall wrote:[color=blue]
> On 19 Jan 2006 20:17:02 GMT, Pedro Graca <hexkid@dodgeit .com> wrote:
>[color=green]
>> $sql = "update user_table set validated=1 where code='{$_GET['code']}'";[/color]
>
> I'm sure you know better than to do this :-) SQL injection ahoy - remember to
> escape appropriately, or use a library that implements (or at least emulates)
> placeholders.[/color]

Of course! I was just testing the audience :-)

Thank you for being on the lookout and calling attention to the errors
commited. It's appreciated.

--
If you're posting through Google read <http://cfaj.freeshell. org/google>

[Pedro Graca]

Re: Sending authentication mails

Justin Koivisto wrote:[color=blue]
> Pedro Graca wrote:[color=green]
>> After the registration data is saved to the database, send them a link
>> to a validate.php script
>>
>> http://www.example.com/validate.php?code=78Jh5qM0
>>
>> The code in the link could be random and saved to the database.
>> When, later, the user access the script, you search the database for the
>> code and update the record indicating that this particular user has
>> validated.
>>
>> $sql = "update user_table set validated=1 where code='{$_GET['code']}'";[/color]
>
> For some reason, basing something like this with just a single
> credential to the database makes me squirmish. I personally would
> validate by asking for their email (whether it's in the URL or
> what-have-you) and issue a query like this:
>
> select user_id from user_table where user_email = '$escpaed_usern ame'
> and code = '$escaped_code'
>
> If no results were returned, then either the code doesn't match with the
> email, and therefore isn't really an account validation after all...[/color]

In real life I'd have a different table with the validation codes. This
table would also have a datetime for the limit of the validation code
(eg one week after sending the email) and the specific record would be
deleted when no longer needed.

I'd probably also make the 'validated' column a 'status' column, linking
to a status table (Pending, Validated, OnVacation, Deleted, ...)

Well ... there are always lots of ways to complicate what begins as a
simple task :)

--
If you're posting through Google read <http://cfaj.freeshell. org/google>