On 25-Oct-2015 21:17 -0500, Justin Dearing wrote:
<<SNIP>>
I meant registering ADDPFM as a stored procedure, but it seems the
one exception to "everything can be a stored proc on the IBM i" is CL
commands. I'm a little concerned about the potential for SQL
injection for a proc that passes a string to QCMDEXEC, but yes I
need to read up, and initially I'm writing a stored procedure only I
will call on a system where I have QSECOFR.
Note: the procedure and program names are QCMDEXC [not QCMDEXEC];
stuff likely will not work so well, with the extraneous 'E' :-)
Re SQL injection: Well, the point of a statement interpreter, is
specifically to allow a user-composed request to be interpreted; kinda
purposely allowing for dynamic /injection/ :-) Hence the feature should
not be exposed to a general-purpose user-input, unless safeguarded, just
like would be the case for exposing an SQL statement interpreter.
Seems that the quote "everything can be a stored proc on the IBM i",
according to a web search of the archives, appears only in that [and
presumably now also this] message. I suppose that was hyperbole, but,
if any docs suggest essentially that, then perhaps they might be
clarified by suggesting effectively that:
Like the SQL, the CL is a language. A CL Command request, is just a
/statement/ of that language. So just like a SQL statement alone can
not "be a stored procedure", nor can a CL statement alone "be a stored
procedure"; either must first be /compiled/ into an executable
program\procedure, or be /interpreted/ by an executable program\procedure.
A CLP program or procedure serving as an external stored procedure
can accept values, that passed as arguments can become the values used
for variables or expressions specified on the Add Physical File Member
(ADDPFM) command. For example, the following CLP source shows
expressions used followed by the CREATE PROCEDURE. The CREATE is
invoked with a *lie* to the SQL about the interface, due to overcoming
language restrictions with VARCHAR; the varying are preferable and thus
chosen, for better effect, with ad hoc calls using literals, but makes
the CL uglier:
CRTCLPGM THE_LIB/ADDPFM from the following source:
pgm parm(&file &libr &mbr &txt &srct)
dcl &file *char 12
dcl &libr *char 12
dcl &mbr *char 12
dcl &txt *char 52
dcl &srct *char 12
dcl &fln *int 2
dcl &lln *int 2
dcl &mln *int 2
dcl &tln *int 2
dcl &sln *int 2
chgvar &lln %bin(&libr 1 2)
chgvar &fln %bin(&file 1 2)
chgvar &mln %bin(&mbr 1 2)
chgvar &tln %bin(&txt 1 2)
chgvar &sln %bin(&srct 1 2)
*nlvlibl/addpfm +
file( %sst(&libr 3 &lln)/%sst(&file 3 &fln) ) +
mbr( %sst(&mbr 3 &mln) ) +
text( %sst(&txt 3 &tln) ) +
srctype( %sst(&srct 3 &sln) ) +
share(*no) expdate(*none)
endpgm
Issue the following SQL CREATE to the procedure ADDPFM in THE_LIB to
invoke the same-named program created in the prior step:
create procedure THE_LIB/addpfm
( in varchar(10), in varchar(10)
, in varchar(10), in varchar(50)
, in varchar(10)
) external name 'THE_LIB/ADDPFM'
language pli parameter style general
Effecting ADDPFM QTEMP/SRCF AMBR TEXT('Tst') SRCTYPE('TXT') is done
with the following SQL CALL:
call addpfm ('SRCF', 'QTEMP', 'AMBR', 'Tst', 'TXT')
midrange.com
