nested exception is java.sql.SQLException: Invalid parameter index 1

Question

I get following Error, but I can not understand why:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [UPDATE da_tracking SET ins_name= xyz, ins_dev_scripted = False WHERE ins_ID = 12]; nested exception is java.sql.SQLException: Invalid parameter index 1.

------------------------ dao class-----
public int save(DboBean record) {
        // TODO Auto-generated method stub
        String sql = "UPDATE da_tracking"
                    + " SET ins_name= " + record.getDboDevName()+ "," 
                    + " ins_dev_scripted = " + record.getDevScripted()
                    + " WHERE ins_ID = " + record.getDboId();
        Object[] params = new Object[] {record.getDboDevName(), record.getDevScripted()};
        int[] types = new int[]{Types.VARCHAR, Types.BIT};
        return jdbcTemplate.update(sql, params, types);
    }
----------------------------Junit-----
bean.setDboDevName("xyz");
bean.setDboId(12);
int rowsAffected =  objDao.save(bean);

    System.out.println("Object is updated [" + bean.getDboId() + ", " + bean.getDboDevName() + 
            ", " + bean.getDevScripted() + "]");

Do you know why?? My delete & read Methods work.

Adam Batkin · Accepted Answer · 2012-08-29 15:42:06Z

The error you are seeing is because you are passing variables in to the params and types arrays, but you haven't put placeholders for those bind variables in your queries:

String sql = "UPDATE da_tracking"
            + " SET ins_name= ?," 
            + " ins_dev_scripted = ?"
            + " WHERE ins_ID = ?"
Object[] params = new Object[] {record.getDboDevName(), record.getDevScripted(), record.getDboId()};
int[] types = new int[]{Types.VARCHAR, Types.BIT, Types.INTEGER};
return jdbcTemplate.update(sql, params, types);

Internally, Spring is doing something like this:

PreparedStatement stmt = conn.prepareStatement("...your sql...");
stmt.setString(1, dboDevName); // this will fail, since there is no bind variable
                               // with index 1
...

You should NEVER build SQL queries by concatenating external data. In the best case, it will result in queries that will randomly fail if someone puts a strange quote or escape character in the data, and in the worst case, a major security hole which will compromise your system.

Garbage · Accepted Answer · 2012-08-29 15:49:46Z

1

public int save(DboBean record) {

        String sql = "UPDATE da_tracking"
                    + " SET ins_name= ?"+"," 
                    + " ins_dev_scripted = ?" 
                    + " WHERE ins_ID = ?";
        Object[] params = new Object[] {record.getDboDevName(), record.getDevScripted(), record.getDboId()};
        int[] types = new int[]{Types.VARCHAR, Types.BIT, Types.INTEGER}; // Change 3rd parameter type here
        return jdbcTemplate.update(sql, params, types);
    }

----------------------------Junit-----
bean.setDboDevName("xyz");
bean.setDboId(12);
int rowsAffected =  objDao.save(bean);

    System.out.println("Object is updated [" + bean.getDboId() + ", " + bean.getDboDevName() + 
            ", " + bean.getDevScripted() + "]");

Does this work?

edited Aug 29, 2012 at 15:49

answered Aug 29, 2012 at 15:36

Garbage

1,53011 silver badges24 bronze badges

1 Comment

Adam Batkin Over a year ago

You are correct that bind variables (paramaterized queries) should be used, but that's not the source of this error. You are right that the variable (probably only ins_name) needs to be quoted, but the SQL parser hasn't gotten that far into parsing the query yet.

Collectives™ on Stack Overflow

nested exception is java.sql.SQLException: Invalid parameter index 1

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related