Lesley Peters Lesley Peters - 5 months ago 38
SQL Question

PHP exception handling after SQL insert

I am creating a very simple registration form in php, currently when the user tries to register there will popup a javascript alert with a succes or fail message.

Now I want to catch the sql exception to show if the username or email already excists in the database instead of a standard fail message.

This is the code I have so far:

if(isset($_POST['btn-signup']))
{
$uname = mysql_real_escape_string($_POST['uname']);
$email = mysql_real_escape_string($_POST['email']);
$upass = md5(mysql_real_escape_string($_POST['pass']));

if(mysql_query("INSERT INTO user(username,password,email) VALUES('$uname','$upass','$email')"))
{
?>
<script>alert('successfully registered ');</script>
<?php
}
else{
?>
<script>alert('error while registering you...');</script>
<?php
}
}
?>


How can I check if the email or username already excists in the database? Both variable's are already unique in the database.

Answer

From Comments:

I don't want 2 queries while the database can return an exception for me. If there are about 10 million records in that table, I don't want to check them all before inserting a new one.

Ok, so you have one query to insert and check is unique? So you have to INSERT on a UNIQUE_INDEX MySQL column, you can catch these sort of exceptions with the following style of answer shameless stolen from this answer to this question:

In the case of this answer we'll assume you're using PDO, because you should. Please read up about it.

// Pre-setup the database connection somewhere, usually an include (or a class)
$link = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbusername,$dbpassword);
// PDO needs to be set in Exception mode:
$link->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

//Input Processing functions.
// (entirely optional)
$uname = MyCleanerFunction($_POST['uname']);
$email = MyCleanerFunction($_POST['email']);
//please see note below re:MD5
//$upass = md5($_POST['pass']); 
$options['cost'] = 12;
$upass = password_hash($_POST['pass'],PASSWORD_BCRYPT,$options);

//now reach the code part:
    try {
        //PDO query execution goes here:

         $statement = $link->prepare("INSERT INTO user(username,password,email) VALUES(:uname, :email, :pass)"));
         $statement->bindValue(":uname", $uname);
         $statement->bindValue(":email", $email);
         $statement->bindValue(":pass", $upass);
         $statement->execute();
         //reaching here everything is ok! 
    }
    catch (\PDOException $e) {
        if ($e->errorInfo[1] == 1062) {
            // The INSERT query failed due to a key constraint violation.
            // THIS means that it failed because the Primary Key 
            // (the email) appears already in the database table.
        }
        if($e->errorInfo[1] == 9999){
          // possible other IF clauses for other errors in INSERT.
        }
    }

You would also do well to read up about catching and outputting PDO errors. As well as all about MySQL Unique Key Constraints.

  • Also very useful alternative viewpoint that you Should not catch PDO exceptions.

  • Also please note that MD5 is an extremely weak hash for storing passwords and that PHP password_hash function is the much preferred way of doing it.

  • PLEASE use Prepared Statements for your MySQL interactions, the layout above is a rough guide to how they look and is very similar for MySQLi and PDO. Prepared Statements go a long way towards securing your data from malicious user input.