-1

During my site's signup process three inserts in separate tables occur to setup a new account:

  1. INSERT INTO User
  2. INSERT INTO Profile
  3. INSERT INTO ProfileSetup

Currently, if a failure occurs at any point in the process, I kill the program and notify the user that an error has occurred. If a failure occurs on step 3 for whatever reason, technically the user account and profile are live and the user could log in, likely resulting in major problems. Not a good first impression.

I'm wondering what the best way to handle this is? If step 3 fails should I go back and delete the records created in step 1 and 2?

Runicode
  • 291
  • 2
  • 3
  • 19

1 Answers1

0

If a failure occurs on step 3 for whatever reason ...

Ideally all possible reasons should be sussed out and built in to the design so that you are able to validate the data before any thing is touched in the database.

Here's is an example of a validation method as part of account creation in Opencart (line 223):

https://github.com/opencart/opencart/blob/master/upload/catalog/controller/account/register.php

As you can see all of the basic requirements are check before the database method in the model is called.

If you implement something like this, the only time I think your query could still fail would be a database crash or some other issue with the operating system. In my experience this is extremely rare unless you have more serious underlying issues with your setup. In that case, I think the comment above about using transactions is good advice.

But those new buttons though..
  • 21,377
  • 10
  • 81
  • 108
  • True, but still the query could fail after a successful validation, for whatever reason. – Robert Nov 05 '17 at 15:29
  • What does *"for whatever reason"* mean? What reason? That's the point of a validation method. That said, I agree with the comment above about transactions. That's a great additional step, but 99.9% of the failures will be caught by a validation method – But those new buttons though.. Nov 05 '17 at 15:32
  • 1
    For whatever reason, as in _not data related_, the query could still fail. That's why I said _after successful validation_ :). I agree with you that proper validation will solve a lot and that it's a must-have, but you might still get a failed query. – Robert Nov 05 '17 at 15:36
  • There is a ton of data validation for both front and backend in place, so the issues should not be data related. It seems like transactions are the way to go so that I can roll everything back. The "for whatever reason" comment was a catch all for the unknown. For example an insert failing due to a dup entry. – Runicode Nov 05 '17 at 15:37
  • 1
    @Robert - yes I agree. I've revised my answer a bit. – But those new buttons though.. Nov 05 '17 at 15:40
  • @Runicode Well... `For example an insert failing due to a dup entry` is something you can validate :) – Robert Nov 05 '17 at 15:42
  • @Runicode - A duplicate entry is *always* something that should be part of your php validation. If you notice line 236 of the example I gave that scenario is checked. – But those new buttons though.. Nov 05 '17 at 15:42
  • You're both correct it can be checked as part of my php validation, but it's also another query to execute. So I guess it's a trade off. The profile table alone can have 10s of millions of records. Seems like it's faster to let a failure occur in the transaction on insert where a unique index is present than run the expensive select statement. Or am I off on this? – Runicode Nov 05 '17 at 15:47
  • The select statement to check for duplicate entry shouldn't be expensive if an index is used. In my opinion, relying on a a transaction failure for validation deprives you of the opportunity to return a useful message to the user about why the failure happened. – But those new buttons though.. Nov 05 '17 at 15:50
  • I want to say something important here based on real world experience - yes you should use ***both***. But I've been a sysadmin for some rather large, high traffic production ecommerce sites running older software that didn't use transactions and I've never once seen a series of queries fail for any reason other than a bug in the validation code or a server crash (extremely rare). I understand it's theoretically possible and that's why transactions exist, but good php validation is really all you need to cover most issues. – But those new buttons though.. Nov 05 '17 at 15:54