× 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.


  1.  Home
  2. RPG400-L
  3. August 2011

Re: Reduce large amount of logicals in SUBFL pgm, take in another direction

The dangers goes well beyond SQL injection, particularly if you are delivering content to a web page. I am planning on submitting a presentation proposal for the Spring conference on "IBM i on and the Web: Not as secure as you think". I just finished up my GIAC Secure Software Programmer certification for Java (giac.org) and I could see plenty of places where insecure programming could come back to haunt you, even on IBM i and using RPG.

For SQL injection, using bind parameters and dynamic SQL is best practice. For other web based exploits, the best overall solution would be to wrap the OWASP Enterprise Security API's (www.owasp.org) so that they are accessible from RPG. This is new stuff for many programmers. My eyes were opened wide when I saw what a hacker can do with an insecure web app, fortunately, we still live in a (mostly) 5250 world...

Pete Helgren
Value Added Software, Inc
www.asaap.com
www.opensource4i.com


On 8/2/2011 10:12 AM, Kurt Anderson wrote:
I never knew about SQL injection until this thread (am I living under a rock?), so this has been quite informative.

I did have a comment/suggestion: Could the programmer check the user-input field for a semi-colon, and if that value was present to treat it as invalid and not perform the SQL (controlled abend)?

-Kurt

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Monnier, Gary
Sent: Monday, August 01, 2011 4:44 PM
To: RPG programming on the IBM i / System i
Subject: RE: Reduce large amount of logicals in SUBFL pgm, take in another direction

I tried your example and get the following.

Message ID . . . . . . : SQL0104 Severity . . . . . . . : 30

Message type . . . . . : Information

Date sent . . . . . . : 08/01/11 Time sent . . . . . . :
14:41:37


