JDBC MySQL query syntax

Question

I'm working with JDBC trying to select from this table :

CREATE TABLE PERIT (
    COGNOM1 VARCHAR(30) NOT NULL,
    COGNOM2 VARCHAR(30) NOT NULL,
    DATANAIX DATE NOT NULL,
    NOM VARCHAR(30) NOT NULL,
    NIF VARCHAR(10) NOT NULL,
    LOGIN VARCHAR(50) NOT NULL,
    PASSWORDMD5  VARCHAR(50) NOT NULL,
    NUMERO INT(3) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (NUMERO)
);

I want to check if exists a row with a certain login and PASSWORDMD5 values like this :

public int Login(String Login, String pass) {
    Statement st = null;
    ResultSet rs = null;
    int count =0;
    try {
        st = con.createStatement();
        String consulta = "Select count(*) from Perit p where p.LOGIN =";
        consulta += Login;
        consulta +=" and p.PASSWORDMD5 = MD5(";
        consulta +=pass;
        consulta+=")";
        rs =  st.executeQuery(consulta);
        while (rs.next()) {
            count = rs.getInt(1);
        }
        st.close();
        rs.close();
    } catch (SQLException ex) {
       Logger.getLogger(JDBCMySQL.class.getName()).log(Level.SEVERE, null, ex);
    }

    return count;
}

But when I call Login("LOGINPERIT1","PASSWORDMDPERIT") I get this error :

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'LOGINPERIT1' in 'where clause' at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) at com.mysql.jdbc.Util.getInstance(Util.java:408) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2497) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2455) at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1369) at info.infomila.info.JDBCMySQL.Login(JDBCMySQL.java:139) at info.infomila.info.ThreadHandler.run(ThreadHandler.java:56)

use PreparedStatement, it is a lot safer to use. The reason for your error though is that the Strings needs to be single quoted, otherwise it is treating it as a column name — Scary Wombat
Your strings i.e. the login and password are not quoted. Please quote them and check. — Aritra Chatterjee
Perhaps try this: String consulta = "SELECT COUNT(*) FROM Perit WHERE LOGIN = '" + Login + "' AND PASSWORDMD5 = 'MD5(" + pass + "');";. Notice the use of single quote marks for login and password? — DevilsHnd

Java Devil Java Devil · Accepted Answer · 2017-05-23T04:09:11

As mentioned in the comments the problem you are getting is because the strings you are supplying for your login and passwords are not enclosed in single quotes.

The quick way around this is to include single quotes in your defined sql string before and after you are including the passed in values for the log in and password as mentioned by @DevilsHnd in the comments:

String consulta = "SELECT COUNT(*) FROM Perit WHERE LOGIN = '" + Login + 
"' AND PASSWORDMD5 = MD5('" + pass + "');"

However this a is dangerous approach and I highly recommend that you do not use this approach as it leaves your login sql vunerable to SQL Injection.

To avoid this, you can use a PreparedStatement which will take care of sanitizing your inputs for you.

You do this by replacing your statement arguments with a ? and then set them using the methods of the PreparedStatement class.

Below is an example of how you can achieve this:

String consulta = "SELECT COUNT(*) FROM Perit WHERE LOGIN = ? AND PASSWORDMD5 = MD5(?);"
try(PreparedStatement pstmt = con.prepareStatement(consulta))
{
    pstmt.setString(1, login); 
    pstmt.setString(2, pass); 
    try(ResultSet rs = pstmt.executeQuery())
    {
        count = rs.getInt(1);
    }
} 
catch (SQLException ex) 
{
    Logger.getLogger(JDBCMySQL.class.getName()).log(Level.SEVERE, null, ex);
}

Another area of concern in your method is that your Statement and ResultSet are not closed within a finally block - this could lead to memory leaks within your code.

Note my use of a try with resources block - which will close the PreparedStatement and ResultSet as if they were closed in a finally block.

And I'll just leave this xkcd comic here!

JDBC MySQL query syntax

1 Answers