Is SQL Injection possible if we're using only the IN keyword (no equals = operator) and we handle the single quote

Naive escaping or filtering that doesn't take the character encoding of the database connection into account is dangerous, because your code and the database system may not agree on what a single quote actually looks like as a byte sequences. Under specific circumstances, an attacker can exploit this to bypass the escaping or filtering altogether. This is why, for example, MySQL has replaced the library function mysql_escape_string (which doesn't have a parameter to specify the encoding) with mysql_real_escape_string (which does).

In general, don't try to invent your own filters. Use well-tested standard methods provided by the database library. First, double-check if you really cannot use prepared statements. I find that hard to believe. If you're sure that there's no way to fix this major limitation, then use the escape functions of the database library, not your own.

There is absolutely no reason why you can't use parameterised statements and let your DB framework handle the rest. This is going to be a lot safer than trying to escape things with whatever methods you could come up with.

Remember that PostgreSQL knows about arrays, and allows you to match to any value in an array:

SELECT * FROM thing WHERE stuff = ANY(:list) 

You then pass your list of values as an array, and this will be equivalent to IN.

YES.

If you create an SQL query using a user string without using appropriate PPE (IE prepared statements). Your code is subject to SQL injection attacks.

This question starts with a false assumption:

"Prepare Statements are not an option for us.". Not only are they always an option (unless this is a homework assignment). They are the best/easiest/most safe way to do things.

Java with PostgreSQL supports prepared statements. You can either dynamically generate your prepared statement (not using untrustworthy user strings.) And then prepare it and bind the evil user strings.

Or you can use an array and use the postgreSQL driver to convert a java array into a postgreSQL array.

Not using an array

_{This works, but not my favored way}

You can generated a prepared statement with N number of tags. Following is some Pseudo Java.

String my_sql_string = "select something, other_thing, last_thing from my_table where X in (?"; // This is fine, string is generated by me, not using untrustworthy user input.... for (int n = 1; n < my_filters.length(); n++) { my_sql_string += ", ?"; } my_sql_string += ")"; PreparedStatement my_statement = dbConnection.preapareStatement(my_sql_string ); for (int n = 0; n < my_filter_options.length(); n++) { my_statement .setString(n+1, my_filters[n]); // This is 1 indexed due to reasons... :( } response = preparedStatemebnt.query(). //Do whatever you want with the response... 

With an array

PostgreSQL supports arrays.

String my_sql_string = "select something, other_thing, last_thing from my_table where x = ANY(?)"; PreparedStatement my_statement = dbConnection.preapareStatement(my_sql_string ); Array my_filters = connection.createArray("varchar", my_filters); my_statement.setArray(1, my_filters); // DO your thing 

Disclaimer

This is untested pseudo code, will probably not work as is, and I might have some method names wrong. But it will work with a small amount of tweaking.

We're thinking of restricting single quotes for user inputs. Is it possible to break the query in our scenario, if we restrict single quote?

Were thinking of messing with people's surnames.

double them instead.

If your database sessions has standard_conforming_strings turned off you'll also need to double backslashes.

If users get to specify column,table, database, or schema names you'll also need to deal with the identifier quoting rules.

You're connecting to arbitrary databases, you had better not be doing that as a database superuser else someone might slip in an ON SELECT TO tablename DO INSTEAD rule that invokes

create temp table foo(); copy foo from program 'rm -rf ../..'; -- this will nuke your database cluster 

or something else similarly dangerous.

You need to escape any user inputs. You should use a proper library to escape any user input and not try any homebrew methods. I'm not sure if jdbc has some base function to do it but perhaps there is a string lib available that will.

Prepared statements are useful for reusing a query with different params such as in a loop, using them for one off situation may not be most efficient depending on use case. https://www.postgresql.org/docs/current/sql-prepare.html

Specific database wasn't mentioned, however, as I work primarily in Oracle, I thought as a potential alternative option, this alternative approach might be worth considering:

Pros:

• uses bind variables - so no sql injection.

Cons:

• Sql statement is slightly more complex
• no promises on performance as it scales, or gets more complex (that said, I've used similar trick and haven't yet hit any real issues .. but of course, YMMV)

Simple example to demonstrate in sqlplus. (note the begin block is just setting up the bind variable. Just because that's text, that isn't intended as your input .. it's just a substitute for a proper bind variable. The bind variable you send into this is a single String (comma delimited) with values ... and if extra "junk" or attempts to break it are added - they are simply ignored - unless the text itself actually matches the lookup ;) )

create table my_table ( id number primary key, col_val varchar2(10) ); insert into my_table values ( 123, 'Alice' ); insert into my_table values ( 143, 'Bob' ); insert into my_table values ( 223, 'Chuck' ); insert into my_table values ( 523, 'Daniel' ); insert into my_table values ( 753, 'Eunice' ); insert into my_table values ( 993, 'Fran' ); commit; select * from my_table; var b varchar2(1000) begin :b := 'Daniel, Alice, Drop Table, other junk, Fran'; end; / select :b from dual; :B ------------------------------------------------------------------------------------------------------------------------ Daniel, Alice, Drop Table, other junk, Fran with temp as ( select :b s from dual ), w_list as ( select level , trim(regexp_substr(t.s, '[^,]+', 1, level)) fs from temp t connect by level <= length ( regexp_replace(t.s, '[^,]+')) + 1 ) select * from my_table mt where mt.col_val in ( select fs from w_list ) / ID COL_VAL ---------- ---------- 523 Daniel 123 Alice 993 Fran 3 rows selected. 

Basically, the with clauses are taking the incoming comma delimited string, and parsing it out into individual rows. so "w_list" ends up looking/acting just like a table. So now you can join to it with normal sql statements. (in this case an IN clause to potentially avoid any duplicates)

The real point here is, there should never be a good reason NOT to use bind variables .. and there is likely ALWAYS a way to use them to avoid sql injection. It might require a redesign of your app or such, or just some creative thinking on how you pass in the values ... but there will be a way.