Java JDBC Incorrect Syntax Error

Question

I have this method to load the objects, however when I am running the sql code it is giving me a Syntax error.

public void loadObjects() {
    Statement s = setConnection();

    // Add Administrators
    try {
        ResultSet r = s.executeQuery("SELECT * FROM Administrator;");
        while (r.next()) {
            Administrator getUser = new Administrator();
            getUser.ID = r.getString(2);

            ResultSet r2 = s.executeQuery("SELECT * FROM Userx WHERE ID= {" + getUser.ID + "};");
            getUser.name = r2.getString(2);
            getUser.surname = r2.getString(3);
            getUser.PIN = r2.getLong(4);

            JBDeveloping.users.administrators.add(getUser);
        }
    } catch (Exception e) {
        System.out.println(e);
    }

}

I have tried inserting the curly braces as stated in other questions, but I am either doing it wrong or it doesn't work. This method should be able to load all administrators but I believe it is only inserting half of the ID. The ID that it gets, consists of numbers and char; example "26315G"

the Error - com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '26315'.

Edit -

private java.sql.Connection setConnection(){
    java.sql.Connection con = null;
    try {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        String url = "jdbc:sqlserver://" + host + ";DatabaseName=" + database + ";integratedSecurity=true;";
        con = DriverManager.getConnection(url, username, password);
    } catch(Exception e) {
        System.out.println(e);
    }
    return con;
}

public void loadObjects() {
    java.sql.Connection con = setConnection();

    // Add Administrators
    try {
        PreparedStatement sql = con.prepareStatement("SELECT * FROM Administrator");
        ResultSet rs = sql.executeQuery();
        while (rs.next()) {
            Administrator getUser = new Administrator();
            getUser.ID = rs.getString(2);

            PreparedStatement sql2 = con.prepareStatement("SELECT * FROM Userx WHERE ID=?");
            sql2.setString(1, getUser.ID);
            ResultSet r2 = sql2.executeQuery();

            getUser.name = r2.getString(2);
            getUser.surname = r2.getString(3);
            getUser.PIN = r2.getLong(4);

            JBDeveloping.users.administrators.add(getUser);
        }
    } catch (Exception e) {
        System.out.println(e);
    }

}

What other questions? This isn't JDBC syntax, it is SQL syntax. — Sotirios Delimanolis
– Sotirios Delimanolis, Commented Feb 6, 2014 at 13:38
Why are you using the braces {}. Try the query this way: "SELECT * FROM Userx WHERE ID=" + getUser.ID + ";" without the braces. — Omoro
– Omoro, Commented Feb 6, 2014 at 13:41
Replace the {}'s with single-quotes ' and you'll be fine, or use PreparedStatement's and the setString()method instead. — Anders R. Bystrup
– Anders R. Bystrup, Commented Feb 6, 2014 at 13:41
I have tried different ways using {} or ''. However when i use '' it returns no rows when it should return 1. i might use prepared statement instead if this doesn't work. — Joseph118
– Joseph118, Commented Feb 6, 2014 at 13:45

Jorge_B · Accepted Answer · 2014-02-06 13:44:20Z

5

Actually it is not the way to do that in JDBC. That way, even if you sort your syntax error, your code is prone to sql injection attacks.

The right way would be:

// Let's say your user id is an integer

PreparedStatement stmt = connection.prepareStatement("select * from userx where id=?");
stmt.setInt(1, getUser.ID);
ResultSet rs = stmt.executeQuery();

This way you are guarded against any attempt to inject SQL in your application request parameters

answered Feb 6, 2014 at 13:44

Jorge_B

9,8622 gold badges20 silver badges22 bronze badges

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

4 Comments

Cromax Over a year ago

As generally it is true, please note, that userID is taken from table Administrator, not from request parameters. But I would use PS anyway also for other reasons.

Joseph118 Over a year ago

so when i am using prepared statements the ? stands for an open variable where i have to set it later on by order?

Joseph118 Over a year ago

tweaked my code and changed all statements to prepared statements however the result set is still returning no rows

Cromax Over a year ago

Please read my notes about separate statements and missing r2.next() in my other answer.

Cromax · Accepted Answer · 2014-02-06 14:12:18Z

1

First of all: if you use concurrently result-sets, you must use separate statements for each one of them (you can not share Statement s between two r and r2). And more, you lack r2.next() before reading from it.

On the other hand: it would be much more effective to use PreparedStatement in the loop that to rewrite the query all the time.

So I'd go for something like this:

public void loadObjects() {
    try (
        Statement st = getConnection().createStatement();
        //- As you read (later) only id, then why to use '*' in this query? It only takes up resources.
        ResultSet rs = st.executeQuery("SELECT id FROM Administrator");
        PreparedStatement ps = getConnection().prepareStatement("SELECT * FROM Userx WHERE ID = ?");
        ResultSet r2 = null;
    ) {
        while (rs.next()) {
            Administrator user = new Administrator();
            user.ID = rs.getString("id");
            ps.setInt(1, user.ID);
            r2 = ps.executeQuery();
            if (r2.next()) {
                user.name = r2.getString(2);
                user.surname = r2.getString(3);
                user.PIN = r2.getLong(4);
                JBDeveloping.users.administrators.add(user);
            }
            else {
                System.out.println("User with ID=" + user.ID + " was not found.");
            }
        }
    }
    catch (Exception x) {
        x.printStacktrace();
    }
}

Please note use of Java7 auto-close feature (you didn't close resources in you code). And last note: until you are not separating statements in your queries, as to JDBC documentation, you should not place ';' at the end of statements (in all cases you shouldn't place ';' as the last character in you query string).

edited Feb 6, 2014 at 14:12

answered Feb 6, 2014 at 13:44

Cromax

2,0821 gold badge27 silver badges39 bronze badges

3 Comments

Joseph118 Over a year ago

no wonder it didnt work then! i forgot to read the statement lol. but other then that at least i learned the prepared statement

Joseph118 Over a year ago

i use different languages each day, i get confused sometimes when and when not to use ";". and what do you mean by not closing the resources?

Cromax Over a year ago

Well, you didn't close statement and result-sets. Normally you should use try-catch-finally construct and place all clean-up code in finally clause, eg. Statement s; ...; try {...;} catch () {...} finally {if (s != null) {try {s.close()} catch () {}}} --- and the same on PreparedStatement, ResultSet and probably Connection object.

Leo · Accepted Answer · 2014-02-06 13:43:33Z

0

You should not use {} and you should not append parameters into a SQL query like this.

Remove the curly braces and use PreparedStatement instead.

see http://www.unixwiz.net/techtips/sql-injection.html

answered Feb 6, 2014 at 13:43

Leo

6,5804 gold badges40 silver badges52 bronze badges

Collectives™ on Stack Overflow

Java JDBC Incorrect Syntax Error

3 Answers 3

4 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related