Re: Using host variables in SQLRPGLE doesn't seem to be working.... IN clause -- RPG400-L

Haha, yes, I've seen that strip before.

Yes, my code probably doesn't meet the PCI compliance in this case, then
again this isn't a concern for us as we don't need to be PCI compliant.
Now, apart from that...

The values used to create NameString goes through a data cleansing
procedure that would thwart such an attack by default since cleans out our
definition of 'invalid' search terms, and the word 'and' would be stripped
out. As well as 'or', and other small dictionary words that would be
neccessary to perform such an attack. So there is a lot of reasons why I
believe this prepared statement is 100% safe.

Thanks
Bryce Martin
Programmer/Analyst I
570-546-4777

Charles Wilt <charles.wilt@xxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
01/10/2011 02:15 PM
Please respond to
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>

To
"RPG programming on the IBM i / System i" <rpg400-l@xxxxxxxxxxxx>
cc

Subject
Re: Using host variables in SQLRPGLE doesn't seem to be working.... IN
clause

Bryce,

It may seem that way at first glance...however you need to realize
that a user seeing data they are not authorized to is only one concern
with SQL injection...

See here for a humorous take on SQL injection..
http://xkcd.com/327/

Google for more detailed information...

If you are subject to PCI, note that dynamic SQL without parameters
fails secure code review.

As general rule, production code should always use parameters.

HTH,
Charles

On Mon, Jan 10, 2011 at 1:30 PM, Bryce Martin <BMartin@xxxxxxxxxxxx>
wrote:

I definitely understand the concern you raise, but this statement is
behind a few layers of logic and doesn't actually return results a

calling

program. The results are used as park of a ranking algorithm with other
criteria and goes through a weighting algorithm that returns only the

top

20 results. So SQL injection isn't a worry.

Thanks
Bryce Martin
Programmer/Analyst I
570-546-4777

"Mark Murphy/STAR BASE Consulting Inc." <mmurphy@xxxxxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
01/10/2011 12:56 PM
Please respond to
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>

To
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
cc

Subject
RE: Using host variables in SQLRPGLE doesn't seem to be working....

clause

Beware of SQL Injection attacks when you do this. If NameString were to
contain something like "space' and ('1' = '1" you would get every record
in the database. May or may not be an issue in this application since

the

user may be authorized to all the records, but just saying that using
prepared statements with appropriate parameter markers is far safer than
allowing the user to enter SQL source.

Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx

From: "Schutte, Michael D" <Michael_Schutte@xxxxxxxxxxxx>
To: RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
Date: 01/10/2011 08:23 AM
Subject: RE: Using host variables in SQLRPGLE doesn't seem to be
working.... IN clause
Sent by: rpg400-l-bounces@xxxxxxxxxxxx

Another option is to build the entire select statement in a string
variable. Prepare it and then declare your cursor over the prepared
statement.

For example.

STRSQL = 'SELECT ' + ColumnsSQL + ' FROM DCR_PIVOT +
WHERE RNWORD1 in (' + %Trim(NameString) + ') +
ORDER BY ' + %TRIM(SortBy);
Exec SQL PREPARE S1 FROM :STRSQL;

Exec SQL DECLARE C1 CURSOR FOR S1;
Exec SQL OPEN C1;

Exec SQL Fetch C1 Into :ColumnsDS
DoW SQLCOD <> 100 And SQLCOD >= 0;
...
Exec SQL Fetch C1 Into :ColumnsDS
EndDo;

I'm assuming RNWORD1 is character so you will need to make sure that you
have the single quotes around each name in your string.

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx

[mailto:rpg400-l-bounces@xxxxxxxxxxxx]

On Behalf Of Bryce Martin
Sent: Friday, January 07, 2011 5:08 PM
To: RPG programming on the IBM i / System i
Subject: Using host variables in SQLRPGLE doesn't seem to be working....
IN clause

I might be trying to be too fancy here, but I thought that maybe this
would work.

I have an array of values (words) that I need to search a particular

field

for in a file.... So I thought that this might work...

Exec SQL
Declare NameSet Cursor For
Select Cast(rncust As Numeric(6,0)),
Cast(rnseq As Numeric(4,0)),
Cast(rntype As Numeric(2,0)),
rnword1, rnword2, rnword3, rnword4, rnword5
From rcm002pf
Where rnword1 in(:NameString)
and rntype = (case when :type<>'' then :NumType else

rntype

End)
Order by rncust, rnseq;

I build NameString from the array values. Its a character string. Its
value is like... 'word1','word2','word3'

My thinking is that NameString would just plop right inside the IN()
clause and act as the list to check. But I keep getting and sql 100

error

(no results). If I put this into my favorite sql tool it works fine.

I know that its something to do with the SQL. I thought this would be
cleaner than doing a Prepare, but maybe I'll have to do it anyway.

Any thoughts????

Thanks
Bryce Martin
Programmer/Analyst I
570-546-4777
--- This message (including any attachments) is intended only for the

use

of the individual or entity to which it is addressed and may contain
information that is non-public, proprietary, privileged, confidential,

and

exempt from disclosure under applicable law. If you are not the intended
recipient, you are hereby notified that any use, dissemination,
distribution, or copying of this communication is strictly prohibited.

you have received this communication in error, please notify us and
destroy this message immediately. ---
--

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 message (including any attachments) is intended only for the

use of the individual or entity to which it is addressed and may contain
information that is non-public, proprietary, privileged, confidential, and
exempt from disclosure under applicable law. If you are not the intended
recipient, you are hereby notified that any use, dissemination,
distribution, or copying of this communication is strictly prohibited. If
you have received this communication in error, please notify us and
destroy this message immediately. ---

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