PHP Validation from database

Question

I am working on a project and I am stuck on the registration page. I want to verify if:

The mobile number already exists.
The username already exists.
The Email ID already exists.

Currently in my code I have added validation for the mobile number and it is working fine. But the username and email part I am not understanding how to implement it. Please help me out with my problem.

Here is my code.

<?php
$msg = '';
if(isset($_POST['register']))
{
    $uname = (!empty($_POST['username']))?$_POST['username']:null;
    $pass = (!empty($_POST['pass']))?$_POST['pass']:null;
    $cpass = (!empty($_POST['cpass']))?$_POST['cpass']:null;
    $fname = (!empty($_POST['fname']))?$_POST['fname']:null;
    $lname = (!empty($_POST['lname']))?$_POST['lname']:null;
    $email = (!empty($_POST['email']))?$_POST['email']:null;
    $mobile = (!empty($_POST['mobile']))?$_POST['mobile']:null;

if($uname == '' || $pass == '' || $cpass == '' || $fname == '' || $lname == '' || $email == '' || $mobile == ''){
    $msg = "<font color='red'>Fields cannot be empty</font>";
}else if(strlen($uname)<5){
    $msg = "<font color='red'>Username must be at least 5 characters long</font>";
}else if(strlen($pass)<6 && strlen($cpass)<6){
    $msg = "<font color='red'>Password must be at least 6 characters long</font>";
}else if($pass != $cpass){
    $msg = "<font color='red'>Passwords are not matching</font>";
}else if(!is_numeric($mobile)){
    $msg = "<font color='red'>Mobile number should contain only numbers</font>";
}else if(strlen($mobile)<10){
    $msg = "<font color='red'>Mobile number should be at least 10 characters long</font>";
}else{

        $query = "SELECT user_mobile FROM user_reg WHERE user_mobile = '".$mobile."'";
        $query1 = mysql_query($query) or die(mysql_error());
        $num_rows = mysql_num_rows($query1);
        $row = mysql_fetch_array($query1);

        if($num_rows > 0)
        {
          $msg = "<font color='red'>Mobile number already exists. Please try again...</font>";
        }
else{
    $str = "INSERT INTO user_reg(user_email, user_uname, user_pass, user_fname, user_lname, user_mobile)VALUES('$email','$uname','$pass','$fname','$lname','$mobile')";
    $sql = mysql_query($str) or die(mysql_error());

if($sql){
    $msg = "<font color='green'>Regstration successfull. Please Login to use your account.</font>";
    }else{
    $msg = "<font color='red'>Sorry.. There are some errors. Please fix them before you continue.</font>";
   }
  }
 }
}
?>

HTML part.

<div class="reg-box"><br />
  <center>
    <?php echo $msg; ?>
  </center>
  <form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
    <div>
      <label>Username</label>
      <input type="text" name="username" value="" class="a-text" />
    </div>
    <div>
      <label>Password</label>
      <input type="password" name="pass" value="" class="a-text" />
    </div>
    <div>
      <label>Confirm Password</label>
      <input type="password" name="cpass" value="" class="a-text" />
    </div>
    <div>
      <label>First Name</label>
      <input type="text" name="fname" value="" class="a-text" />
    </div>
    <div>
      <label>Last Name</label>
      <input type="text" name="lname" value="" class="a-text" />
    </div>
    <div>
      <label>Email</label>
      <input type="email" name="email" value="" class="a-text" />
    </div>
    <div>
      <label>Mobile</label>
      <input type="text" name="mobile" value="" class="a-text" maxlength="10" />
    </div>
    <input type="submit" name="register" value="Register" class="button" id="button-left" />
  </form>
</div>

What should I do add username and email validation? Please help me out friends.

