Testing for null inside SQL query

Question

I want to implement search filter for this table:

CREATE TABLE ACCOUNT(
 ID INTEGER NOT NULL,
 USER_NAME TEXT,
 PASSWD TEXT,
 FIRST_NAME TEXT,
 LAST_NAME TEXT,
 LAST_LOGIN DATE,
 DATE_REGISTERED DATE,
 ROLE INTEGER,
 CAN_LOGIN INTEGER
)
;

-- ADD KEYS FOR TABLE ACCOUNT

ALTER TABLE ACCOUNT ADD CONSTRAINT KEY1 PRIMARY KEY (ID)
;


SELECT * FROM ACCOUNT 
WHERE '" + searchString + "' IN (ID, USER_NAME, FIRST_NAME, LAST_NAME)
ORDER BY %S %S offset ? limit ?;

But when I have empty search filter I get this error:

org.postgresql.util.PSQLException: ERROR: invalid input syntax for integer: "null" Position: 30

How can I edit the SQL query in a way that WHERE clause will be skipped if searchString is empty?

Here is the Java method:

public List<AccountsObj> list(int firstRow, int rowCount, String sortField, boolean sortAscending) throws SQLException
    {
        String SqlStatement = null;

        if (ds == null)
        {
            throw new SQLException();
        }

        Connection conn = ds.getConnection();
        if (conn == null)
        {
            throw new SQLException();
        }

        String sortDirection = sortAscending ? "ASC" : "DESC";

        SqlStatement = "SELECT * FROM ACCOUNT "
            + " WHERE '" + searchString + "' IN (ID, USER_NAME, FIRST_NAME, LAST_NAME)"
            + " ORDER BY %S %S offset ? limit ? ";

        String sql = String.format(SqlStatement, sortField, sortDirection);

        PreparedStatement ps = null;
        ResultSet resultSet = null;
        List<AccountsObj> resultList = new ArrayList<>();

        try
        {
            conn.setAutoCommit(false);
            boolean committed = false;

            ps = conn.prepareStatement(sql);
            ps.setInt(1, firstRow);
            ps.setInt(2, rowCount);

            resultSet = ps.executeQuery();
            resultList = ProcessorArrayList(resultSet);

            conn.commit();
            committed = true;

        }
        finally
        {
            ps.close();
            conn.close();
        }

        return resultList;
    }

Whatever you do just stop for a moment. Your query looks very odd. In your application layer you probably have: 1.string concatenation 2. parameter binding 3. string substution. What if user provide searchString that will comment rest of query and DROP DATABASE? — Lukasz Szozda
– Lukasz Szozda, Commented Apr 9, 2016 at 18:28
Anyway it is probably duplicate of: stackoverflow.com/questions/36519762/search-in-postgresql-table — Lukasz Szozda
– Lukasz Szozda, Commented Apr 9, 2016 at 18:29
So you don't see any problem with mixing: sql query string concatenation/parameter binding and string substitution? — Lukasz Szozda
– Lukasz Szozda, Commented Apr 9, 2016 at 18:30
What if I set the search string as parameter of prepared statement? — Peter Penzov
– Peter Penzov, Commented Apr 9, 2016 at 18:30

nyname00 · Accepted Answer · 2016-04-09 22:39:52Z

1

Using SQL to check for a null search string you can do:

SELECT * FROM account WHERE ? IS NULL OR ? IN (user_name, first_name, last_name)

Here the ? IS NULL will short-circuit if the parameter is NULL and the second part will not be evaluated.

Note that, I've used two parameter bindings with the same value (your search string) here and that the ID column is gone - you cannot mix varchar and integer in the IN clause.

Edit For wildcard searches you can use LIKE or ILIKE (for case-insensitive searches)

SELECT * FROM account WHERE 
     (trim(?) = '') IS NOT FALSE 
    OR user_name like ? 
    OR first_name like ? 
    OR last_name like ?

Using a prepared statement you would call it like this (note that you have to bind the same parameter four times)

try (PreparedStatement ps = conn.prepareStatement(sql)) {

    ps.setString(1, searchString);
    ps.setString(2, searchString);
    ps.setString(3, searchString);
    ps.setString(4, searchString );

    try (ResultSet rs = ps.executeQuery()) {
        // read data 
    }
}

edited Apr 9, 2016 at 22:39

answered Apr 9, 2016 at 19:24

nyname00

2,5762 gold badges25 silver badges25 bronze badges

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

8 Comments

Peter Penzov Over a year ago

Thanks, How I Can add wildcard character? For example I would like to search part of a string `%valu%'

nyname00 Over a year ago

@Peter Penzov The straight forward way to go would be to use like or ilike (case-insensitive), e.g. WHERE ? IS NULL OR user_name like ? OR first_name like ? OR last_name like ?

nyname00 Over a year ago

Also, you could go for fulltext search, see here postgresql.org/docs/current/interactive/…

Peter Penzov Over a year ago

Can you give me please example how I can use the example from the link with Java code and prepared statement? It's not very clear for me how I can use to_tsvector('english', body) @@ to_tsquery('english', 'friend'); in my case.

nyname00 Over a year ago

Something like this user_name::tsvector @@ to_tsquery(?) where the search string would be like valu:*. But the wildcard works only for prefix searches (:*value:* will not work) - so this may not be the best solution for you, but it's always good to know all your options

|

Vic Seedoubleyew · Accepted Answer · 2016-04-09 21:16:15Z

0

You can replace this line of your java code :

+ (searchString == null || searchString.length == 0 ) ? "" : (" WHERE '" + searchString + "' IN (ID, USER_NAME, FIRST_NAME, LAST_NAME)")

It basically checks if searchString is empty, and adds the line only if it is not

edited Apr 9, 2016 at 21:16

answered Apr 9, 2016 at 18:44

Vic Seedoubleyew

10.7k6 gold badges61 silver badges81 bronze badges

4 Comments

Peter Penzov Over a year ago

I get incompatible types: String cannot be converted to boolean

Vic Seedoubleyew Over a year ago

you only need to change that one line

Peter Penzov Over a year ago

I get bad operand type String for unary operator '+++'

Peter Penzov Over a year ago

I get incompatible types: String cannot be converted to boolean

Collectives™ on Stack Overflow

Testing for null inside SQL query

2 Answers 2

8 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related