SELECT IN using "StringUtils.join(array, "','");"

Question

I have to perform a query on a DB where since one of the values passed is an array I have used StringUtils.join(array, "','") Here is how I implemented it, this code is part of the server side of a web-service

 public String medico(int age, String sexstr, String etniastr, String[] sintom)  {

      String tes=StringUtils.join(sintom, "','");
      String ris = "no";
      String q;
      String errore = connetti();
        try {
          if (errore.equals("")) {
            Statement st = conn.createStatement();
            //ESECUZIONE QUERY

       q = "SELECT DISTINCT nome FROM malattia WHERE eta='" + age + "' AND sesso='" + sexstr + "' AND etnia='" + etniastr + "' AND sintomi IN('tes')";

                    ResultSet rs = st.executeQuery(q);
                    if (!rs.last()) {
                          ris = "no";
                    } else {
                          ris = "si";
                    }
              } else {
                    ris = errore;
              }
              conn.close();
            } catch (Exception e) {
              ris = e.toString();
            }
            return ris;
        }

The way i pass tes seems to be the problem, when i pass it the way written it gives me an Error 500(internal server error) when i change it to:

         ..... sintomi IN('"+tes+"')";

It gives me zero rows,even though the corresponding data exists in the DB

what is the correct way to resolve this issue? Thanks in advance.

Have you tried looking at the generated query and seeing if it's malformed? — NullUserException, Aug 30 '11 at 23:26
Could you tell me why? The query is working perfectly if I omit the sintomi IN part i mean if i pass the other values and make some changes they work fine, therefore i assume that I somewho dont pass the values of the array sintomi correctly — tutak, Aug 30 '11 at 23:28
That's exactly why I am asking you to output the query; a simple `System.out.println(q)` would do. It *might* not be generating what you expect it to. If it does, try running that query directly in MySQL. — NullUserException, Aug 30 '11 at 23:31
This is a SQL injection hole galore. Please use `PreparedStatement` http://download.oracle.com/javase/tutorial/jdbc/basics/prepared.html Then you can use the IN clause like as descibed in http://stackoverflow.com/questions/2861230/what-is-the-best-approach-using-jdbc-for-parameterizing-an-in-clause — BalusC, Aug 30 '11 at 23:39
Since the code is running on the server-side there is only afew outputs i can get, "ris" actually gets the result of q which either be "si" if the query returns a row and "no" otherwise. when i get error 500 my "ris" outputs "error" which is also specified in the code. But it doesnt output where it occured — tutak, Aug 30 '11 at 23:44
@Student I meant what's the datatype of `sintomi` on the database? MySQL does not have an array type. — NullUserException, Aug 30 '11 at 23:49
From what I can infer, (and since `sintomi` really is symptoms [plural]) it seems like you are storing a serialized array into a RDBMS. And that's a big no-no... — NullUserException, Aug 30 '11 at 23:51
@NullUserException sintomi is a FK in the "malattia", i have another table called sintomi connected to malattia. — tutak, Aug 30 '11 at 23:52
I marked this with "homework", because I have seen this exact question or one very similar at least 10 times in the last week. — Bohemian, Aug 30 '11 at 23:52
@Bohemian: are you sure that they aren't from the same user? Check his question history. — BalusC, Aug 30 '11 at 23:53
@Student You still haven't answered my question: what's the datatype of `sintomi` in the database? MySQL does not have an array type. — NullUserException, Aug 30 '11 at 23:54
@NullUserException I did :) obviously storing multiple values in the DB shows that the DB design is not normalized, however sintomi is Foreign Key of another seperate TABLE "sintomi" this way the tables "malattia" and "sintomi" are connected through a FK constraint, making it possible to store multiple values, or seperate records for every single value. I can post the DB structure if you want.Thanks for the help! — tutak, Aug 31 '11 at 00:15

score 3 · Answer 1 · answered Aug 31 '11 at 00:11

