× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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')



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.