0

I would like to have a aspect of my site that users can use to sign up for a newsletter.

I am not 100% what the best way to accomplish this task would be.

What I can think of is very simple:

One input with a submit button, the user enters their email address there.

A random 32 character hash is generated and stored along with their addess within a mysql table.

Am email is sent to the address containing the hash and asking the user to enter their email address and the hash on a page that checks it against the mysql table.

If correct the email becomes active by defining an additional entry on the table.

That is about as far as my knowledge of the two can take me...

What i would like to accomplish, is in the confirmation send the user a link that they can click to confirm their address... something like http://www.mysite.com/users/newsletter/?user=aGuy&confirm=blahBlah.

But I really do not know where to start with something like that... And as i understand it, allowing mysql queries in such a manner is not secure...

Would someone be able to provide me with some more information regarding this matter?

This being in the form of suggestions or links to tutorials that may cover something like this.

Thank you for taking the time to read this!!

Oliver Charlesworth
  • 267,707
  • 33
  • 569
  • 680
Craig van Tonder
  • 7,497
  • 18
  • 64
  • 109
  • Alot of this is mostly basic PHP and MySQL. Google and this site are good resources. – Norse May 16 '12 at 23:23
  • 1
    @Norse I am self taught, and have been learning through google for years... I dont mean to sound snotty, but if i knew what it was i was talking about, i would not have made a question about it on here... I am just looking for some insight on the matter, from some people who know more about it. Thanks. – Craig van Tonder May 16 '12 at 23:25
  • What's your question? I assume it's about MySQL security and [SQL Injection](http://bobby-tables.com/), but your question isn't quite clear. – Fabrício Matté May 16 '12 at 23:29
  • @Fabrico My question relates to how this process is generally accomplished by other members of this community. But it also relates to applying an sql query directly in the manner which i have described... Also a name for it would be nice so i dont have to keep calling it it :) Thank you! – Craig van Tonder May 16 '12 at 23:31

2 Answers2

3

You're close.

When a user submits his email address, insert it into the database. At a minimum, the table should 4 fields (id,email,verified,key). The id is just a surrogate key (auto-increment). verified should default to false, and the key shouldn't really be a hash but a randomly generated string -- anything that's hard to guess. Hashes are deterministic, so hashing the user's email address with an md5 wouldn't make for a good key if someone figured out what algorithm you were using. A random element is better suited, but again, anything hard to guess will serve just fine.

The email should contain a link that holds the id and the key. You use the id to look up the record in the DB (since it's unique) and then check that the key in the URL matches the one stored in the database (key doesn't have to be unique). If they match, set verified to true, and voila.

For bonus points, you can store a date that the verify email is sent, and you can prune out unverified emails after 24 hours or so.

mpen
  • 272,448
  • 266
  • 850
  • 1,236
  • 2
    One of the best and directly-to-the-point email subscribing php/mysql tutorial I've seen. – Fabrício Matté May 16 '12 at 23:34
  • @Mark Wow, thank you so much :) that was exactly what i needed! But there is a bit of confusion within this in my mind. I understand 100% what you are saying in the second paragraph, but i do not see an obvious means for the id to go from the email, to the website... Would the user enter the ID and random string, or are you suggesting some other means of doing this? – Craig van Tonder May 16 '12 at 23:39
  • 1
    @BlackberryFan: The user doesn't have to enter anything, they just click the link. It might look like `http://yoursite.com/verify_email.php?id=5&key=SJidrf8gaer98AI_eae7h`. Your `verify_email.php` script then pulls out this information from the `$_GET` vars, and updates the DB. Presumably it then redirects them to a "Success!" page, or an "Oh no! Your email couldn't be verified (perhaps this link has expired). Click here to send again." – mpen May 16 '12 at 23:48
  • 1
    @Mark It took some time to soak in but I think the process is crystal clear now! Thank you SO much for your input! – Craig van Tonder May 16 '12 at 23:48
1

Your description of the flow is fine, just start implementing it.

The sfGuard package implements a similar flow, check out their source code and database design.

ilanco
  • 9,581
  • 4
  • 32
  • 37
  • Thanks, I have actually used something like this before... So the idea was quite thought out... I am just not sure if this is the most logical approach. I thought in the users instance, they would have to physically cut and paste the hash, i was just trying to make that process simple for them. But then again, I don't think simple is going to be safe. Thanks for your reply! – Craig van Tonder May 16 '12 at 23:33
  • 2
    When you send the email to the user send it as HTML and include a direct link to your confirmation url, no cutting and pasting and it's not more unsafe. – ilanco May 16 '12 at 23:35
  • 1
    @ilanco Exactly, when it comes to security, your user may manipulate the input fields' values as well as the URL itself, and even generate direct requests to your page. You shouldn't have issues as long as you do the proper sanitizing, that is, using PDO to connect to your db and [prepare](http://php.net/manual/en/pdo.prepare.php)ing your query strings, (or if using `mysqli_`) using the [mysqli_real_escape_string](http://php.net/manual/en/mysqli.real-escape-string.php) and quoting your user input. – Fabrício Matté May 16 '12 at 23:39
  • @ilanco Okay that makes a bit more sense... Thanks alot! – Craig van Tonder May 16 '12 at 23:46
  • @Fabricio Thank you so much for your input! And I do see the point you were getting at... The input is correctly sanitized using mysqli so as you say... there should not be any issues in this. – Craig van Tonder May 16 '12 at 23:47