asked on
how to avoid concatenation while using prepartstatement
Hi Experts,
String acct_nos = parameters.get("ACCT_NOS");//this may have multiple acct nos
ResultSet resultSet= null;
PreparedStatement statement = null;
statement = con.prepareStatement("select distinct ACCOUNT_NUMBER,CODE from ACCOUNTS " +
"where ACCOUNT_NUMBER in(" + acct_nos + ")");
statement.execute()
how to avoid concatenation here
when i try with below code it is printing any value
statement = con.prepareStatement("select distinct ACCOUNT_NUMBER,CODE from ACCOUNTS " +
"where ACCOUNT_NUMBER in(?)");
statement.setString(1,acct_nos);
ResultSet resultSet = statement.getResultSet();
while(resultSet.next()) {
String accno = resultSet.getString(1);
System.out.println("accno is "+accno);
}
Java
Last Comment
ste5an
Unfortunately you can't use a bind parameter/placeholder for set-based queries. So (efficient) concatenation it is, i'm afraid
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
HI CEHJ,
I am using IBM APPSCAN.
Code scanning result showing SQL injection.
For avoiding sql injection I am looking for alternative approach.
I am using IBM APPSCAN.
Code scanning result showing SQL injection.
For avoiding sql injection I am looking for alternative approach.
Yes, you'd probably do better to follow ste5an
ASKER
Hi Experts
i have tried with the above code.i am getting the reslut.
but still it is showing sql injection.
why what is the wrong in this code.
String[] listvals = acct_nos.split(",");
StringBuilder sb = new StringBuilder();
sb.append("select distinct ACCOUNT_NUMBER,CODE from ACCOUNTS " +
"where ACCOUNT_NUMBER in(");
for (String val : listvals) {
sb.append("?,");
}
String qry = sb.deleteCharAt( sb.length() -1 ).toString()+ ")";
statement = con.prepareStatement(qry); //still this line is showing sql injection
int i = 1;
for (String accnos : listvals) {
statement.setString(i++,accnos);
}
i have tried with the above code.i am getting the reslut.
but still it is showing sql injection.
why what is the wrong in this code.
No - that's still set-based. Try to append ORs (or equals)
Nothing, the scanner is too sensitive.
But you should extract the query string building to a separate method, use only a parameter (int) for the number of required placeholders, use string consts to build it.
Maybe the scanner does detect this correctly. But when not, you can simply verify, that you now don't have SQL injection. And maybe you can annotated exceptions for the scanner in code.
But you should extract the query string building to a separate method, use only a parameter (int) for the number of required placeholders, use string consts to build it.
Maybe the scanner does detect this correctly. But when not, you can simply verify, that you now don't have SQL injection. And maybe you can annotated exceptions for the scanner in code.
ASKER
Hi Experts,
I have refreshed IDE and tried one more time the above code resolves the injection.sql issue
I have refreshed IDE and tried one more time the above code resolves the injection.sql issue
ASKER
Hi ste5an,
can you please elaborate more using an ad-hoc query way,
I still didn't understand how to pass the input values to--@AN01,@AN02...
can you please elaborate more using an ad-hoc query way,
I still didn't understand how to pass the input values to--@AN01,@AN02...
Well, the indicator for SQL injection is string concatenation with parameters or variables. Thus you need a (constant) literal. This means that you must specify the entire SQL statement including all possible parameters with their name (which is inefficient when you need large numbers of parameters).
Thus my sample has ten hardcoded parameters, which must be filled before you execute it. While you're limited to 10 parameters, you still need to ensure that it also works for e.g. only one parameter. This simple means, that you assign the first value to all parameters, which you don't need. As the WHERE predicate uses OR to concatenate them, this redundant, but it makes your SQL statement for sure SQL-injection-proof
Thus my sample has ten hardcoded parameters, which must be filled before you execute it. While you're limited to 10 parameters, you still need to ensure that it also works for e.g. only one parameter. This simple means, that you assign the first value to all parameters, which you don't need. As the WHERE predicate uses OR to concatenate them, this redundant, but it makes your SQL statement for sure SQL-injection-proof