Having problems passing empty strings from webpage to select query using AND and Where

Question

I am tryin to fetch values from my sql database based on the inputs from a webpage using a select query. I have pasted the query and bit of the code below. The problem is that the query needs input for each and every variable to fetch data since I am using AND. if i dont input a certain value, It will not throw out any output. Please tell me how can I get data from the database even if some of the variables have empty strings.

     String host = reqt.getParameter("hostname");
     String uuid = reqt.getParameter("cinum");
     String cdir = reqt.getParameter("custcode");
     String customer = reqt.getParameter("custname");
     String mgip = reqt.getParameter("mgmtip");
     String cusip = reqt.getParameter("custip");
     String bakip = reqt.getParameter("backip");
     String ismtick = reqt.getParameter("ismticket");
     String tickmean = reqt.getParameter("ticketmean");
     String mgtlev = reqt.getParameter("mgmtlvl");
     String ptchcat = reqt.getParameter("patchcat");
     String ptchsc = reqt.getParameter("patchsch");
     String vmsite = reqt.getParameter("site");
     String vmcep = reqt.getParameter("cep");
     String vmscope = reqt.getParameter("scope");
     String os = reqt.getParameter("platform");
      String plattype = reqt.getParameter("ostype");
   Statement stmt = null;
   Class.forName("com.mysql.jdbc.Driver");
   Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/CMS_SCS_PORTAL", "root", "test");
  String sql = "Select * from inventory_table where ISNULL(hostname) = '"+host+"' AND ISNULL(uuid) = '"+uuid+"' AND ISNULL(cdir) = '"+cdir+"' AND ISNULL(customer) = '"+customer+"' AND ISNULL(management_ip) = '"+mgip+"' AND ISNULL(customer_ip) = '"+cusip+"' AND ISNULL(backup_ip) = '"+bakip+"' AND ISNULL(ism_ticket_state) = '"+ismtick+"' AND ISNULL(ticket_state_meaning) ='"+tickmean+"' AND ISNULL(management_level) = '"+mgtlev+"' AND ISNULL(patch_category) = '"+ptchcat+"' AND ISNULL(patch_schedule) = '"+ptchsc+"' AND ISNULL(host_site) = '"+vmsite+"' AND ISNULL(VM_Cep) = '"+vmcep+"' AND ISNULL(VM_Scope) = '"+vmscope+"' AND ISNULL(Operating_System) = '"+os+"' AND ISNULL(Operating_System_Type) = '"+plattype+"' LIMIT 10000";

stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql);

`ISNULL` returns a boolean (1 or 0). What are you trying to do? — shmosel, Jan 20 '17 at 09:26
I am trying to input only 2 values from the webpage lets say "custode" and "site". but i think the query expects all values to be entered and it is not able to or not taking empty values. Also you can ignore Isnull. — shahed, Jan 20 '17 at 09:34
Are you trying to match *any* parameter, or *all* non-empty parameters? — shmosel, Jan 20 '17 at 09:36

score 1 · Accepted Answer · answered Jan 20 '17 at 09:36

Three tips here:

Use prepareStatement, placeholders, and bound parameters.
I think you mean COALESCE(fieldname, '') instead of ISNULL(fieldname)
Pay close attention to data types. I am not sure how MySQL handles strings as Booleans, given that they don't really have a native true Boolean type (I think integers are used internally iirc).

The first step is to mock up your query and try it at the command line in your rdbms and make sure it does what you want it to do. Fix that first. Then rewrite using prepareStatement and bound parameters.

It is not clear what this query is supposed to do in your code sample so unfortunately that's probably the best we can do.

Hmm... maybe OP meant `IFNULL(fieldname, '')`. Though it doesn't sound like that's really the goal either. — shmosel, Jan 20 '17 at 09:37

score 0 · Answer 2 · answered Jan 20 '17 at 09:37

0

How about using if statements to control creating the select string?

if(! host.isEmpty()) {  
   sql += "WHERE hostname = '" + host +"'";
}

And so on for every variable...

answered Jan 20 '17 at 09:37

Inuendo

67
1
8

SQL injection issues? – Chris Travers Jan 20 '17 at 09:42
Can u elaborate a little more? this one looks promising – shahed Jan 20 '17 at 09:43
here somthing to read about SQL Injection: https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet and dynamic query creation: http://stackoverflow.com/questions/2041312/how-to-dynamically-generate-sql-query-based-on-user-s-selections – Inuendo Jan 20 '17 at 10:02
The answer is just to give a hint on how you can control the creation of the query to avoid WHERE claus on empty variables.. – Inuendo Jan 20 '17 at 10:08

Arnaud · Answer 3 · 2017-01-20T10:18:18.123

Consider building the query depending on the available parameters.

To do this, you may populate a Map of pairs "parameter name"/"SQL clause" (we will use PreparedStatement with placeholders, that's why you see ? placeholders).

For each of those parameters, check if it is present and valid in the reqt object.

If it is, append its SQL clause to the query, and store this parameter name in the list .

Once the query is built, create a PreparedStatement with it.

For each stored (valid) parameter, bind its value to its placeholder in the PreparedStatement.

// create a Map of SQL clauses parts
Map<String,String> paramQueryMap = new HashMap<>();

paramQueryMap.put("hostname"," ISNULL(hostname) = ? ");
paramQueryMap.put("cinum"," ISNULL(cinum) = ? ");
// .... and so on



// list of the valid parameters
List<String> foundParameters = new ArrayList<>();

// build the query
StringBuilder builder = new StringBuilder();

builder.append("SELECT * FROM inventory_table");

// browse all the parameters of the map
for (String param : paramQueryMap.keySet()) {

    String reqtParam = reqt.getParameter(param);

    // valid parameter (not null, not empty), let's add its sql part, and add this parameter to the foundParameters list
    if (reqtParam != null && !reqtParam.trim().equals("")) {

        foundParameters.add(reqtParam);

        if (foundParameters.size() == 0) {// first filter clause, add a WHERE
            builder.append(" WHERE ");
        } else { // not the first filter, add AND
            builder.append(" AND ");
        }

        // add the sql clause of this parameter
        builder.append(paramQueryMap.get(param));
    }

}

PreparedStatement statement = conn.prepareStatement(builder.toString());

// replace placeholders for each valid parameter

int paramCounter = 1; // placeholders indexes start at 1

for (String foundParameter : foundParameters) {

    statement.setString(paramCounter, reqt.getParameter(foundParameter));

    paramCounter++;
}

ResultSet rs = statement.executeQuery();

Please note that this example assumes that the query in your question is correct, and that setString will fit all of your used column types (see the comments and the other answers).

Also note that you could use another Map of "parameter name"/"parameter value" of the valid parameters from the request object, rather than calling getParameter twice for each parameter, but in that case the insertion order will be important and you'd better use a LinkedHashMap.

@shahed : You're welcome. Consider accepting the answer if it helped, thanks :) — Arnaud, Jan 20 '17 at 15:42

Having problems passing empty strings from webpage to select query using AND and Where

3 Answers3