Statement.execute always returns true for a boolean query executed from Java

Question

When I execute a SQL query from Java and store the boolean returned, the query always returns true which shouldn't be the case at all. So I emptied the table and fired the query again, and yet it returns true for the emptied table. I have attached a picture of the table. I want the query to return true or false, so I can store it in Java. Can someone please specify an alternate code for this, please?

This is my code on java for the query.

boolean avail = st.execute("SELECT EXISTS(SELECT * from sales WHERE product='"+n+"' AND ord_date='"+sqlDate+"');");

And this is my code for result set

Statement st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

This is the table, name of the table is 'sales'

the tables' name is 'sales'

I'm new to MySQL, a more specific approach is appreciated.

When you find out what `Statement.execute` returns by looking at the API docs, you will know never to use it again ;) Moreover you should use `PreparedStatement`. The correct way to do what you're doing is to use one with `if(result.next())` — g00se, Aug 08 '21 at 14:55
Your SQL statement is very open to [SQL injection](https://en.m.wikipedia.org/wiki/SQL_injection). — Andrei Odegov, Aug 08 '21 at 16:34
Use `executeQuery` and process the result set (which will have one row with the boolean value) — Mark Rotteveel, Aug 08 '21 at 16:59

score 2 · Answer 1 · answered Aug 08 '21 at 15:38

Statement.execute will return true regardless of what the query returns. You are still supposed to retrieve the actual result of the query.

Returns

true if the first result is a ResultSet object; false if it is an update count or there are no results

As you execute an EXISTS statement, there will always be a result (true or false). The actual value still has to be retrieved:

You must then use the methods getResultSet or getUpdateCount to retrieve the result, and getMoreResults to move to any subsequent result(s).

For reference: https://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#execute-java.lang.String-String

Also note that you are directly embedding strings into your query, this will leave you vulnerable to SQL injections. Please read: How can prepared statements protect from SQL injection attacks?. Recommended reading: Introduction to JDBC

I tried using getResultSet and getUpdateCount to retrieve the result, it gives the following error "getResultSet in interface Statement cannot be applied to give types;" — Gnaneshwaar, Aug 09 '21 at 07:45

score 1 · Answer 2 · answered Aug 09 '21 at 09:12

The return value of Statement.execute() signals whether the query produces a result set (true) or - possibly - an update count (false). The query you execute is a select which will always produce a result set (even if empty). In other words, it will always return true for a select.

If you want to get the result of your query, then the recommend approach is to use executeQuery. However, you are also concatenating values into your query string, which is a bad idea because it leave you vulnerable to SQL injection. The recommended approach is to switch to prepared statements:

try (PreparedStatement pstmt = con.prepareStatement(
      "SELECT EXISTS(SELECT * from sales WHERE product = ? AND ord_date = ?)")) {
    pstmt.setString(1, n);
    pstmt.setDate(2, sqlDate);
    try (ResultSet rs = st.executeQuery() {
        boolean avail = rs.next() && rs.getBoolean(1);
        // use avail...
    }
}

Statement.execute always returns true for a boolean query executed from Java

2 Answers2

Linked

Related