Re: JDBCR4 question -- Checking the syntax of a query without crashing the JVM

James,

As Buck said, this is called an SQL injection attack, and you should be writing your code in a way that prevents it. Users should only be able to provide variable values, not _part_of_ your SQL statement!

Instead of JDBC_ExecQry, you should be using JDBC_PrepStmt like this:

myStmt = JDBC_PrepStmt( conn: 'select x,y,z from mytable +
where somefield=?');

The ? is called a "parameter marker" and takes the place of a variable value. The user can then supply the value that goes there, but they cannot do things like add an additional clause on to your SQL statement, they can only provide a variable that's used by the WHERE.

JDBC_setString( myStmt: 1: TheUserData );

The above sets the value for the first (reading from left to right) parameter marker in the statement. If there's more than one ? in the statement to set, you can change the 1 to a 2, 3, or 4, etc, to match the parameter marker that you are setting.

Then you do:

rs1 = JDBC_ExecPrepQry( conn: myStmt );

to run it. It can be run (potentially) multiple times before you're done with it, each time with different parameter values. When you're done, do JDBC_FreeStmt(myStmt) to free up the statement handle that you originally created.

This will prevent SQL injection attacks, which are a huge security problem. You should only use JDBC_ExecQry() when the statement is entirely generated from data in your program, so there's no danger of the user interjecting something that should not be there.

-SK

On 2/25/2014 1:18 PM, James H. H. Lampert wrote:

My Fellow Midrange Geeks and Geekettes:

One of the features I've put into my project is the ability to
incorporate a user-specified WHERE clause into the generated WHERE
clauses of my queries, in order to filter the records accessed.

But if the user uses this feature to insert garbage into the WHERE
clause (e.g., "WHERE FOO = 'BAR'," with no FOO field in the file), it
results in a series of bad JDBC calls that crashes JDBC and the JVM,
requiring the user to sign off and sign back on.

Space offset X'00000000' or X'00008000300149F0' is outside current limit
for object <jobname> <user> <jobnum>.

I'd like to be able to catch the bad query before this happens. Is there
a way to do this?

It seems, running it in the debugger, that when the query gets executed

rs1 = jdbc_ExecQryCS( conn : %UCS2('Select * '
+ ' from ' + %TRIM(QUALTBL) + ' WHERE ((')
+ %TRIM(WHERECLAUSE1) + %UCS2(') ') + %TRIM(ORWHERE1)
+ %UCS2(') AND (') + %UCS2(%TRIM(ADDLWHERE))
+ %UCS2(') ORDER BY ' + ORDERBY));

(jdbc_ExecQryCS being a UCS2-parameter [so we can process Unicode
keyfields], scrollable result set version of jdbc_ExecQry), RS1 comes
back as null (0), and an RNX0301,

Java exception received when calling Java method.

appears in the joblog, with

Cause . . . . . : RPG procedure JDBC_EXECQ in program AQUESTVIEW/JDBCR4QV
received Java exception "java.sql.SQLException: [SQL0206] Column FOO not in
specified tables." when calling method "executeQuery" with signature
"(Ljava.lang.String;)Ljava.sql.ResultSet;" in class "java.sql.Statement".

Now assuming I recognize, and respond to, the null result set I get, am
I going to have to insert an ExceptionClear somewhere, in order to keep
the next call from crashing the JVM? For reference, here is the
aforementioned "jdbc_ExecQryCS":

P JDBC_ExecQryCS B export
D JDBC_ExecQryCS PI like(ResultSet)
D conn like(Connection) const
D sql 32767C varying const options(*varsize)
D stmt s like(Statement)
D temp s like(ResultSet)
D rs s like(ResultSet)
/free
jdbc_begin_object_group(50);

monitor;
stmt = createStatement2( conn: 1005: 1007); // 1007 read-only
temp = executeQuery( stmt : new_StringC(sql));
jdbc_end_object_group(temp: rs);
on-error;
jdbc_end_object_group();
return *NULL;
endmon;

return rs;
/end-free
P E

(Not too different from Scott's original jdbc_ExecQry.)

--
JHHL