Re: How to save a list of programs

I don't think it is good to just decry dynamic SQL in general. In many environments SQL is always used in a dynamic manner. There are ways to avoid SQL injection built in to the language so that is no reason to avoid dynamic SQL. And, there are things you can do in dynamic SQL that work better than static SQL such as adapting a query to a set of user keyed search/sort criteria.

However, the dynamic SQL as stated below is indeed a problem as you have a long, and as far as we know, unvalidated string being inserted into a statement. This is really using SQL injection to accomplish a positive end. The expected string is a chunk of SQL which defines a list of program names. But what if the string were something like this " ' ' ) and 1 = 1 --", which is also a valid chunk of SQL. Now all programs are likely selected, and the outcome could be bad for you. It would be better to say that dynamic SQL without parameter markers is frowned upon, though in an environment with strong typing like RPG, numeric variables can be converted to strings and concatenated directly into the SQL. In other environments like PHP/ODBC/JDBC where everything is dynamic you have to be more careful and parameterize everything that could possibly be provided by a user.

Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx


-----Charles Wilt <charles.wilt@xxxxxxxxx> wrote: -----
To: "RPG programming on the IBM i (AS/400 and iSeries)" <rpg400-l@xxxxxxxxxxxx>
From: Charles Wilt <charles.wilt@xxxxxxxxx>
Date: 11/10/2016 08:55AM
Subject: Re: How to save a list of programs


You can't do it with static SQL...

You'd have to pass each value separately.
WHERE IN (:v1, :v2, :v3)

Otherwise dynamic SQL is an option
wSqlStmt += 'WHERE fld in (' + myProgramList + ')';

exec sql
prepare S1 from :wSqlStmt;

exec sql
execute S1;

Note that the use of dynamic SQL is frowned upon now-a-days; as dynamic SQL
is open to SQL injection attacks.

Assuming this is a IT tool, not open to your users or the public or that
the program is building the statement without directly concatenating user
input into the statement. You should be able to make the case for using it.

Charles


On Thu, Nov 10, 2016 at 6:38 AM, Robert Rogerson <rrogerson@xxxxxxxxxxx>
wrote:

Hi all,

I have a list of programs which is generated by a 3rd party software
application. This is in the form of ('program1', 'program2', 'program3').
The list is actually used in a WHERE IN ('program1', 'program2',
'program3') sql statement used by the vendor software.

Basically I want to use that same WHERE IN ('program1', 'program2',
'program3') statement within a custom RPG program. I'm planning to PREPARE
an sql CURSOR and add the WHERE IN (:myprogramList).

But I can't figure out how to save the list as one big string. Does anyone
know how I can save ('program1', 'program2', 'program3') as one big string?

The problem I'm experiencing (I think) is that the quotes are interpreted
as string delimiters. I was trying with a data area and a one column table
but both had the same issue. Any ideas?

Thanks,

Rob

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.