Oracle SQL prepareStatement sending NULL as parameter within setXXX()?

Asked Dec 10 '19 at 19:37

Active Dec 10 '19 at 19:39

Viewed 74 times

Hey all I have the following SQL query:

Class.forName("oracle.jdbc.driver.OracleDriver");
PreparedStatement stmt = con.prepareStatement("SELECT " +
                                                "DECODE(MAX(ID), NULL, 'NO', 'YES') BOOL_VAL " +
                                              "FROM " +
                                                "MASTERNICSDB " + 
                                              "WHERE " + 
                                                "FTAG = ? " +
                                              "AND " + 
                                                "BADGENUM IS ?");
stmt.setString(1, System.getenv("COMPUTERNAME").toUpperCase());
stmt.setNull(2, Types.VARCHAR);
ResultSet rs = stmt.executeQuery();
rs.next();

The issue with the above is that it keeps throwing:

ORA-00908: missing NULL keyword

I have tried all of the following:

stmt.setNull(2, Types.VARCHAR);
stmt.setString(2, "NULL");
stmt.setNull(2, oracle.jdbc.OracleTypes.VARCHAR);
stmt.setObject(2, null);

None of which seem to work. They all get the same error as above.

However, if I place the values inside the query all is fine - but I don't want to do that due to SQL injection and all that fun stuff.

edited Dec 10 '19 at 19:39

asked Dec 10 '19 at 19:37

StealthRT

10,108
40
183
342

you can't pass that as a parameter, `IS NULL` is a single operator. – Dec 10 '19 at 19:41
@a_horse_with_no_name then what do you suggest? – StealthRT Dec 10 '19 at 19:53
Use `BADGENUM IS NULL` inside your SQL string – Dec 10 '19 at 20:01
@a_horse_with_no_name wouldn't that be prone to an SQL injection? – StealthRT Dec 10 '19 at 20:13
1

How can that be prone to injection if the expression is hardcoded. – Dec 10 '19 at 20:14
https://stackoverflow.com/a/32642737/277480 – StealthRT Dec 10 '19 at 20:17
Your code does not concatenate any user input if it contains the constant string `AND BADGENUM IS NULL` – Dec 10 '19 at 20:25
Make it the answer so that i can give you credit. – StealthRT Dec 10 '19 at 20:28

Oracle SQL prepareStatement sending NULL as parameter within setXXX()?

0 Answers0