MYSQL Exception in the statement prepared to use the correct syntax near '?'

advertisements

I have written the following code. But I am getting the MySQL Exception to use the right syntax near '?'. Can you help me to resolve this issue?

public static void inbox(String username) {
    String query_recipient = "SELECT user_id, first_name, last_name FROM user_info WHERE username = '" + username + "' ";
    String query = "SELECT sub, msg FROM message WHERE recipient = ?";
    Statement s = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    ResultSet rs1 = null;
    int userId = 0;

    try {
        s = Connect.con.createStatement();
        rs = s.executeQuery(query_recipient);
        rs.next();
        userId = rs.getInt("user_id");

        ps = Connect.con.prepareStatement(query);
        ps.setInt(1, userId);

        rs1 = ps.executeQuery(query);

        while (rs1.next()) {
            System.out.println(rs1.getString("sub"));
            System.out.println(rs1.getString("msg"));
        }

    } catch (SQLException ex) {
        Logger.getLogger(UserBAL.class.getName()).log(Level.SEVERE, null, ex);
    } //end catch
} //end inbox()

Edit: This is the error message

SEVERE: null
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1
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:408)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2788)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2738)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1617)
at msg_pkg.MsgBAL.inbox(MsgBAL.java:82)
at msg_pkg.MsgBAL.main(MsgBAL.java:116)


I see some mistakes in your code:

  1. You're passing a SQL statement to a prepared statement... and that is not only not necesary, it is wrong. Check the documentation for the executeQuery() method of prepared statements.
  2. After you retreive the users result set, you simply move to the next row... It may work, but the right way to do it is: go to the first row of the result set (with rs.first()).

    That same thing happens some lines below: you move to the next row of the messages result set; you should first move before the first row, and then move to the next (while there's a next row).

  3. (Personal suggestion) The first query is vulnerable to SQL injection attacks. Since you're already using prepared statements, why don't you use a prepared statement to get the user id?
  4. (Personal suggestion), instead of using the try ... catch style (which is not incorrect), I think it would be better to use the try with resources style. That way you force the resources (in this case, the prepared statement(s)) to close as soon as your program is done with them.

Let's clean up your code a bit:

public static void inbox(String username) {
    /*
       First: Avoid SQL injection risks: use a prepared statement
       to get user data.
    */
    String qryRec = "SELECT user_id, first_name, last_name "
                  + "FROM user_info "
                  + "WHERE username = ?";
    String qryMsg = "SELECT sub, msg "
                  + "FROM message "
                  + "WHERE recipient = ?";
    ResultSet rsRec = null,
              rsMsg = null;

    // "try-with-resources"
    try(
        PreparedStatement psRec = Connect.con.prepareStatement(qryRec);
        PreparedStatement psMsg = Connect.con.prepareStatement(qryMsg);
    ) {
        // Set the parameter for psRecipient, and execute the query
        psRec.setString(1, username);
        rsRec = psRec.executeQuery(); // No need to pass the query
                                      // as an argument here; it's
                                      // already prepared

        // Go to the first row of rsRecipient
        rsRec.first();

        // Set the parameter for psMessage, and execute the query
        psMsg.setInt(1, rsRecipient.getInt("user_id");
        rsMsg = psMmsg.executeQuery(); // No need to pass the query
                                       // as an argument here; it's
                                       // already prepared

        // Go to the resultset "header" (i.e. before the first row
        rsMsg.beforeFirst();
        while(rsMsg.next()) {
            System.out.println(rsMsg.getString("sub"));
            System.out.println(rsMsg.getString("msg"));
        }

        // Remember to close the result sets
        try {
            if(rsRec != null)
                rsRec.close();
        } catch(SQLException e) {/*Ignore*/} finally {rsRec = null;}

        try {
            if(rsMsg != null)
                rsMsg.close();
        } catch(SQLException e) {/*Ignore*/} finally {rsMsg = null;}
    } catch(SQLException e) {
        // Handle exception
    }
}

Hope this helps