You have an error in your SQL syntax check the manual JAVA

Question

I'm getting this error while trying to run this code 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 ''And StatusMembership=Active' at line 1

public boolean checkLenderToIfExists(BorrowedBook borrowedBook) throws SQLException {
    try {
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM library_students.student WHERE StudentID='"+ borrowedBook.getLenderTo()+"'And Permission=1"+"'And StatusMembership='Active'");
        if (!(rs.next())) {
            return false;
        }
        rs.close();
        return true;
    } catch (SQLException ex) {
        ex.printStackTrace();
        return false;
    }

'And I think you'll need to remove that first ' there. Or you need one before the 1 — Stultuske, Jan 28 '19 at 13:33
The error is telling you there's an error in your SQL code, not in the Java code which *generates* your SQL code. Start by looking at the runtime value of `"SELECT * FROM library_students.student WHERE StudentID='"+ borrowedBook.getLenderTo()+"'And Permission=1"+"'And StatusMembership='Active'"` when the error occurs. — David, Jan 28 '19 at 13:33
To prevent more errors of this type and SQL injection, I suggest you use prepared statements — ChatterOne, Jan 28 '19 at 13:37

Naya · Answer 1 · 2019-01-28T13:53:43.433

4

You're generating the next query :

SELECT * FROM library_students.student WHERE StudentID='10'And 
Permission=1'And StatusMembership='Active'

Probably the error is because of extra single quotes ' in Permission=1'And

edited Jan 28 '19 at 13:53

answered Jan 28 '19 at 13:33

Naya

850
6
19

Darshan Mehta · Accepted Answer · 2019-01-28T14:10:29.663

2

It might be happening because of the spacing in the query. Try the following format with PreparedStatement:

PreparedStatement preparedStatement = conn.prepareStatement("SELECT * FROM "
        + "library_students.student WHERE StudentID= ? "
        + "AND Permission=1 AND StatusMembership='Active'");
preparedStatement.setInt(1, borrowedBook.getLenderTo());
ResultSet rs = preparedStatement.executeQuery();

Also, here's some bonus reading on why you should use PreparedStatement in this case.

edited Jan 28 '19 at 14:10

answered Jan 28 '19 at 13:39

Darshan Mehta

30,102
11
68
102

Darshan I thought that would have been the issue, the missing spaces (hence my upvote), but well... check this [DB Fiddle](https://www.db-fiddle.com/f/pmR3g2MYb9hj7Qw5qxcDEZ/0) – Cid Jan 28 '19 at 14:08
@Cid have you tried copy pasting the above query in the console? I think it might be both spacing and the quotes. However, I suggested `PreparedStatement` solution to get rid of quotes and have a clean query. – Darshan Mehta Jan 28 '19 at 14:10
Do you mean Naya's one? – Cid Jan 28 '19 at 14:11
I totally agree with you concerning the spaces and the prepared statements (too much people here don't care about it) but it looks like MySQL accept such statements `StudentID='10'And Permission=1` – Cid Jan 28 '19 at 14:13
@Cid Agreed, MySQL does accept such parameters. I have updated my answer. – Darshan Mehta Jan 28 '19 at 14:15
The real issue was a typo, as stated Naya in his answer. I voted to close since this is typographical – Cid Jan 28 '19 at 14:16

You have an error in your SQL syntax check the manual JAVA

2 Answers2