AWS Athena JDBC PreparedStatement

Question

I don't manage to make AWS Athena JDBC driver working with PreparedStatement and binded variables. If I put the desired value of a column directly in the SQL string, it works. But if I use placeholders '?' and I bind variables with setters of PreparedStatement, it does not work. Of course, we know we have to use the second way of doing (for caching, avoid SQL injection and so on).

I use JDBC Driver AthenaJDBC42_2.0.2.jar. I get the following error when trying to use placeholders '?' in the SQL String. The error is thrown when I get the PreparedStatement from the JDBC Connection. It complains about parameters not being found. But I set them after in the code. How can I set the parameters before getting the PreparedStatement :-) ?

java.sql.SQLException: [Simba][AthenaJDBC](100071) An error has been thrown from the AWS Athena client. SYNTAX_ERROR: line 1:1: Incorrect number of parameters: expected 1 but found 0

at com.simba.athena.athena.api.AJClient.executeQuery(Unknown Source)
at com.simba.athena.athena.dataengine.AJQueryExecutor.<init>(Unknown Source)
at com.simba.athena.athena.dataengine.AJDataEngine.prepare(Unknown Source)
at com.simba.athena.jdbc.common.SPreparedStatement.<init>(Unknown Source)
at com.simba.athena.jdbc.jdbc41.S41PreparedStatement.<init>(Unknown Source)
at com.simba.athena.jdbc.jdbc42.S42PreparedStatement.<init>(Unknown Source)
at com.simba.athena.jdbc.jdbc42.JDBC42ObjectFactory.createPreparedStatement(Unknown Source)
at com.simba.athena.athena.jdbc42.AJJDBC42ObjectFactory.createPreparedStatement(Unknown Source)
at com.simba.athena.jdbc.common.SConnection.prepareStatement(Unknown Source)
at com.simba.athena.jdbc.common.SConnection.prepareStatement(Unknown Source)
at ****************************************************
Caused by: com.simba.athena.support.exceptions.GeneralException: [Simba][AthenaJDBC](100071) An error has been thrown from the AWS Athena client. SYNTAX_ERROR: line 1:1: Incorrect number of parameters: expected 1 but found 0
... 37 more

Am I doing something wrong ? Here is the code

    @Test
public void testWhichFails() throws SQLException {
    try (Connection connection = athenaConnexion()) {
        String sql = "select * from my_table where col = ? limit 10";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setInt(1, 30);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    System.out.println("rs.getString(1) = " + rs.getString(1));
                }
            }
        }
    }
}

@Test
public void testWhichWorks() throws SQLException {
    try (Connection connection = athenaConnexion()) {
        String sql = "select * from my_table where col = 30 limit 10";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            //ps.setInt(1, 30);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    System.out.println("rs.getString(1) = " + rs.getString(1));
                }
            }
        }
    }
}

I've had the same issue. Since Athena is for read-only ops, SQL injection should not be an issue. So I just used `String.format()` instead. In your case, it's `String.format("select * from my_table where col = %d limit 10", yourColValue)`. — Andrii Karaivanskyi, Jun 14 '18 at 20:49
@Andrii Karaivanskyi: SQL injection is not only an issue with updates; access to data not referenced by the original query could be a serious concern as well. Also, there's e.g. CREATE TABLE, ALTER TABLE, DROP TABLE statements that will indeed change stuff. This is an incredibly serious issue IMO. — marthursson, Feb 01 '19 at 10:50
@AndriiKaraivanskyi SQL injection is very much an issue even with read-only systems, it can lead to users gaining access to data they are not authorised to access. It is a great way to get your confidential data leaked. — Andrey, Feb 07 '23 at 23:34

score 5 · Answer 1 · edited Apr 12 '19 at 23:01

Athena supports only SQL functions listed here Athena SQL functions which in turn are based on Functions and Operators Presto version 0.172 with the following list of Athena's SQL related limitations. Prepared statements can be used in the new version of Presto Presto Documentation. However, Athena does not support this new version yet. You can always write to the Athena support team to ask for the PREPARE function to be added.

score 3 · Answer 2 · edited Jun 07 '18 at 20:57

3

Currently, I don't think the Athena JDBC jar supports prepared statements with position variables. While using myBatis, the prepared statement variable #{variable} didn't work, while the string replacement ${variable} did.

select * from my_table where col = #{col} limit 10 didn't work
select * from my_table where col = ${col} limit 10 did work

I think the error occurs because the Athena SConnection object doesn't support position variables, but since I don't have the source, I can't verify.

edited Jun 07 '18 at 20:57

8-Bit Borges

9,643
29
101
198

answered Jun 07 '18 at 19:15

jhorner

31
3

1

Sorry to revive this but what does it mean? Can I do a similar technique in Hibernate? – user266391 Apr 16 '20 at 15:05

AWS Athena JDBC PreparedStatement

2 Answers2

Linked