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
– Stultuske, Commented Jan 28, 2019 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
– David, Commented Jan 28, 2019 at 13:33
To prevent more errors of this type and SQL injection, I suggest you use prepared statements — ChatterOne
– ChatterOne, Commented Jan 28, 2019 at 13:37

Naya · Accepted Answer · 2019-01-28 13:53:43Z

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, 2019 at 13:53

answered Jan 28, 2019 at 13:33

Naya

8806 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Darshan Mehta · Accepted Answer · 2019-01-28 14:10:29Z

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, 2019 at 14:10

answered Jan 28, 2019 at 13:39

Darshan Mehta

31k14 gold badges75 silver badges111 bronze badges

6 Comments

Cid Over a year ago

Darshan I thought that would have been the issue, the missing spaces (hence my upvote), but well... check this DB Fiddle

Darshan Mehta Over a year ago

@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.

Cid Over a year ago

Do you mean Naya's one?

Cid Over a year ago

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

Darshan Mehta Over a year ago

@Cid Agreed, MySQL does accept such parameters. I have updated my answer.

|

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related