mySQL nested loop wont execute

Question

I'm working on a SOAP based webservice where in a part of it i have to perform some queries on the database using nested loop, the problem is that the inner loop just gets executed for ONE time only, before giving up.This is the code:

          for(int i=0; i<selec.length; i++){
                for(int j=0; j<sintom.length;j++){
                    var[(i*sintom.length)+j] = "INSERT INTO malattia (nome, eta,  descrizione, sesso, etnia, sintomi) "
                + "VALUES ('" + malattia + "','" + eta + "','" + descrizione + "','" +  sexarra[0] + "','" + selec[i] + "','" + sintom[j] + "')";
        }

      }

This is where the queries are supposed to get executed:

        if (errore.equals("")) {
              try {
                    Statement st = conn.createStatement();
      for(int i=0; i<selec.length; i++){
        for(int j=0;j<sintom.length;j++){

                     st.executeUpdate(var[(i*sintom.length)+j]);}}

What happens is that no matter the size of select it will work fine as long as the length of sintom is 1,bigger than 1 and it wont work.

Thanks for your expert advices, always appreciated!

Try creating a different Statement object for each query. Also, fix your SQL injection vulnerability. — cdhowie
– cdhowie, Commented Aug 23, 2011 at 21:02
Could you elaborate abit more? You mean i should create statement inside the loops? How could i protect myself against SQL injection? — rosa
– rosa, Commented Aug 23, 2011 at 21:10
Yes, create the statement object, execute the query, and then destroy the object -- for each query. See this question and the corresponding answer for details on using prepared statements to secure yourself against SQL injection attacks. — cdhowie
– cdhowie, Commented Aug 23, 2011 at 21:15
Thanks alot, ill be back after implementing what you advised. — rosa
– rosa, Commented Aug 23, 2011 at 21:17
@cdhowie: the idea of a prepared statement is to reuse it multiple times with different parameters. This is where it becomes more efficient than a regular statement. It shouldn't be destroyed and recreated for each query. It's always a good idea to use them even for one-shot queries to avoid SQL injections, though. — JB Nizet
– JB Nizet, Commented Aug 23, 2011 at 21:24

JB Nizet · Accepted Answer · 2011-08-23 21:22:37Z

Your use-case is a perfect example of a case where a prepared statement should be used. Read more about them in the JDBC tutorial.

Using a prepared statement would allow

avoiding SQL injection attacks. You should never use string concatenation to build your SQL query. A malicious user could enter some special value which would completely change the meaning of your query. A non-malicious user could enter special characters (quotes, for example) which would make the query fail because it's not syntaxically correct.
letting the database prepare the execution plan only once, for all the insert queries you're executing. Indeed, the query is always the same. Only the parameters change.

So, the code should look like this:

PreparedStatement ps = conn.prepareStatement("INSERT INTO malattia (nome, eta, ...) values (?, ?, ...)");
for (int i= 0; ...) {
    for (int j = 0; ...) {
        ps.setString(1, malattia);
        ps.setString(2, eta);
        ...
        ps.executeUpdate();
    }
}

umbr · Accepted Answer · 2011-08-23 21:25:01Z

1

Try to use PreparedStatement and its Batch capability instead of plain query to simplify code and prevent SQL-injection.

answered Aug 23, 2011 at 21:25

umbr

4322 silver badges4 bronze badges

Collectives™ on Stack Overflow

mySQL nested loop wont execute

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related