PHP exception handling after SQL insert

Question

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 1

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.

Answer 2

$con=mysqli_connect("localhost","root","","my_db");
$check="SELECT COUNT(*) FROM persons WHERE Email = '$_POST[eMailTxt]'";
if (mysqli_query($con,$check)>=1)
{
    echo "User Already in Exists<br/>";
}
else
{
    $newUser="INSERT INTO persons(Email,FirstName,LastName,PassWord) values('$_POST[eMailTxt]','$_POST[NameTxt]','$_POST[LnameTxt]','$_POST[passWordTxt]')";
    if (mysqli_query($con,$newUser))
    {
        echo "You are now registered<br/>";
    }
    else
    {
        echo "Error adding user in database<br/>";
    }
}