private String MEDICO_SQL = "SELECT DISTINCT nome FROM malattia WHERE eta=? AND sesso=? AND etnia=? AND sintomi IN (%s)";
public String medico(int age, String sexstr, String etniastr, String[] sintom)  {
    try {
        String sql = String.format(MEDICO_SQL, StringUtils.repeat("?", ",", sintom.length));

        PreparedStatement st = conn.prepareStatement(sql);
        st.setInteger(1, age);
        st.setString(2, sexstr);
        st.setString(3, etniastr);
        for (int i=0; i<sintom.length; i++) {
            st.setString(3+i, sintom[i]);
        }
        ResultSet rs = s.executeQuery();
        ... process results ...
    } catch (....) {
       ... handle exceptions ...
    }
}

I'm assuming that the StringUtils you are using is Apache Commons.

Alfredo Osorio · Accepted Answer · 2011-08-30T23:49:29.043

-1

The problem is that you have to add a single quote at the start and at the end besides the comma.

Take a look at this:

@Test
public void testArray() {
    String[] strArray = {"hello", "world", "bye"};

    System.out.println(singleQuoteAndComma(strArray));
}

private String singleQuoteAndComma(String[] strArray) {
    String in = "";
    for (int i = 0; i < strArray.length ; i++) {
        in += "'" + strArray[i] + "'";
        if (i != strArray.length - 1) {
            in += ",";
        }
    }
    return in;
}

Output:

'hello','world','bye'

Use the singleQuoteAndComma method and then append the result string to your query.

I made the correction:

public String medico(int age, String sexstr, String etniastr, String[] sintom)  {

      String tes= singleQuoteAndComma(sintom);
      String ris = "no";
      String q;
      String errore = connetti();
        try {
          if (errore.equals("")) {
            Statement st = conn.createStatement();
            //ESECUZIONE QUERY

       q = "SELECT DISTINCT nome FROM malattia WHERE eta='" + age + "' AND sesso='" + sexstr + "' AND etnia='" + etniastr + "' AND sintomi IN(" + tes + ")";

                    ResultSet rs = st.executeQuery(q);
                    if (!rs.last()) {
                          ris = "no";
                    } else {
                          ris = "si";
                    }
              } else {
                    ris = errore;
              }
              conn.close();
            } catch (Exception e) {
              ris = e.toString();
            }
            return ris;
        }

edited Aug 30 '11 at 23:49

answered Aug 30 '11 at 23:28

Alfredo Osorio

11,297
12
56
84

Thanks for the reply, I just made the changes as you said, but it gives me Error 500 – tutak Aug 30 '11 at 23:34
1

Bad. This doesn't cover values which contain singlequotes by itself like `"don't"`. Use `PreparedStatement`. – BalusC Aug 30 '11 at 23:42
WOW it actually worked!!! obviously a very different approach to the way i was trying but anyways works perfectly :) Thank you all for your time! – tutak Aug 31 '11 at 00:07
1

@BalusC SQL injection protection wasn't an issue since the array "sintom" contains data which is taken from checkboxes, so no way for a use to enter data which could cause problems. Thanks – tutak Aug 31 '11 at 00:10
2

Users can manipulate the obtained HTML source, run some JavaScript on the HTML DOM tree, and/or tamper the HTTP request parameters, etcetera. **Never trust user input!** If I was your tutor, you'd have gotten huge negative points for this. – BalusC Aug 31 '11 at 00:12
1

@Student That's an extremely naive assumption. I could easily create an HTML file: `
`. Then I put whatever I want in sintomi, submit it and you are hosed. – NullUserException Aug 31 '11 at 01:08
This answer is dangerous and opens your application up to **SQL injection**. You do **not** want to join [this list](http://en.wikipedia.org/wiki/SQL_injection#Known_real-world_examples). – Philipp Reichart Aug 31 '11 at 19:57

SELECT IN using "StringUtils.join(array, "','");"

2 Answers2