Creating a Simple SQL Query by concating condition based on Three Filters - Java & MySQL

Question

I have three HTML Select tags. The contents are as below:

All, Users, Guests
All, Approved, Rejected
All, Public, Private

I am doing following:

String by = //Some Code to get this value
String status = //Some Code to get this value
String privacy = //Some Code to get this value

String searchQuery = "SELECT * FROM requests";

if(!by.equalsIgnoreCase("all")){
    searchQuery += " WHERE SEARCH_BY='" + by + "'";;
}

if(!status.equalsIgnoreCase("all")){
    searchQuery += " WHERE SEARCH_STATUS='" + status + "'";;
}

if(!privacy.equalsIgnoreCase("all")){
    searchQuery += " WHERE SEARCH_PRIVACY='" + privacy + "'";;
}

This will be a working query only if the user changes only one tag. But what if he changes two select tags: searchQuery will look like this:

SELECT * FROM requests WHERE SEARCH_BY='Users' WHERE SEARCH_STATUS='Rejected'

I know i can use AND keyword like : WHERE thisCondition AND thatCondition

But What is the possibility of knowing that thisCondition exists(only if this select tag is not changed), it will make my query like this: WHERE AND thatCondition

In the End, I need something like:

Select * FROM requests WHERE SEARCH_BY='A' AND SEARCH_STATUS='B' AND SEARCH_PRIVACY='C';

Where A, B & C can be either All or a value. When it is All, I want it to contain all the results from database.

Something like: WHERE SEARCH_BY='all' should return all the rows of database rather than matching values of SEARCH_BY column to all keyword.

Mr Lister · Accepted Answer · 2016-02-28 08:58:21Z

2

Try this:

String searchQuery = "SELECT * FROM requests where 1=1";

if(!by.equalsIgnoreCase("all")){
    searchQuery += " AND SEARCH_BY='" + by + "'";;
}

if(!status.equalsIgnoreCase("all")){
    searchQuery += " AND SEARCH_STATUS='" + status + "'";;
}

if(!privacy.equalsIgnoreCase("all")){
    searchQuery += " AND SEARCH_PRIVACY='" + privacy + "'";;
}

This uses a dummy condition so no matter how many other condition are meet, they will just be added as AND <CONDITON>.

edited Feb 28, 2016 at 8:58

Mr Lister

46.8k15 gold badges118 silver badges156 bronze badges

answered Feb 21, 2016 at 12:57

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rupinderjeet Over a year ago

Just want to confirm that it matches One Integer with another integer or I need to create a column named '1' and value '1'

sagi Over a year ago

Yes it matches an integer with another intenger, equals to true = true or 1>0 ETC.. no need to add anything. @rupinderjeet47

Mita · Accepted Answer · 2016-02-21 18:15:34Z

1

First of all ensure that "by", "status" and "privacy" values are equals the select options. Written like that your code could be a victim of an SQL injection. There is many ways to do than. One and simple is:

public enum EBy {
    All, Users, Guests
}
public enum EStatus {
    All, Approved, Rejected
}
public enum EPrivacy {
    All, Public, Private
}
EBy eBy = EBy.valueOf(by);
EStatus eStatus = EStatus.valueOf(status);
EPrivacy ePrivacy = EPrivacy.valueOf(privacy);

Avoid using " WHERE 1=1" in an sql query. SELECT * FROM t is not equals SELECT * FROM t WHERE 1=1 You could see that running "explain" on both queries.

String where = "";
if (eBy != null && eBy != EBy.All) {
    where += ((where == "") ? " WHERE " : " AND ") + " SEARCH_BY = '" + eBy.name() + "'";
}
if (eStatus != null && eStatus != EStatus.All) {
    where += ((where == "") ? " WHERE " : " AND ") + " SEARCH_STATUS = '" + eStatus.name() + "'";
}
if (ePrivacy != null && ePrivacy != EPrivacy.All) {
    where += ((where == "") ? " WHERE " : " AND ") + " SEARCH_PRIVACY = '" + ePrivacy.name() + "'";
}
searchQuery += where;

Are you using JDBC? Try to apply values on the prepared query instead of concatenating them in the query string. There are lot of materials over the internet for that, for example: Preventing SQL Injection in Java

answered Feb 21, 2016 at 18:15

Mita

4223 silver badges5 bronze badges

5 Comments

rupinderjeet Over a year ago

What is the difference between eBy and eBy.name()? and This query is used for a ResultSet. and I don't think a user can edit properties/values of select tag on my website. I insert that value from tag in query directly. There will be injection if they somehow managed to alter select variable only. right?

rupinderjeet Over a year ago

RE: What is the difference between eBy and eBy.name() because you use the enum object to match with Eby.All, Then, why can't you output it in Query, why you needed to use eby.name()?

Mita Over a year ago

1. The user CAN edit the value that HTML sends to your server. Read more about SQL injections.

Mita Over a year ago

2. "some string" + eBy + "some string" is equals to "some string" + eBy.toString() + "some string" That's why I preffer the eBy.name(), because I am not sure if there is no toString() method predefined for that enum. In a big application it is not a good idea to rely to toString() method, because someone could chage it, whithout even warning you :)

rupinderjeet Over a year ago

why a query with 'Where 1=1' is not equal to another query without it?

Collectives™ on Stack Overflow

Creating a Simple SQL Query by concating condition based on Three Filters - Java & MySQL

2 Answers 2

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related