java.sql.SQLSyntaxErrorException-ORA-01722: invalid number - Prepared statement

Question

I am fixing some SQL Injection issues in the code by removing the replaceAll() in code. I am replacing the named parameters in the query with the placeholders and using in it the prepared statement. I am getting below issue, please advice.

Query:

<entry key= "GET DATA">
select ATYPE, ANAME, AGRID, AVALUE
from ALG_RSV.TERMS
--where AGRID in (':AGRS')  // changed this line
where AGRID in (?) // to this 
order by 2,1

Code:

 {...
    List<String> agreeIDs = getAgree
    String agrID = String.join(",",agreeIDs);
    // String sqlQuery = CSRConsole.getProperty(GET DATA).replaceAll(":AGRS", agrID);// 
    commented out replaceALL 

    String sqlQuery = CSRConsole.getProperty(GET DATA); //Changed like this
    prepStat= Conn.prepareStatement(sqlQuery);
    prepStat.setString(1, agrID);// changed like this, getting issue in this line
    rs = prepStat.executeQuery();
    .....}

Error:

java.sql.SQLSyntaxErrorException-ORA-01722: invalid number

Can someone help me with this? what is the issue here? how to convert named parameters to placeholders?

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2021-11-11T10:28:48

I'm not sure of the entirety of your question, but the Java PreparedStatement statement API does not used named placeholders, it only uses positional placeholders which are indicated by ?. From the official documentation, here is a sample code:

String updateString = "update COFFEES " + "set SALES = ? where COF_NAME = ?";
PreparedStatement updateSales = con.prepareStatement(updateString);
updateSales.setInt(1, e.getValue().intValue());
updateSales.setString(2, e.getKey());
updateSales.executeUpdate();

java.sql.SQLSyntaxErrorException-ORA-01722: invalid number - Prepared statement

1 Answers