Checking if a user exists, Java, JDBC and MySQL

Question

I'm trying to check if a user exist on my database, and then perform some verification processes for the user, and if not return an intruder message. I'm using Java and MySQL for this purpose. Here is my code.

public class Check{

    public boolean isPresent(){
        connect();// private method that connects to db
        boolean isAvailable = false;
        String query = "SELECT EXISTS(SELECT 1 FROM students WHERE matric =" + this.myId + ")"; // this.myId has been passed into the constructor

        try{
            Statement statement;
            ResultSet resultSet;
            statement = connection.createStatement();
            resultSet = statement.executeQuery(query);
            if(resultSet.absolute(1)){
                isAvailable = true;
            }else{
                isAvailable = false;
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
         closeConnection();// private method that closes connection
         return isAvailable;
    }

}

I have checked for a solution like this earlier but it does not seem to solve the bug. The error I get reads: "Unknown column CCE in where clause", There are other errors from JDBC API tho. Seem to me like something is being truncated for the value I passed in, from the construtor.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'CCE' in 'where clause'
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
        at java.lang.reflect.Constructor.newInstance(Unknown Source)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
        at com.mysql.jdbc.Util.getInstance(Util.java:383)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:980)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2541)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2499)
        at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1432)

you didn't quote `myId`. You should read about sql injection and prepared statements. You also probably need a LIKE here (based on your screenshot) — , Dec 22 '15 at 06:06
see this [answer](http://stackoverflow.com/a/1812920/180100) for example — , Dec 22 '15 at 06:11
try printing out your sql query statement. like system.out.println(query ); before the starting of try block ,so you can view your complete query. — Haseeb Anser, Dec 22 '15 at 06:15

Jan · Answer 1 · 2017-11-23T13:15:13.490

From Easy Fix to correctness

1 - Quoting your ID will fix the SQL error you have (adding ' around id)

String query = "SELECT EXISTS(SELECT 1 FROM students WHERE matric = '" + this.myId + "')"; // this.myId has been passed into the constructor

But still, using a subselect for existance? Why not simply get the student directly?

2 - Remove subselect

String query = "SELECT * FROM students WHERE matric = '" + this.myId + "'"; 
try{
        Statement statement;
        ResultSet resultSet;
        statement = connection.createStatement();
        resultSet = statement.executeQuery(query);
        if(resultSet.next()){
            isAvailable = true;
        }else{
            isAvailable = false;
        } //Missing in your code

Using a prepared Statement to prevent SQL-Injection

Think of a myId like... myId = "killer'); DROP TABLE STUDENTS; --" - you don't want to execute that this way.

String query = "SELECT * FROM students WHERE matric = ?"; 
try{
        PreparedStatement statement;
        ResultSet resultSet;
        statement = connection.prepareStatement(query);
        statement.setString(1, myId);
        resultSet = statement.executeQuery();

Try-With-Ressource and improved readability

By using try-with-resource you can get rid of most of the catches:

String query = "SELECT * FROM students WHERE matric = ?"; 
try (PreparedStatement statement = connection.prepareStatement(query)) {
        statement.setString(1, myId);
        try(ResultSet resultSet = statement.executeQuery()) {
          return rs.next();
        }
} catch(SQLException se) {
   se.printStackTrace();
   return false;
}

score 1 · Answer 2 · answered Dec 22 '15 at 06:14

SQL Queries are NOT as Java Statements/Expressions.

In the place of the variable

this.myId

use a placeholder i.e.

?

Example: "SELECT EXISTS(SELECT 1 FROM students WHERE matric =?"

and include this inside the double quotes.

Please remember to use PreparedStatement unstead of Statement

PreparedStatement pst=connection.createStatement();

Once u create the query using statement = connection.createStatement(); just set the myId variable to the query like :

pst.setString(1,this.myID);

this should help you

score 0 · Answer 3 · answered Dec 22 '15 at 06:39

Try this Am passing the id as parameter and use prepared statement to make your code more efficacy

 public boolean isUserExsit(String id) {

   boolean isDuplicated = false;
   Connection connection = lockConnection();
   String sql = "SELECT 1  FROM students WHERE matric = ? ";



    try {
        PreparedStatement statement = connection.prepareStatement(sql);
        if (statement != null) {
            statement.setString(1, id);




            try {
                ResultSet results = statement.executeQuery();
                if (results != null) {
                    try {
                        if (results.next()) {
                            isDuplicated = true;

                        }
                    } catch (Exception resultSetException) {
                        resultSetException.printStackTrace();
                    }
                    results.close();
                }
            } catch (Exception statmentExcption) {
                statmentExcption.printStackTrace();
            }
            statement.close();
        }
    } catch (Exception generalException) {
        generalException.printStackTrace();
    }

    releaseConnection(connection);



    return isDuplicated;
}

Checking if a user exists, Java, JDBC and MySQL

3 Answers3