Trim Java String and use in Sql Where Clause

Question

I am trying to get values from MySQL db using a query which gets values from a multiple select. The problem is that I am using StringBuilder to create the query based on the values I am getting. If you see the Where clause used why me, every time a new variable is fetched a comma is added in the end.

How do I remove the last comma added as it is giving me an error in the sql query. Will appreciate any help.

String[] stat = req.getParameterValues("status");
StringBuilder sb = new StringBuilder(200);

if (stat != null) {

    for (int i = 0; i < stat.length; i++) {

        sb.append("'" + stat[i] + "',");

    }

    String app = "WHERE status in (" + sb.toString() + ")";
    String sqlquery = "SELECT * FROM Table " + app + ";";
}

Result Query:

SELECT * FROM Table 
   WHERE status in ('Deployed','PendingDisposal','Available','Reserved','Broken',);

I want to get rid of the last comma after 'Broken'

You code is asking to be SQL injected. Sanitize inputs and use prepared statements. — Gurwinder Singh, Apr 02 '17 at 13:00
Remember [the story of Little Bobby Tables](https://xkcd.com/327/). — Andy Turner, Apr 02 '17 at 13:02

score 3 · Answer 1 · edited Sep 27 '17 at 20:39

Your problem is with comma in the end, you can append your Strings like this :

String del = "";
for (int i = 0; i < stat.length; i++) {
    sb.append(del).append("'" + stat[i] + "'");
    del = ",";
}

But in fact this is not really good idea to use this way, because it can cause SQL Injection or syntax error, you have to use PreparedStatement for example :

for (int i = 0; i < stat.length; i++) {
    sb.append(del).append("?");
    del = ",";
}

try (PreparedStatement ps = connection.prepareStatement(sb) {
    for (int i = 0; i < stat.length; i++) {
       ps.setString(i, stat[i]);
    }
    ResultSet result = preparedStatement.executeQuery();//get results
    ...
}

Finally someone mentioned PreparedStatement. – duffymo Apr 02 '17 at 13:13 — duffymo, Apr 02 '17 at 13:13

score 1 · Answer 2 · edited May 23 '17 at 12:02

You can join a string array with commas using Java 8 streams:

String joined = Arrays.asList(stat).stream()
    .map(s -> "'" + s + "'")
    .collect(Collectors.joining(", "));

But note that joining strings like this is a really bad idea: you are vulnerable to SQL injection attacks, e.g. if one of the element of stat is something like:

'); DROP TABLES Table; --

You are better off building the query using a PreparedStatement. See this question.

Ravi · Answer 3 · 2017-04-02T13:06:45.620

0

Need to update your for loop where you need to append element from array till its second last element and append last element of the array outside the loop.

for(int i=0;i<stat.length-1;i++){  // loop till second last element
sb.append("'"+stat[i]+"',"); //appends , at end 
}
sb.append("'"+stat[stat.length-1]+"'"); // append last element

edited Apr 02 '17 at 13:06

answered Apr 02 '17 at 13:01

Ravi

30,829
42
119
173

1

"Try this" why? What are you trying to demonstrate with this answer? Dumping code isn't useful: you need to explain what you're doing differently. – Andy Turner Apr 02 '17 at 13:03
Sure. I'm gonna explain it. – Ravi Apr 02 '17 at 13:04

score 0 · Answer 4 · answered Apr 02 '17 at 13:08

You can use deleteChartAt method: sb.deleteCharAt(sb.length()-1);

For example:

String[] stat = req.getParameterValues("status");
StringBuilder sb = new StringBuilder(200);

if(stat!=null) {
    for(int i=0;i<stat.length;i++){
        sb.append("'"+stat[i]+"',");
    }
    sb.deleteCharAt(sb.length()-1); //---------> This will remove the last comma
    String app = "WHERE status in ("+sb.toString()+")";
    String sqlquery = "SELECT * FROM Table "+app+";";
}

score -1 · Answer 5 · answered Apr 02 '17 at 13:03

-1

try something this.

 for(int i=0;i<stat.length;i++){

        sb.append("'"+stat[i]+"'");
            if(i!=stat.length-1){
              sb.append(",");
            }

        }

answered Apr 02 '17 at 13:03

Dhiraj

1,430
11
21

downvoter please provide the reason for your vote so that i can improve on it. – Dhiraj Apr 02 '17 at 15:39

score -1 · Answer 6 · answered Apr 02 '17 at 13:08

-1

try String app = "WHERE status in ("+sb.substring(0, sb.length()-2)+")";

answered Apr 02 '17 at 13:08

Durgesh Kumar

91
6

Trim Java String and use in Sql Where Clause

6 Answers6