CODEWITHSUNDEEP
java
Pawan
Pawan

Reputation: 32321

How to keep Quotes For Making proper SQL Query

When i run the below program 

package com.util;
import java.util.ArrayList;
public class Test {
    public static void main(String[] args) throws Exception {
        ArrayList<String> list_of_symbols = new ArrayList<String>();
        list_of_symbols.add("ABB");
        list_of_symbols.add("ACC");
        list_of_symbols.add("SBIN");
        StringBuilder sb_builder = new StringBuilder();
        for (int i = 0; i < list_of_symbols.size(); i++) {
            sb_builder.append(list_of_symbols.get(i) + ",");
        }
        String sql = "Select * from data where symbol_name IN ("
                + sb_builder.deleteCharAt(sb_builder.length() - 1).toString()
                + ")";
        System.out.println(sql);
    }
}

The Result of SQL IS 

Select * from data where symbol_name IN (ABB,ACC,SBIN)

Where as the expected result should be 

Select * from data where symbol_name IN ('ABB','ACC','SBIN')

Could you please let me know how can i keep Quotes so that it becomes valid SQL

Upvotes: 0

Views: 54

Answers (3)

Jan
Jan

Reputation: 13858

As you know the number of values in your list I'd suggest to put in one ? per IN value and make it a PreparedStatement.

Then simply do

 ps.setString (n, nthString);

In a loop for 1..n parameters and your driver will handle proper escaping.

Like this:

    List<String> list_of_symbols = new ArrayList<String>();
    list_of_symbols.add("ABB");
    list_of_symbols.add("ACC");
    list_of_symbols.add("SBIN");
    StringJoiner join = new StringJoiner(",",
            "Select * from data where symbol_name IN (", ")");
    for (int i = 0; i < list_of_symbols.size(); i++) {
        join.add("?");
    }
    PreparedStatement ps = aConnection.prepareStatement(join.toString());
    for (int i = 0; i < list_of_symbols.size(); i++) {
        ps.setString(i+1, list_of_symbols.get(i));
    }

Upvotes: 0

BlueMoon93
BlueMoon93

Reputation: 2976

When making your list, use this:

sb_builder.append("'"+list_of_symbols.get(i) + "',");

Notice the ' on the string. This works for simple cases only, where strings do not have ' and you're not worried with SQL Injection. For more complex cases, used PreparedStatements and add a list of ?. Then replace the ? with the Strings you want to use.

Upvotes: -1

T.J. Crowder
T.J. Crowder

Reputation: 1073978

Don't use string concatenation to fill in SQL parameters. It's error-prone. Instead, build the SQL with as many ? as you need, and then use a PreparedStatement and as many setString(x, theString) as you need to fill in the ?.

In your case, it would look roughly like this:

package com.util;
import java.util.ArrayList;
public class Test {
    public static void main(String[] args) throws Exception {
        ArrayList<String> list_of_symbols = new ArrayList<String>();
        list_of_symbols.add("ABB");
        list_of_symbols.add("ACC");
        list_of_symbols.add("SBIN");

        // Build the statement
        StringBuilder sql = new StringBuilder(200);
        sql.append("Select * from data where symbol_name IN (");
        for (int i = 0; i < list_of_symbols.size(); i++) {
            sql.append(i == 0 ? "?" : ", ?");
        }
        sql.append(')');

        // Build the PreparedStatement and fill in the parameters
        PreparedStatement ps = someConnection.prepareStatement(sql.toString());
        for (int i = 0; i < list_of_symbols.size(); i++) {
            ps.setString(i + 1, list_of_symbols.get(i));
        }

        // Do it
        ResultSet rs = ps.executeQuery();
    }
}

(This is un-optimized, and dashed off. Some editing is likely required.)

This site has a good explanation of why using string concat for parameters is a bad idea, as well as practical examples of how to do things properly in many languages, including Java.

Upvotes: 6

Related Questions