can't you use the same validation you used for mobile? "SELECT username FROM user_reg WHERE username = '".$uname."'" — Jimmy Long, Jun 13 '15 at 14:13
yes i can.. thats the only method but my problem is how can I use that code again bcoz I cant use any if...else there as it is already in an else statement... there i cant apply it again... thats the problem.. i know what to implement but I m confused in how to implement it.... — Shubham Jha, Jun 13 '15 at 14:18
You are open to SQL injections with this code. You should check each field separately and append the `$msg`. Currently if the user has issues in every field it will take them 6 form submissions to figure it out. I'm confused by the `I cant use any if...else there`, why can't you? — chris85, Jun 13 '15 at 14:24
could u just show me how can I add that code here to check username and passwords? and how am I open to sql injections? — Shubham Jha, Jun 13 '15 at 14:29
See this, http://php.net/manual/en/security.database.sql-injection.php. Anytime you pass user input directly into your SQL you are open to SQL injections. You could use http://php.net/manual/en/function.mysql-real-escape-string.php but read the warning at the top and consider switching to PDO or mysqli_ functions. You are trying to check that username is unique right? What are you trying to check with passwords that isn't working? You also shouldn't store passwords in plain text. http://php.net/manual/en/faq.passwords.php — chris85, Jun 13 '15 at 14:33
Nothing is wrong with the passwords. Password validations are working fine. However I have not yet used the MD5 encryption which I will use later. Just in a fix with username and email validation... — Shubham Jha, Jun 13 '15 at 14:40
*"However I have not yet used the MD5 encryption which I will use later."* - Just **don't**. That is old and no longer safe to be used to store a hash. Use what @chris85 outlined in a comment above to store a hash. Whoever told you to use MD5, obviously isn't in 2015, but are stuck in 1995. — Funk Forty Niner, Jun 13 '15 at 14:41
To answer the question; just add more conditions to your `WHERE` clause, by either using `AND` and/or `OR`. Try both or a mix of. — Funk Forty Niner, Jun 13 '15 at 14:47
For email send one that requires a reply to verify it is correct — Ed Heal, Jun 14 '15 at 05:24

score 4 · Answer 1 · edited May 23 '17 at 11:46

It has already been stated in comments that your code isn't safe to use.

Use prepared statements and a modern password hashing method.

Consult my footnotes.

To answer the question, use the following:

$query = "SELECT * FROM user_reg 
WHERE user_mobile = '".$mobile."'

AND user_email = '$email' 
AND user_uname = '$uname' 

";

That will match for all conditions.
You could seperate the condition using OR or a mix of, in order to check for "any" condition. I will let you decide which conditions should be met.

Footnotes:

Your present code is open to SQL injection. Use mysqli with prepared statements, or PDO with prepared statements, they're much safer.

Passwords:

I noticed you may be storing passwords in plain text. If this is the case, it is highly discouraged.

However I have not yet used the MD5 encryption which I will use later.

Plus, you mentioned in wanting to use MD5 in commments. Do not use that. It is old and no longer safe to use as a password hashing/storage method.

I recommend you use CRYPT_BLOWFISH or PHP 5.5's password_hash() function. For PHP < 5.5 use the password_hash() compatibility pack.

i cant use this bcoz I want to show the messages separately for each errors of username, email and mobile. I want to execute the statements separately. — Shubham Jha, Jun 13 '15 at 15:11
@ShubhamJha then try Gideon's answer, or use Ajax which would work best for this. — Funk Forty Niner, Jun 13 '15 at 15:12
@ShubhamJha You also should have pointed that out in your question, rather than in comments. I don't always check through next-to-endless comments. I see a question and I answer it, which I did and pointed out the weaknesses in your code. Ajax in my view, would be a much better method to use and would reduce your code dramatically. — Funk Forty Niner, Jun 13 '15 at 15:22

score 1 · Accepted Answer · answered Jun 13 '15 at 14:58

@Jha, it seems you are quite confused. Yh I know, is kind of wierd. But if I were you I will go by:

<?php
$msg = '';

