How to validate user email from the MySQL database?

Question

There is a registration form on my web application. What I want is, when a user submits the form, validation should be done to check if the email id which the user entered already exists or not in the database. And accordingly, if it exists then a message like in a snackbar should appear saying, the email id already exists. If it's a new email, then it will redirect to the success page.

Following is my checkData method where I am just checking if when I enter the email id on the form, it exists or not, but the ouput is always "value not found".

public void checkData() {

      try {
            Class.forName("com.mysql.cj.jdbc.Driver");


          Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbname?autoReconnect=true&useSSL=FALSE", "root", "pwd");



          st = con.createStatement();

           String query1 = "select email from users where email='" +email99+ "'";
          ResultSet rs = st.executeQuery(query1);


          if(rs.next()) {

              System.out.println("Success");

          }

          else {

              System.out.println("Value not found");
          }

          rs.close();




      } catch (Exception e) {
            // TODO Auto-generated catch block
            System.out.println(e.getMessage());
        }

      }

Note:- My main concern is email validation.

Thank you

ehm ... you claim your main concern is email validation, yet your question is about checking whether it's already in your DB, which is not email validation related. — Stultuske, Mar 25 '19 at 07:08
Either way: once the user provided his input SELECT * FROM MY_TABLE WHERE EMAIL = [valueProvidedByUser]; if ( nrOfResults == 0 ) -> valid input else -> There is already a user with this email — Stultuske, Mar 25 '19 at 07:09
@Shashankk is 'email99' dynamic value? Try the below query "select email from users where email='email99'". If you have the column email in the table users and if you have record with email value 'email99' then you will get 'Success' message. — Ganesa Vijayakumar, Mar 25 '19 at 07:15

Ajmal Muhammad · Answer 1 · 2019-03-25T07:28:41.703

For Email validation

you have to set email column as unique in you table.
when ever you are trying to insert new user, SQL will throw an constraint violation exception if email already exists. you can use that error also for error handling

generally update will take more time compared to get operation So the best method is.. First you have to check is there any row exist with this email ID

String sql = "select email from users where email= ? ";
PreparedStatement prepStmt = conn.prepareStatement(sql);
prepStmt.setString(1, "emailId");
ResultSet rs = prepStmt.executeQuery();

if (rs.next()) {
  System.out.println("Row with email found");
} else {
  // you can write update code here
}

Ali Azim · Answer 2 · 2019-03-25T08:18:51.963

0

For Unique Email validation, there are two methods:

Method 1: The first one is to execute the select query and check it, which is not recommended as a solution.

PreparedStatement statement = connection.prepareStatement("select email from users where email= ?");
statement.setString(1, email99);    
ResultSet resultSet = statement.executeQuery();

if (resultSet.next()) {
  System.out.println("Row with email found");
} else {
  // you can write update code here
}

Method 2: on the DB level, make the email column unique. so whenever a new record is inserted. DB is responsible for checking the unique email address and throw the error. Use the error to display it on the front end.

cheers!!

edited Mar 25 '19 at 08:18

answered Mar 25 '19 at 07:24

Ali Azim

160
1
15

Method 1 has problem. When you look up the target email, you may get 0 result. But in the same time, other users submit the target email and you will get duplicate emails in your DB. – flycash Mar 25 '19 at 08:12
that's why I said it's not a recommended solution at all. – Ali Azim Mar 25 '19 at 08:24
Thank you. But, I want to put that into a condition like, if(email entered by user exists within the db) then redirect the user back to the register.jsp – Shashankk Shekar Chaturvedi Mar 25 '19 at 08:55
@ShashankkShekarChaturvedi then used the if condition as defines above and if its true redirect back to the register.jsp page else perform your business logic in else block. – Ali Azim Mar 25 '19 at 08:58
Everything is defined within checkData() method and I cannot use request dispatcher or response.sendredirect inside this method. – Shashankk Shekar Chaturvedi Mar 25 '19 at 09:01
1

The simple solution is that change your method return type to boolean and if email exists return true else false and in calling method use this boolean result and perform your logic. – Ali Azim Mar 25 '19 at 09:24

flycash · Answer 3 · 2019-03-25T08:37:13.937

Method1:

The simplest method is using unique index on column email.

You can not using the solution which looks up email firstly and insert email. Suppose that there are two users submit the email some@email.com. And there does't have the email in your DB.

Both users will get res.next() false, and you will insert two rows with the same email to DB.

If you use the unique index, you will get Exception if there already has the email. You can catch the Exception and return the "duplicate email" message. See the question catch duplicate exception

If you use Mysql, maybe you can use the ON DUPLICATE KEY.

Method2: There has a more complicated solution.

If you can not use the unique index, then you can consider this.

You can use the Lock. If you work on distributed system, you should use the distributed lock. You should lock the email. If the thread get the lock, you can check whether there has the target email and then insert into DB.

But if your system just run on one JVM, you can try:

syschronized(email.intern()) {
  // check the email whether exists
  // if not, insert it.
}

In this case, you will suffer from the large String pool which will affect your GC pause and waste memory.

In fact you can use double-check to optimize the performance. It looks like

 emailInDB = fetchEmailFromDB(email);
 if (emailInDB == null) {
    lock(email);
    emailInDB = fetchEmailFromDB(email);
    if(emailInDB == null) {
       insertIntoDB(email)
   } else {
     return "the email already exists";
   }
 } else {
  return "the email already exists";
}

I want to put that into a condition like, if(email entered by user exists within the db) then redirect the user back to the register.jsp — Shashankk Shekar Chaturvedi, Mar 25 '19 at 08:32
Also, I have a user model class in the above project which stores user object. So, for the email99, I am directly getting that value. Eg:- String email99 = ud.getEmail(email); — Shashankk Shekar Chaturvedi, Mar 25 '19 at 08:34

How to validate user email from the MySQL database?

3 Answers3