Java SQL Prepared Statement Issue with Array and Where... IN List

Question

I have an Array List which contains Strings:

ArrayList<String> anrs = new ArrayList<>();

Now I convet those into an SQL array like this:

final String[] data = anrs.toArray(new String[anrs.size()]);
final java.sql.Array sqlArray = connection.createArrayOf("varchar", data);

And now I want to make a prepared statement like this:

statement = connection.createStatement();
String selectSQL = "SELECT * FROM rekopf INNER JOIN repos ON rekopf.rekopfnum=repos.Reposnum WHERE repos.reposart IN ?";
pstatement = connection.prepareStatement(selectSQL);
pstatement.setArray(1, sqlArray);
resultSet = pstatement.executeQuery();

But I got this error:

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.1 unexpected token: ? required:

error occures in this line: pstatement = connection.prepareStatement(selectSQL);

What is my issue in this case? Thanks in advance.

Update When I try it like this:

pstatement.setArray(1, connection.createArrayOf("varchar", data));

Then I got this error

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.1 incompatible data type in conversion

I have done a little bit of googling on this. It seems that the issue is that the ? in your prepared statement is supposed to represent a single value. Most suggestions talk about dynamically creating a string like "(?,?,?,...?)" for as many ? as you like and then setting all of these. However this does remove the efficiency gains you get from using the PS. Though you do gain from the SQL injection protection. Hope this helps? — Bill Naylor, Mar 30 '19 at 13:32
hm poor okay thanks. Can I do this with a normale statement? — Felix, Mar 30 '19 at 13:34
Don't think so, you don't have the setArray(n,array) method. Also you would be setting yourself up for SQL injection if you just manually created the query without the PS ?. I would go for the dynamically (?,?,?,..?) creation method.Oh yes — Bill Naylor, Mar 30 '19 at 13:40
Oh yes and apparently there are limits on the number of parameters a PS can have, depending on which DB. e.g. 1000 for Oracle. — Bill Naylor, Mar 30 '19 at 13:47

Gro · Answer 1 · 2019-04-01T10:29:06.753

java.sql.Array doesn't equate to java Arrays of any datatype. Some DBs have in-build support of Collections and Records types (e.g. Varray in Oracle). java.sql.Array comes handy to send data for such datatypes (mostly parameters to Procedures PL/SQLs) from client side. For any further information you may want to do some reading on Collections, Records and Types support for your database of choice.

For your need, you would simply want to write an SQL where each element from List becomes part of the IN clause

for example: select * from user where name in ('Hans', 'Joe', 'Jane');

    //Your below code snippet, 
    ArrayList<String> anrs = new ArrayList<>(); 
    // may be generated elsewhere as
    // List<String> anrs = Arrays.asList("Hans", "Joe", "Jane");
    // can be flattened to comma separated, quoted String
    String inClause = anrs.stream().map(s -> "'"+s+"'")
                      .collect(Collectors.joining(","));       
    System.out.println("List<String> flattened for IN Clause -> "+inClause);
    // which you will use to create SQL
    String selectSQL = String.format("SELECT * FROM rekopf INNER JOIN repos "
        + "ON rekopf.rekopfnum=repos.Reposnum WHERE repos.reposart IN (%s)", 
        inClause);
    java.sql.PreparedStatement pstatement = 
                            connection.prepareStatement(selectSQL);
    java.sql.ResultSet resultSet = pstatement.executeQuery();

    while(resultSet.next()){
        //TODO - process the result
    }

Binding parameters to PreparedStatement for an SQL with In Clause is not straight forward. I leave this as an exercise for you, in case you want to go above and beyond.

score 1 · Answer 2 · answered Mar 30 '19 at 13:37

1

The problem might be using ? as a placeholder.

Try ...WHERE repos.reposart IN (?)

As found here

answered Mar 30 '19 at 13:37

Lucas Henry

33
6

score 0 · Accepted Answer · answered Mar 30 '19 at 13:41

0

Yes like the others said the ? is the issue.

In this case I do it like this:

String selectSQL = "SELECT * FROM rekopf INNER JOIN repos ON rekopf.rekopfnum=repos.Reposnum WHERE repos.reposart IN (";

            int count = 0;

            for (String anr : anrs){
                selectSQL += "'"+anr+"'";
                count++;
                if (count < anrs.size()){
                    selectSQL += ",";
                }
            }

            selectSQL += ")";

Thats not nice. But super efficient in this case.

Thanks.

answered Mar 30 '19 at 13:41

Felix

5,452
12
68
163

But as I mentioned earlier, you are leaving yourself open to SQL injection, or is this not possible in your case? – Bill Naylor Mar 30 '19 at 14:17
No its not possible its a getter function without any input from outside. no user input just a select of all items. – Felix Mar 31 '19 at 06:17
1

OK, if you're sure. However I have found that this sort of approach can allow cross site scripter attackers to put bad stuff on your database, which can then shoot you through code like this?! If the values on the database are auto generated then you should be OK, however if they are coming from the wild ... you're in danger! – Bill Naylor Apr 07 '19 at 18:42
I agree with @BillNaylor on this. Since it is a list of Strings that you're appending to the select statement, there could be anything in that list. Someone could just put "); DROP SCHEMA public CASCADE;" and if you didn't carefully put your role permissions your whole database is gone. That is, of course, if anrs can be set by a user somehow... – Maverick283 Feb 12 '20 at 12:18

Java SQL Prepared Statement Issue with Array and Where... IN List

3 Answers3