if (isset($_POST['register'])) {

    $uname  = (!empty($_POST['username'])) ? $_POST['username'] : null;
    $pass   = (!empty($_POST['pass'])) ? $_POST['pass'] : null;
    $cpass  = (!empty($_POST['cpass'])) ? $_POST['cpass'] : null;
    $fname  = (!empty($_POST['fname'])) ? $_POST['fname'] : null;
    $lname  = (!empty($_POST['lname'])) ?$_POST['lname'] : null;
    $email  = (!empty($_POST['email'])) ?$_POST['email'] : null;
    $mobile = (!empty($_POST['mobile'])) ?$_POST['mobile'] : null;

    if ($uname == '' || $pass == '' || $cpass == '' || $fname == '' || $lname == '' || $email == '' || $mobile == '') {
        $msg = "<font color='red'>Fields cannot be empty</font>";

    } else if (strlen($uname) < 5) {
        $msg = "<font color='red'>Username must be at least 5 characters long</font>";

    } else if (strlen($pass) < 6 && strlen($cpass) < 6) {
        $msg = "<font color='red'>Password must be at least 6 characters long</font>";

    } else if ($pass != $cpass) {
        $msg = "<font color='red'>Passwords are not matching</font>";

    } else if (!is_numeric($mobile)) {
        $msg = "<font color='red'>Mobile number should contain only numbers</font>";

    } else if (strlen($mobile) < 10) {
        $msg = "<font color='red'>Mobile number should be at least 10 characters long</font>";

    } else {
        //query for mobile validation
        $m_sql      = "SELECT user_mobile FROM user_reg WHERE user_mobile = '".$mobile."'";
        $m_query    = mysql_query($m_sql) or die(mysql_error());
        $m_num_rows = mysql_num_rows($m_query);
        $m_row      = mysql_fetch_array($m_query);

        //query for username validation
        $u_sql      = "SELECT user_mobile FROM user_reg WHERE user_mobile = '".$uname."'";
        $u_query    = mysql_query($u_sql) or die(mysql_error());
        $u_num_rows = mysql_num_rows($u_query);
        $u_row      = mysql_fetch_array($u_query);

        //query for email validation
        $e_sql      = "SELECT user_email FROM user_reg WHERE user_mobile = '".$email."'";
        $e_query    = mysql_query($e_sql) or die(mysql_error());
        $e_num_rows = mysql_num_rows($e_query);
        $e_row      = mysql_fetch_array($e_query);

        if ($m_num_rows > 0) {
            $msg = "<font color='red'>Mobile number already exists. Please try again...</font>";

        } else if ($u_num_rows > 0) {
            $msg = "<font color='red'>Username already exists. Please choose a unique one...</font>";

        } else if ($e_num_rows > 0) {
            $msg = "<font color='red'>Email already exists. Please choose a unique one...</font>";

        } else {
            $str = "INSERT INTO user_reg(user_email, user_uname, user_pass, user_fname, user_lname, user_mobile)VALUES('$email','$uname','$pass','$fname','$lname','$mobile')";
            $sql = mysql_query($str) or die(mysql_error());

            if ($sql) {
                $msg = "<font color='green'>Regstration successfull. Please Login to use your account.</font>";
            } else {
                $msg = "<font color='red'>Sorry.. There are some errors. Please fix them before you continue.</font>";
            }
        }
    }
}

?>

Its actually through, your code is very open to SQL injection and also mysql database connection layer is deprecated so try using mysqli or pdo. Hope it went well. — Gideon Appoh, Jun 13 '15 at 15:01
EXACTLY A SOLUTION TO MY PROBLEM... I got what I was looking for. THANKS A LOT DUDE... however I will look forward to use mysqli_ in my code. Thanks. :) — Shubham Jha, Jun 13 '15 at 15:21
I will look for prepared statements later. But for now this is the solution. Thanks. :) — Shubham Jha, Jun 13 '15 at 15:24

rastaBob · Answer 3 · 2015-06-14T05:20:06.093

Besides fixing your code so that it's not vulnerable to SQL injection you should change your query to check all three inputs at the same time using the OR operator.

$query = "SELECT * FROM user_reg WHERE user_mobile = '".$mobile."' OR user_uname = '".$uname."' OR user_email = '".$email."'";

Then if you do get any hits you can check to see what it was:

if($query1->num_rows > 0){
        while($field = $query1->fetch_assoc()){
            if($field['user_mobile'] === $mobile){
                 $msg = $msg . "<font color='red'> Mobile number already exists. Please try again...</font>";
            }
            if($field['user_email'] === $email){
                $msg = $msg . "<font color='red'> Email already exists. Please choose a unique one...</font>";
            }
            if($field['user_uname'] === $uname){
                 $msg = $msg . "<font color='red'> Username already exists. Please choose a unique one...</font>";
            }
        }
    }

But like the others say, you'll want to switch to using either MySQLi or PDO_MySQL

i can use this but then it will let me display only one message.. like USER DATA ALREADY EXISTS... I want to display the messages separately like MOBILE NUMBER ALREADY EXISTS, USERNAME ALREADY EXISTS, EMAIL ID ALREADY EXISTS.. for this it is necessary for me to use each statements separately... but how? — Shubham Jha, Jun 13 '15 at 15:09

PHP Validation from database

3 Answers3