SQLException: Invalid parameter index 1 only with PreparedStatement

Question

I have got a webapp(JSP/Servlet) with Tomcat8 + SQL Server2012 JDBC Driver Type 4: JTDS old version 1.2.5 (http://jtds.sourceforge.net/)

I change this kind of query, adding Prepared Statement (server pagining)

    Sting DDXsql = "SELECT '?' *, ( DDX_RECORD_COUNT / '?' + 1 ) AS DDX_PAGE_COUNT 
FROM 
( SELECT '?' * 
FROM ( SELECT '?' *, 
             (SELECT COUNT(*) " + "FROM " 
          + session.getAttribute("DatabaseName") + ".G1_grid " 
          + sqlFrom 
          + sqlWhere + " "    
            + " )  AS DDX_RECORD_COUNT " 
        + "FROM " + session.getAttribute("DatabaseName") + ".G1_grid " 
        + sqlFrom 
        + sqlWhere + " " 
        +    " ORDER BY '?' '?' , '?' '?' ) AS TMP1 ORDER 
          BY '?' '?', '?' '?') AS r ORDER BY '?' '?', '?'  '?'";

Parameters:

String top1 = DBManager.getTOP(request, "TOP " + Integer.valueOf((String)ResourceManager.findData("pageSize", request)));
                Integer pagesizeInt = Integer.valueOf((String)ResourceManager.findData("pageSize", request));
                String top2 = DBManager.getTOP(request, "TOP " + Integer.valueOf((String)ResourceManager.findData("ddxrecordcount", request))); 
                String top3= DBManager.getTOP(request, "TOP " + Integer.valueOf((String)ResourceManager.findData("toRange", request)));
                String notSortStr = (String)ResourceManager.findData("notSort", request);
                Object[] values = new Object[] {
                top1,               
                pagesizeInt,
                top2,           
                top3,
                SortKey,
                Sort,
                TotalSortKey,
                Sort,
                SortKey,
                notSortStr,
                TotalSortKey ,          
                notSortStr,     
                SortKey,
                Sort,
                TotalSortKey,
                Sort
                };

Before, I didint use PreparedStatement I have this kind of query (replace "?" with the Object array values, without StringEscapeUtils):

    String DDXsql = "SELECT " + 
DBManager.getTOP(request, "TOP " 
+ Integer.valueOf(StringEscapeUtils.escapeSql((String)ResourceManager.findData("pageSize", request)))) + " *, 
( DDX_RECORD_COUNT / " + Integer.valueOf(StringEscapeUtils.escapeSql((String)ResourceManager.findData("pageSize", request))) + " + 1 ) AS DDX_PAGE_COUNT FROM 
( SELECT " 
+ DBManager.getTOP(request, "TOP "
 + Integer.valueOf(StringEscapeUtils.escapeSql((String)ResourceManager.findData("ddxrecordcount", request)))) 
+ " * FROM ( SELECT " + DBManager.getTOP(request, "TOP " + Integer.valueOf(StringEscapeUtils.escapeSql((String)ResourceManager.findData("toRange", request)))) 
+ " *, (SELECT COUNT(*) " 
+ "FROM " + session.getAttribute("DatabaseName") + ".G1_grid " + sqlFrom + sqlWhere + " " + " )  AS DDX_RECORD_COUNT " 
+ "FROM " + session.getAttribute("DatabaseName") 
+ ".G1_grid " + sqlFrom + sqlWhere + " " + " ORDER BY "
 + StringEscapeUtils.escapeSql(SortKey) + " " + StringEscapeUtils.escapeSql(Sort) + ", " 
+ StringEscapeUtils.escapeSql(TotalSortKey) + " " 
+ StringEscapeUtils.escapeSql(Sort) + ") AS TMP1 ORDER BY "
 + StringEscapeUtils.escapeSql(SortKey) + " " 
+ StringEscapeUtils.escapeSql((String)ResourceManager.findData("notSort", request)) 
+ ", " + StringEscapeUtils.escapeSql(TotalSortKey) + " " 
+ StringEscapeUtils.escapeSql((String)ResourceManager.findData("notSort", request)) + " ) AS r ORDER BY " 
+ StringEscapeUtils.escapeSql(SortKey) + " " 
+ StringEscapeUtils.escapeSql(Sort) + ", " 
+ StringEscapeUtils.escapeSql(TotalSortKey) 
+ " " + StringEscapeUtils.escapeSql(Sort) + " ";

The last query runs without error, System.out of this query give this for example:

SELECT TOP 20 *, ( DDX_RECORD_COUNT / 20 + 1 ) AS DDX_PAGE_COUNT 
FROM 
( SELECT TOP 20 * FROM 
               ( SELECT TOP 20 *, 
                   (SELECT COUNT(*) 
                     FROM SuiteMA_DIP.dbo.G1_grid  
                  WHERE 1 = 1   )  AS DDX_RECORD_COUNT 
                FROM SuiteMA_DIP.dbo.G1_grid  WHERE 1 = 0   ORDER BY DATA_ISCRIZIONE_ORDER DESC, SOGGETTO_RILEVANTE_PAID DESC) AS TMP1 ORDER BY DATA_ISCRIZIONE_ORDER ASC, SOGGETTO_RILEVANTE_PAID ASC ) AS r ORDER BY DATA_ISCRIZIONE_ORDER DESC, SOGGETTO_RILEVANTE_PAID DESC 

But when i run sql with preparedStatement:

java.sql.SQLException: Invalid parameter index 1.
    at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.getParameter(JtdsPreparedStatement.java:340)
    at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setParameter(JtdsPreparedStatement.java:409)
    at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setObjectBase(JtdsPreparedStatement.java:395)
    at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setObject(JtdsPreparedStatement.java:667)
    at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:188)
    at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:188)
    at it.netbureau.jfx.db.SQLDBManager.execSQL(SQLDBManager.java:57)
    at it.netbureau.jfx.db.SQLDBManager.execSQL(SQLDBManager.java:78)
    at org.apache.jsp.G1.select_jsp._jspService(select_jsp.java:691)

The java method execute the query :

class jfx.db.SQLDBManager.execSQL:

public Object execSQL(PreparedStatement stmt, Object values[], String xmlId)
        throws SQLException
    {
        Object result = null;
        if(stmt == null)
            return null;
        try
        {
            for(int i = 0; i < values.length; i++)
                if(values[i] == null)
                    stmt.setNull(i + 1, 4);
                else
                    stmt.setObject(i + 1, values[i]); <--this give exception!

            if(stmt.execute())                  result = transform(stmt.getResultSet(), xmlId);
        }
        catch(SQLException ex)
        {
            rollback();
            throw ex;
        }
        return result;
    }

What's wrong?

Thank you very much

roby

Answer 1

Your query does not contain any parameters, a '?' is just a literal string with a question mark in it, it is not a parameter.

You also can't parameterize object names like column names and clauses (like a TOP 20), so even if you'd change it to - for example - order by ?, ... it wouldn't work, as you'd be sorting by the string value (which would be the same for all rows, so effectively you wouldn't be sorting at all).

To do what you want to do you will need to concatenate the column names (and other clauses) into the query string. This also means that you might open yourself up to SQL injection: be sure to check the values carefully (for example against a whitelist of allowed values).