Message . . . . : Token ; was not valid. Valid tokens: ( + - ? : DAY
NOT RRN
CASE CAST CHAR DATE DAYS HASH HOUR LEFT.

Cause . . . . . : A syntax error was detected at token ;. Token ; is
not a
valid token. A partial list of valid tokens is ( + - ? : DAY NOT RRN CASE
CAST CHAR DATE DAYS HASH HOUR LEFT. This list assumes that the statement is
correct up to the token. The error may be earlier in the statement, but the
syntax of the statement appears to be valid up to this point.

Recovery . . . : Do one or more of the following and try the request
again:
-- Verify the SQL statement in the area of the token ;. Correct the

statement. The error could be a missing comma or quotation mark, it could
be a misspelled word, or it could be related to the order of clauses.

-- If the error token is<END-OF-STATEMENT>, correct the SQL
statement
because it does not end with a valid clause.

Gary


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Tommy.Holden@xxxxxxxxxxxxxxxxxxxxx
Sent: Monday, August 01, 2011 1:08 PM
To: RPG programming on the IBM i / System i
Subject: RE: Reduce large amount of logicals in SUBFL pgm,take in another direction

UserEnteredValue = ';drop table mytable cascade'





From: "Monnier, Gary"<Gary.Monnier@xxxxxxxxx>
To: "RPG programming on the IBM i / System i"
<rpg400-l@xxxxxxxxxxxx>
Date: 08/01/2011 02:58 PM
Subject: RE: Reduce large amount of logicals in SUBFL pgm, take
in
another direction
Sent by: rpg400-l-bounces@xxxxxxxxxxxx



So tell me, how did userEnteredValue not get verified/edited?

Gary



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Charles Wilt
Sent: Monday, August 01, 2011 12:37 PM
To: RPG programming on the IBM i / System i
Subject: Re: Reduce large amount of logicals in SUBFL pgm,take in
another direction

Fine...

So tell me how this could be safe?
wSqlStmt = 'SELECT * FROM MYTABLE WHERE MYFLD = ' + userEnteredValue;

Charles

On Mon, Aug 1, 2011 at 3:27 PM, Monnier, Gary<Gary.Monnier@xxxxxxxxx>
wrote:
Charles,

With respect, I do understand the issue. I simply disagree with your
assessment.
Gary



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Monday, August 01, 2011 11:48 AM
To: RPG programming on the IBM i / System i
Subject: Re: Reduce large amount of logicals in SUBFL pgm,take in
another direction

Gary,

I wouldn't call this a matter of disagreement ...

With respect, you obviously don't understand the issue....but it's an
important issue. I'd be happy to clarify the problem, but I don't know
any better way to explain it. Perhaps Scott, Joe or Jon can jump in
with a better explanation.
Or perhaps you can explain why you think your code is safe because
it's
in a module...
You might start by posting the actual code, along with sample usage...

Perhaps then somebody will be able to give you a concrete example of
how
it could be misued.
Sincerely,
Charles

On Mon, Aug 1, 2011 at 2:25 PM, Monnier, Gary<Gary.Monnier@xxxxxxxxx>
wrote:
Charles,

So we choose to disagree.

Gary


-----Original Message-----
From: rpg400-l-bounces+gary.monnier=terex.com@xxxxxxxxxxxx
[mailto:rpg400-l-bounces+gary.monnier=terex.com@xxxxxxxxxxxx] On
Behalf Of Charles Wilt
Sent: Monday, August 01, 2011 11:19 AM
To: RPG programming on the IBM i / System i
Subject: Re: Reduce large amount of logicals in SUBFL pgm,take in
another direction

Gary,

You seem to be hung up on *MODULE...

I repeat, it doesn't matter where the statement is built. If you're
building a statement dynamically with concatenation of user supplied
string. The code is vulnerable. It doesn't have anything to do with
*PGM, *MODULE, *SRVPGM, authority or debug mode.
Charles

On Mon, Aug 1, 2011 at 1:54 PM, Monnier, Gary
<Gary.Monnier@xxxxxxxxx>
wrote:
Charles,

With respect you seem to be hung up on my example being a *PGM. I
recommended *MODULE. If you feel someone can get to a *PGM build from
*MODULEs and can inject an SQL statement then again I assert security
settings to the program are set incorrectly. Either you allow *PUBLIC
to debug the program or you have had a security breach involving a
profile with enough authority to debug the program. If the executable
*PGM allows modules and or service programs to be changed then you also
have a security hole. In both cases it is not my example causing the
breach and other much larger security issues are in play.
Respectfully,

Gary Monnier


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Monday, August 01, 2011 6:06 AM
To: RPG programming on the IBM i / System i
Subject: Re: Reduce large amount of logicals in SUBFL pgm,take in
another direction

Gary,

If you think that your program doesn't open you up to SQL injection,
you are sadly mistaken...
The fact that the procedure is bound statically by reference or by
copy as opposed to a dynamic called *PGM has no bearing.
SQL injection attacks can occur anytime you dynamically build a
statement and concatenate user string input into the statement. It
doesn't matter where the statement is built.
Again, all that matters is that in some way shape or form, you are
building a statement like so:
wSqlStmt = 'SELECT * FROM MYTABLE WHERE MYFLD = ' +
userEnteredValue;

Theoretically, you could parse and sanitize the user input, making
sure it's safe before using it. However, unlike some languages RPG
doesn't have that functionality built in. Also, IMHO you're
dependent on the guy who wrote the sanitizer being smarter than the
hackers :)

The safe way to do dynamic statements is via parameter markers. You
build a statement like so:
wSqlStmt = 'SELECT * FROM MYTABLE WHERE MYFLD = ?';

then when you open/execute it, you pass in the variables to use at
the
parameter markers.
open C1 using :userEnteredValue;

With respect to PCI requirements...
6.5 Develop applications based on secure coding guidelines and
review
custom application code to identify coding vulnerabilities. Follow
up-to-date industry best practices to identify and manage
vulnerabilities.
OWASP is one of the standards usually used for secure coding...their
top security risk for 2010...injection (SQL queries, LDAP queries, XPath
queries, OS commands, program arguments, etc. ).
https://www.owasp.org/index.php/Category:OWASP_Top_Ten_Project

Thus, dynamic SQL as used by your programs would fail an secure code
review and you'd likely fail a PCI audit.
HTH,
Charles






On Fri, Jul 29, 2011 at 2:26 PM, Monnier, Gary
<Gary.Monnier@xxxxxxxxx> wrote:
Your opinion Charles and what seems a very strong opinion. J



Yes, I've heard of SQL Injection attacks and no my suggestion does
not leave you open to this sort of attack. I've also heard of LDAP
injection attacks.



What I suggested does not impact PCI compliance or any other
security parameters. Nor does it impact FDA compliance. All the
MODULE does is build an SQL string. When bound with other modules
to make an executable object there are no compliance issues.



If you create ILE programs that allow updating modules or service
programs then you have a behavior that needs changing. My opinion
is, and it is only my opinion, you should NEVER create a program
with Allow update. You are just asking for trouble in the ILE
world.


If you have ulcer causing issues with pgmToBuildSQL make it a copy
module rather than a bound module.



Regards,



Gary





-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Friday, July 29, 2011 10:35 AM
To: RPG programming on the IBM i / System i
Subject: Re: Reduce large amount of logicals in SUBFL pgm,take in
another direction



Very, very BAD IDEA!



Horrible in fact.



I hope you're not subject to PCI compliance rules, as the code you
posted pretty much guarantees an automatic failure.



Try googleing for "SQL injection"...



Charles



On Fri, Jul 29, 2011 at 1:16 PM, Monnier, Gary
<Gary.Monnier@xxxxxxxxx<mailto:Gary.Monnier@xxxxxxxxx> > wrote:

Sharon,
The process that builds your SQL statement can have parameters
passed
to it. One of these parameters can be a list of selections.
Something like this.
pgmToBuildSQL PR
nbrFieldsForWhereClause
arrayOfFieldsForWhereClause
arrayOfValuesForWhereClause
SQLStringBegin = 'Select * From yourfile ';
X = 1;
startPos = 1;
doW (X< = nbrFieldsForWhereClause);
whereValue = arrayOfFieldsForWhereClause(X) +
arrayOfValuesForWhereClause(X); //ValueFor can be = value, like
value, etc
valueLen = %len(%trimr(whereValue));
%subst(whereClause:startPos:valueLen) = whereValue;
startpos = startPos + valueLen + 1;
X = X + 1;
enddo;
Return;
--

This is the RPG programming on the IBM i / System i (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx
<mailto:RPG400-L@xxxxxxxxxxxx> To subscribe, unsubscribe, or
change list options,

visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
<http://lists.midrange.com/mailman/listinfo/rpg400-l>

or email: RPG400-L-request@xxxxxxxxxxxx
<mailto:RPG400-L-request@xxxxxxxxxxxx>

Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l
<http://archive.midrange.com/rpg400-l> .



--
This is the RPG programming on the IBM i / System i (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.

--
This is the RPG programming on the IBM i / System i (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.
--
This is the RPG programming on the IBM i / System i (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.

--
This is the RPG programming on the IBM i / System i (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.
--
This is the RPG programming on the IBM i / System i (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.

--
This is the RPG programming on the IBM i / System i (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.

--
This is the RPG programming on the IBM i / System i (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.


--
This is the RPG programming on the IBM i / System i (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.

--
This is the RPG programming on the IBM i / System i (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.


--
This is the RPG programming on the IBM i / System i (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.

--
This is the RPG programming on the IBM i / System i (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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
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.