JDBC: i need your opinion with Log in Code using SQL and JDBC

Question

This is a code that i'm creating helping users to log in into my application a method login that gets two arguments username and password.

I want to be specified with my error messages to the user, so:

if the email does not exist in the table it shows "email is not registered"
if the email exists and the password is incorrect is shows " incorrect password"

Is my code efficient? I need your opinion. What do I need to improve\change?

Here's the method

    private String command;

private ResultSet resultSet;
private PreparedStatement statement;
private Connection connection;

String jdbcUrl = "jdbc:mysql://localhost:3306/registered";
String jdbcUser = "...";
String jdbcPassword = "...";

public boolean login(String eml, String pwd) {

    try {

        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection(jdbcUrl, jdbcUser,
                jdbcPassword);

        command = "SELECT Email FROM users WHERE Email LIKE '" + eml + "';";
        statement = connection.prepareStatement(command);
        resultSet = statement.executeQuery();

        if (!resultSet.isBeforeFirst()) {
            System.out.println("Email (" + eml + ") is not registered ! ");

            // show error message
        } else {
            command = "SELECT Email,Password FROM users WHERE Email LIKE '"
                    + eml + "' AND Password LIKE '" + pwd + "';";
            statement = connection.prepareStatement(command);
            resultSet = statement.executeQuery();

            if (!resultSet.isBeforeFirst()) {
                System.out.println("Password for Email (" + eml
                        + ") is incorrect ! ");

                // show error message
            }
            else {
                System.out.println("Logged in!");
            }

        }

    } catch (SQLException e) {
        System.out.println("SQLException: " + e.getMessage());
        System.out.println("Vendor error: " + e.getErrorCode());

    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }

    return false;
}

to give values you should probably use setString()... that is more appropriate. — Aradhna, Jun 21 '14 at 04:16
Please remember to [hash (and salt)](http://stackoverflow.com/a/24265777/2970947) your user's passwords. — Elliott Frisch, Jun 21 '14 at 04:27

score 2 · Accepted Answer · edited May 23 '17 at 10:25

Is my code efficient?

No, it is not. The main problem in the performance of this method is that you open the connection manually here:

Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword);

This is a bottleneck because you will open a physical connection to the database and close it, and this is a high-cost operation. This should be replaced for a database connection pool and retrieve the connection from there. Some options to implement a database connection pool:

More info:

What do I need to improve\change?

Apart of the way you obtain the connection, the current code is prone to SQL Injection because you concatenate the string to generate the SQL statement:

command = "SELECT Email FROM users WHERE Email LIKE '" + eml + "';";

Use PreparedStatement accordingly and set parameters:

command = "SELECT Email FROM users WHERE Email = ?";
statement = connection.prepareStatement(command);
statement.setString(1, eml);
resultSet = statement.executeQuery();

Not really a problem with your code but with your design. The password of the users should be encrypted and validated on database only. Looks like you're storing the password as plain text, which is a valid option for learning purposes, but not to use in real world applications.

Is using like on login really good here? I can imagine some new class of regex injection with this. — Elliott Frisch, Jun 21 '14 at 04:24
@ElliottFrisch a decent JDBC driver will escape any invalid character and append quotes to the parameter used in `LIKE` operator. — Luiggi Mendoza, Jun 21 '14 at 04:25

JDBC: i need your opinion with Log in Code using SQL and JDBC

1 Answers1