用户注册数据库问题

Question

So, this is a script to dump the user info into the database using the PDO driver. Whenever I run the script with a unique username it works and it dumps the info in. But, if a user with the same username tries to sign up it should throw an error. I'm using $stmt->errorCode() == 23000 to check for duplicates in the database but whenever I try and register a username I've already registered I just get a blank white screen. I've ran through the script with xDebug and it looks like when I try and sign up with a username that's already in the database it's not even hitting the if/else statements at the bottom of the script. It's just executing and throwing a blank white screen.

EDIT: I do have the username field in the DB set to 'unique'.

Video of xDebug walkthrough: http://screencast.com/t/znZfMgV2

Any ideas?

// create a salt using the current timestamp
$salt = time();

// encrypt the password and salt
$password = sha1($password.$salt);

// prepare the sql statement
$sql = 'INSERT INTO users (created, username, salt, password) VALUES(now(), :username, :salt, :password)';
$stmt = $conn->prepare($sql);
// bind the parameters
$stmt->bindParam(':username', $username, PDO::PARAM_STR);
$stmt->bindParam(':salt', $salt, PDO::PARAM_INT);
$stmt->bindParam(':password', $password, PDO::PARAM_STR);
$stmt->execute();

if ($stmt->rowCount() == 1) {
    $registerResult = "$username has been registered. You may now log in.";
} elseif ($stmt->errorCode() == 23000) {
    $registerResult = "$username is already in use. Please choose another username.";
} else {
    $registerResult = 'Sorry, there was a problem with the database';
}

Answer 1

Checking for errorcodes will not work, because the duplicate error will throw an exception on the call to execute().

You need to catch PDOException :

try {
    // create a salt using the current timestamp
    $salt = time();

    // encrypt the password and salt
    $password = sha1($password.$salt);

    // prepare the sql statement
    $sql = 'INSERT INTO users (created, username, salt, password) VALUES(now(), :username, :salt, :password)';
    $stmt = $conn->prepare($sql);
    // bind the parameters
    $stmt->bindParam(':username', $username, PDO::PARAM_STR);
    $stmt->bindParam(':salt', $salt, PDO::PARAM_INT);
    $stmt->bindParam(':password', $password, PDO::PARAM_STR);
    $stmt->execute();

    if ($stmt->rowCount() == 1) {
        $registerResult = "$username has been registered. You may now log in.";
    }
} catch (PDOException $e) {
    if ($stmt->errorCode() == 23000) {
        $registerResult = "$username is already in use. Please choose another username.";
    } else {
        $registerResult = 'Sorry, there was a problem with the database';
    }
    die($e->getMessage());
}

Make sure your connection is set up to throw exception of course:

$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );