没有唯一约束的mysql插入如果不存在（不想依赖回滚错误）（PHP + PDO）

Question

I am making a registration page. I'm worrying about concurrency issues where two users register with the same username at the same time (know it's real rare, but hate having small flaws in code).

So my approach is, check if the username exists, and if not, insert a new row. I'm using PDO

What I've tried

I'm using transactions, but from my question here How exactly do transactions with PHP PDO work with concurrency? it appears that two transactions can read at the same time

• I don't think I can use select...for update because I am not updating; in fact, I need to lock an "imaginary" row where a new entry will be added, if it does not exist already
• I've tried googling some examples, but they don't seem to handle the concurrency issue mentioned above http://php.about.com/od/finishedphp1/ss/php_login_code_2.htm

Solution?

I've googled and added a UNIQUE constraint on the username field, but do not want to rely on a MySQL error to rollback the transaction. I'm no expert, but it just doesn't feel elegant to me. So is there a way to insert if not exists with pure MySQL?

Now, if this really is the way to go, I've got a couple questions. If a warning is thrown, does that stop the queries? Like will it throw the exception shown in the example here PHP + MySQL transactions examples ? Or will only a downright-error throw the exception?

Also, it seems to imply here Can I detect and handle MySQL Warnings with PHP? that warnings will show up somehow in the PHP output, but I've never had "visible MySQL errors." The question was not using PDO like in my code, but I was just wondering. Do MySQL errors and warnings make html output? Or does it only say something if I callerrorInfo()?

Thanks

Answer 1

UPDATE

If I had two unique fields, and did not want to allow either one to be null (since I know I could set one table to "null-able" and have two queries), is there a way to check which one messed up? Like I want to let the user know if username or email was taken. If I do a single insert, I won't know which one failed

And furthermore, if I have two statements (first insert username, check if failed, then try same for email), I can only detect one error at a time; if the username fails and rolls back, I cannot try to do the same for the email (well it would be redundantish, because even if the email exists I would have to check to see if the first query had failed) Edit: Think it can work if I use nested try...catch statements

I think youre over thinking this. You can show a different error to the user for each one but you can really only detect one at a time because mysql is only going to give you one error message with the first problem it encounters. Assuming the previous query (to insert all values at once):

try {
  $stmt = $db->prepare($sql);
  $stmt->execute(array(
    ':username' => $username,
    ':email' => $email,
    ':password' => $password
  ));

  $db->commit();
} catch (PDOException $e) {
  $db->rollBack();
  if($e->getCode() == 23000) {

    // lets parse the message

   if(false !== strpos('email', $e->getMessage())) {
       echo 'Email "'.  $email . '" already exists';
    } else if(false !== strpos('username', $e->getMessage()) {
       echo 'Username "'. $username .'" taken';
    }

  } else {
     // not a dupe key rethrow error
     throw $e;
  }
}

Now the hitch with that is the error message is going to look something like:

Duplicate entry 'THE_VALUE_YOU_TRIED_TO_INSERT' for key THE_KEY_NAME

The way to get more meaningful reporting on which column it was is to name the indexes with something meanigful when you create the table for example:

CREATE TABLE the_table (
   `id` integer UNSIGNED NOT NULL AUTO_INCREMENT
   `username` varchar(30),
   `email` varchar(100),
   `password` varchar(32),
   PRIMARY KEY (`id`),
   UNIQUE KEY `uk_email` (`email`),
   UNIQUE KEY `uk_username` (`username`)
); 

so now your error message will look like:

Duplicate entry 'THE_VALUE_YOU_TRIED_TO_INSERT' for key uk_username

OR

Duplicate entry 'THE_VALUE_YOU_TRIED_TO_INSERT' for key uk_email

Which is easily parsable.

The only real alternative to doing it this way is to do a select on table before you insert to ensure the values dont already exist.

---

If youre using PDO you shouldnt have PHP warnings... Just exceptions, which if uncaught will generate a standard php error. IF you have display_errors turned off then that wont be output to the screen, only to the error log.

I dont think there is a way to insert if not exists so you are back to using a unique key and then catching the exception:

$db = new PDO($dsn, $user, $pass);
$sql = "INSERT INTO the_table (username, email, etc) VALUES (:username,:email,:password)";

$db->beginTransaction();
try {
  $stmt = $db->prepare($sql);
  $stmt->execute(array(
    ':username' => $username,
    ':email' => $email,
    ':password' => $password
  ));

  $db->commit();
} catch (PDOException $e) {
  $db->rollBack();
  if($e->getCode() == 23000) {
    // do something to notify username is taken
  } else {
     // not a dupe key rethrow error
     throw $e;
  }
}