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



Jerry,

A great xkcd comic about SQL injection: http://xkcd.com/327/

Paul Morgan

Principal Programmer Analyst
IT Supply Chain/Replenishment


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jerry C. Adams
Sent: Monday, August 01, 2011 10:32 AM
To: 'RPG programming on the IBM i / System i'
Subject: RE: Reduce large amount of logicals in SUBFL pgm,take in another direction

Okay, thanks to you and Paul, I think I see.

I really appreciate it because this distinction has always bugged me. Never
thought about, as in Paul's example, concatenating something like ';drop
thistable'.

I have frequently used the concatenation method for building SELECT
statements. However, the values used have either been determined by my
program, such as the name of the table to use, or merely a check list where
the user types an 'X' next to a literal field on a panel to use for, say, an
'ORDER BY'; I build the actual field values in the program. I've never
allowed the user to build the SQL statement per se, such as one can do with
STRSQL. I know how dangerous that can be; every time I use a DELETE
statement to clear up orphaned records, for example, not only do I test it
against a test file first, but I still stare at it long and hard before
pressing Enter!

For example, the panel the user sees might have:
_Location
_Department
_Class
Where they would enter 1, 2, and 3 to denote the sort sequence. This
scenario would not be susceptible to injection, would it?

Thanks.

Jerry C. Adams
IBM i Programmer/Analyst
On a good day I can make a snake faint.
--
A&K Wholesale
Murfreesboro, TN
615-867-5070


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

Jerry,

Basically, it has to do with when and how statement is parsed...

When you build the string without parameters, the user entered values
becomes part of the statement and is parsed like any other part of the
statement string.

However, when you use parameters, the statement is parsed and the
parameter markers identified for replacement. When the user value is
passes in, the system treats it simple as a value, it doesn't get
parsed as part of the statement.

HTH,
Charles

On Mon, Aug 1, 2011 at 9:21 AM, Jerry C. Adams <midrange@xxxxxxxx> wrote:
I hate to sound like an idiot (which I am as regards SQL), but I've read
these statements before about SQL injection and even googled it.

What I don't understand is why building the SELECT string with '+
UserEnteredValue' is more liable to injection that using parameters.  That
is, couldn't the parameters be compromised as well?

Just asking.

Jerry C. Adams
IBM i Programmer/Analyst
If you can't lose weight, fatten up your loved ones.  You'll look thinner
by
comparison.
--
A&K Wholesale
Murfreesboro, TN
615-867-5070


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Charles Wilt
Sent: Monday, August 01, 2011 8: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.



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.