How to make select query with StringTokenizer?

Question

I'm trying to make a query with selected fields for final user from view (JSP) to controller, but I don't know how.

For example, I have this parameters from view (JSP)

IDUSER,>,2,OR,USERNAME,=,'KURT'

So, I'll want to have something like this,

SELECT IDUSER, USERNAME FROM TABLE_NAME WHERE IDUSER > 2 OR USERNAME = 'KURT'

but I have next result

SELECT null FROM TABLE_NAME WHERE IDUSER > 2 OR USERNAME = 'KURT'

I'm parsing string with StringTokenizer class, where query is: String query = request.getParameter("data"); and data is IDUSER,>,2,OR,USERNAME,=,'KURT'.

StringTokenizer field = new StringTokenizer(query, ",");
nFields = field.countTokens();
System.out.println("nFields: " + nFields);
String[] fields = new String[nFields];
for(int i = 0; i < fields.length; i++) {
    while(field.hasMoreTokens()) {
         fields[i] = field.nextToken();
    }
    System.out.println("fields[i]: " + fields[i]);
    myQuery = "SELECT " + fields[i] + " FROM "+tableName+ " WHERE ";
    System.out.println("myQuery 1: " + myQuery);
}
StringTokenizer token= new StringTokenizer(query, "|,");
while(token.hasMoreTokens()) {
     myQuery = myQuery + token.nextToken() + " ";
}
System.out.println("QUERY RESOLVED: " + myQuery);

PLEASE HELP ME

This approach is vulnerable to SQL injection. Don't use it on a public-facing web site. — artbristol, Sep 20 '12 at 08:25
Column's names and where-clauses are mixed up in this query. Better to aquire them separetly. — Yegoshin Maxim, Sep 21 '12 at 06:51

score 1 · Accepted Answer · answered Sep 21 '12 at 06:39

Here is the solution after minor tweak in your query (redefined the separators)

public static void main(String[] args) {
        // Redefine the separators as single , separators is difficult to process
        //You would need to define possible operators like this (#OR# , #AND# ) ,surrounded by special characters to identify.
        String query ="IDUSER_>_2#OR#USERNAME_=_'KURT'";
        String tableName="TESTTABLE";
        String operator=null;
        //you can choose operator conditionally
        if(query.contains("#OR#")) operator="#OR#";
//      if(query.contains("#AND#")) operator="#AND#";
        //Used split instead of Tokenizer.
        String cols[]= query.split(operator);
        String myQuery = "SELECT ";
        String select="";
        for(String col:cols){
            if(!select.isEmpty()){
                select+=" , ";
            }
            // Only the first element is retrieved (for select)
            select+=col.split("_")[0];
        }
        myQuery+=select+" FROM "+tableName+ " WHERE ";
        // Removes all special charecters (like,  # and _ with white space)
        String subQuery = query.replaceAll("#", " ");
        subQuery=subQuery.replaceAll("_", "");
        myQuery+=subQuery;
        System.out.println("QUERY RESOLVED: " + myQuery);

    }

Note : ',' is replaced with '_' and operators are surrounded by '#'

Cheers!!

score 0 · Answer 2 · answered Sep 20 '12 at 07:45

0

I think the problem is this line in your while loop:

myQuery = "SELECT " + fields[i] + " FROM "+tableName+ " WHERE ";

This will keep changing the value of myQuery as the while loop executes.

Maybe you need to replace this with:

myQuery = "SELECT " + fields[0] + " FROM "+tableName+ " WHERE ";
break;

I am assuming your selection criteria is the first field in the parameter from your view.

answered Sep 20 '12 at 07:45

Luhar

1,859
2
16
23

No, user final can selected 1 or more fields from view (JSP) – hekomobile Sep 20 '12 at 07:50
OK, so how do you decide what the user can select and what should be the constraint? I.e. how do you find what goes after the SELECT and what goes after the WHERE? Please see Thor84no's comment to the original question above. – Luhar Sep 20 '12 at 07:54
The query he wants actually includes two column names, but nowhere does he have the information available to show him that it's supposed to be two (that has to be inferred from the column names in his magic `String`). Assuming these columns are variable it's going to take looking up in a list of possible column names and counting the number of fields, then including them properly. – Vala Sep 20 '12 at 07:55

score 0 · Answer 3 · answered Sep 20 '12 at 07:45

Do not see where does IDPERFIL come from. Also, I don't like this:

while(field.hasMoreTokens()) {
     fields[i] = field.nextToken();
}

That will iterate tokenizer to the end, and stop at last element. I am sure you don't want this. Fix that, tell where IDPERFIL come from, and then, maybe, you'll understand answer by yourself. Otherwise, I'll try to help further.

How to make select query with StringTokenizer?

3 Answers3