Home > Archive > Cobol > June 2007 > Re: Getting SQLCODE -302 after migrating DB2 V7 to V8 IN ibm z/os
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Re: Getting SQLCODE -302 after migrating DB2 V7 to V8 IN ibm z/os
|
|
| Arnold Trembley 2007-06-03, 3:55 am |
|
LX-i wrote:
> Arnold Trembley wrote:
>
>
>
> Is that just a step your organization has taken to comply, or do we
> actually have a law on the books prohibiting Dynamic SQL?
>
I'm not a lawyer, and I am not aware of any law prohibiting Dynamic
SQL. Here's the problem, based on this quote from Wikipedia:
"In terms of compliance, the key rules under the Act include The
Financial Privacy Rule which governs the collection and disclosure of
customers’ personal financial information by financial institutions.
It also applies to companies, regardless of whether they are financial
institutions, who receive such information. The Safeguards Rule
requires all financial institutions to design, implement and maintain
safeguards to protect customer information. The Safeguards Rule
applies not only to financial institutions that collect information
from their own customers, but also to financial institutions – such as
credit reporting agencies – that receive customer information from
other financial institutions."
My understanding of the issue is that if data is taken from the web
and inserted into a variable used by dynamic SQL then there is a
possibility that personal financial data could be altered or publicly
exposed.
I don't really think it's a huge issue, because I'm not aware that we
use dynamic SQL in any mainframe applications. And we don't take
input from the public internet and put it into SQL statements. But
there is a lot of concern about the security of databases connected to
the web.
So the short answer to your question is it's a step my organization is
taking to reduce its risk.
--
http://arnold.trembley.home.att.net/
| |
|
|
|
| Arnold Trembley wrote:
>
>
> Frank Swarbrick wrote:
>
> That could certainly be the case, although I think it is more likely
> that *I* don't understand all the security issues. We're being told
> that COBOL programs need to guard aginst "cross-site scripting", but
> none of the COBOL applications I work with are web-enabled.
>
> Instead, it's been suggested that we invalidate input fields that
> contain special characters, such as % and +, for example.
For SQL Injection, ' (apostrophe) and ; (semi-colon) are the ones that
can cause you problems... :)
> There is some mainframe Java in my organization, but I have no
> experience with JDBC. It's not in my COBOL applications. We've only
> been using DB2 for about six or seven years and we haven't even tried
> out dynamic SQL in COBOL yet.
On the Unisys mainframe (2200/ClearPath IX), just from a performance
angle, dynamic SQL costs significant overhead the first time it's used.
Granted, if you're executing the same dynamic statement 100 times or
more, it really doesn't matter. The numbers they gave in the manual for
performance were ratios. If a dynamic statement took 1.0 to process,
the same Static ESQL took 0.6. If the table definition had changed
since the program had been compiled, it did an on-the-fly compilation of
that statement; these took 0.65. Of course, the second and further
executions are the same.
One thing that the system I'm working on now does is keep all their
queries in XML files. Then, they pass a function name and parameter
array to the "DB Layer", and it handles all the database access. It's a
very  system; if I had the time to do something like that for my
prior system, I'd be a hero. :) Load them up, PREPARE them as part of
system start-up - then boom! You don't have to recompile to change the
query, and you don't have to use dynamic SQL in your program!
Of course, Java makes that a little easier to do than COBOL, but it
could still be done. I'm thinking of a small procedural subprogram that
returns fields by name, and has a "move next" and "move prior" method as
well. It wouldn't be as pretty as
for (ReturnObject obj : aReturnObjects) {
sName = obj.getString("name");
sTitle = obj.getString("title");
iAge = obj.getInteger("age");
}
but it could be just as functional...
*> Assuming that the "get data" puts the pointer at the first
*> result, and sets ws-status if no results are found...
*> Also keep in mind that this is straight COBOL 85, with no
*> object extensions.
perform until ws-status = EOF
move "name" to ws-field
call "db-result-string" using ws-field ws-name
on exception go to uh-oh
end-call
move "title" to ws-field
call "db-result-string" using ws-field ws-title
on exception go to uh-oh
end-call
move "age" to ws-type
call "db-result-number" using ws-field ws-age
on exception go to uh-oh
end-call
call "db-next-result" using ws-status
on exception go to uh-oh
end-call
end-perform
This actually wouldn't be that hard to implement, as a working model.
What would be a bear is actually changing the code to use the new model.
IMO, it would be very and worth the effort, though.
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~
~ / \ / ~ Live from Albuquerque, NM! ~
~ / \/ o ~ ~
~ / /\ - | ~ daniel@thebelowdomain ~
~ _____ / \ | ~ http://www.djs-consulting.com/linux/blog ~
~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
~ GEEKCODE 3.12 GCS/IT d s-:+ a C++ L++ E--- W++ N++ o? K- w$ ~
~ !O M-- V PS+ PE++ Y? !PGP t+ 5? X+ R* tv b+ DI++ D+ G- e ~
~ h---- r+++ z++++ ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~
"Who is more irrational? A man who believes in a God he doesn't see, or
a man who's offended by a God he doesn't believe in?" - Brad Stine
| |
| Arnold Trembley 2007-06-05, 9:55 pm |
|
docdwarf@panix.com wrote:
> In article <R7X8i.103793$p47.39693@bgtnsc04-news.ops.worldnet.att.net>,
> Arnold Trembley <arnold.trembley@worldnet.att.net> wrote:
>
> [snip]
>
>
>
>
> Oh, I *cannot* resist... what an outmoded, outdated place that must be, Mr
> Trembley! You only process, what... about a billion transactions a day,
> across twenty-seven timezones or so?
>
> DD
Actually, it's roughly between 50 and 100 million per day, but the 27
time zones is probably correct. :-)
With kindest regards,
--
http://arnold.trembley.home.att.net/
| |
| Arnold Trembley 2007-06-05, 9:55 pm |
|
LX-i wrote:
> Arnold Trembley wrote:
>
>
>
> For SQL Injection, ' (apostrophe) and ; (semi-colon) are the ones that
> can cause you problems... :)
Yes, I think those were also included in the list of "special
characters" that were risky.
>
>
>
> On the Unisys mainframe (2200/ClearPath IX), just from a performance
> angle, dynamic SQL costs significant overhead the first time it's used.
> Granted, if you're executing the same dynamic statement 100 times or
> more, it really doesn't matter. The numbers they gave in the manual for
> performance were ratios. If a dynamic statement took 1.0 to process,
> the same Static ESQL took 0.6. If the table definition had changed
> since the program had been compiled, it did an on-the-fly compilation of
> that statement; these took 0.65. Of course, the second and further
> executions are the same.
>
> One thing that the system I'm working on now does is keep all their
> queries in XML files. Then, they pass a function name and parameter
> array to the "DB Layer", and it handles all the database access. It's a
> very system; if I had the time to do something like that for my
> prior system, I'd be a hero. :) Load them up, PREPARE them as part of
> system start-up - then boom! You don't have to recompile to change the
> query, and you don't have to use dynamic SQL in your program!
>
> Of course, Java makes that a little easier to do than COBOL, but it
> could still be done. I'm thinking of a small procedural subprogram that
> returns fields by name, and has a "move next" and "move prior" method as
> well. It wouldn't be as pretty as
>
> for (ReturnObject obj : aReturnObjects) {
> sName = obj.getString("name");
> sTitle = obj.getString("title");
> iAge = obj.getInteger("age");
> }
>
> but it could be just as functional...
>
> *> Assuming that the "get data" puts the pointer at the first
> *> result, and sets ws-status if no results are found...
> *> Also keep in mind that this is straight COBOL 85, with no
> *> object extensions.
> perform until ws-status = EOF
>
> move "name" to ws-field
> call "db-result-string" using ws-field ws-name
> on exception go to uh-oh
> end-call
>
> move "title" to ws-field
> call "db-result-string" using ws-field ws-title
> on exception go to uh-oh
> end-call
>
> move "age" to ws-type
> call "db-result-number" using ws-field ws-age
> on exception go to uh-oh
> end-call
>
> call "db-next-result" using ws-status
> on exception go to uh-oh
> end-call
>
> end-perform
>
> This actually wouldn't be that hard to implement, as a working model.
> What would be a bear is actually changing the code to use the new model.
> IMO, it would be very and worth the effort, though.
>
That's a very interesting idea, sort of a wrapper around the actual
SQL calls. I suppose it might also be done with DB2 stored
procedures. We have a few in the shop, but none in the applications I
support.
Thanks for sharing that.
With kindest regards,
--
http://arnold.trembley.home.att.net/
| |
| Pete Dashwood 2007-06-06, 9:55 pm |
|
"Arnold Trembley" <arnold.trembley@worldnet.att.net> wrote in message
news:Qoo9i.111509$p47.31073@bgtnsc04-news.ops.worldnet.att.net...
>
>
> LX-i wrote:
>
to the "DB Layer", and it handles all the database access. It's a
Why not simply use a data provider? Let the system use XML or RDB but the
interface to it would be the same.
I came across a C# web template the other day that has that option. You can
load up data from XML or SQL Server but they are seen as Data Providers and
the interface is identical. (Flick a switch on startup to decide DB or XML).
Add other Data Provider classes and the interface still remains the same...
really .
I'm not sure about encapsulating SQL into XML then dynamically setting the
SQL from that. It gives flexibility, certainly , but it also allows
maintenance of the SQL externally from the system. I know you see that as a
benefit, but there are downsides to it too...:-)
Every time you change the SQL you must regression test everything.
If the "DB Layer" was a (Data Provider) Class with Methods for each of the
queries, you wouldn't have the regression testing problem.
You could add and replace methods as required and everything else would
continue to function.
[color=darkred]
.... Or you could use a data reader object :-)[color=darkred]
I'd like it better if you overloaded the DB call so it automatically decided
whether the field was a string or a number (you can get this from the DB
Schema; or, if you use a Table Adapter, it is returned automatically in
the column properties when you get the field.)
However, given that you want to stick to COBOL and not use OO... how about :
[color=darkred]
returning ws-name[color=darkred]
.... where you check the returned DB Host variable field for numeric or
alphanumeric before placing it in ws-name or whatever the returning field
for the call is.
( I don't like the condition being = EOF and the go to uh-oh either, but
we'll let those pass... :-)
Bottom line is... it's MUCH more elegant in C# (or Java).
[color=darkred]
>
> That's a very interesting idea, sort of a wrapper around the actual SQL
> calls. I suppose it might also be done with DB2 stored procedures. We
> have a few in the shop, but none in the applications I support.
>
Stored procedures would be a much better solution. The fields for the row
can be entered into host variables and that's it. You need a trigger for the
procedure, but it's fairly easy to contrive one. Not sure whether it is
possible to run a cursor from a stored proc but that has some interesting
possibilities too... :-)
Pete.
| |
|
| Pete Dashwood wrote:
> "Arnold Trembley" <arnold.trembley@worldnet.att.net> wrote in message
> news:Qoo9i.111509$p47.31073@bgtnsc04-news.ops.worldnet.att.net...
> to the "DB Layer", and it handles all the database access. It's a
> Why not simply use a data provider? Let the system use XML or RDB but the
> interface to it would be the same.
I think that's what the "DB Layer" is - it could be swapped out for
whatever, and our "business functions" would still be named the same.
> I'm not sure about encapsulating SQL into XML then dynamically setting the
> SQL from that. It gives flexibility, certainly , but it also allows
> maintenance of the SQL externally from the system. I know you see that as a
> benefit, but there are downsides to it too...:-)
True - but my old system, the flexibility would *far* outweigh the
risks, IMO... We had changes that we needed to make, but politically,
we couldn't make them.
> Every time you change the SQL you must regression test everything.
No - nowhere near "everything"... :) It's split into separate files,
so the changes are somewhat isolated.
> If the "DB Layer" was a (Data Provider) Class with Methods for each of the
> queries, you wouldn't have the regression testing problem.
>
> You could add and replace methods as required and everything else would
> continue to function.
I haven't gotten into it enough to know the exact implementation. Plus,
it's pretty much one of those things where you set it and forget it. It
returns an array of ReturnObject objects, which have the requisite
getString(), getInteger(), etc.
>
> ... Or you could use a data reader object :-)
Would that be any less code?
for (DataRow dr in ds["my_table"]) {
sName = dr["name"].toString();
sTitle = dr["title"].toString();
iAge = Convert.ToInt32(dr["age"]);
}
>
> I'd like it better if you overloaded the DB call so it automatically decided
> whether the field was a string or a number (you can get this from the DB
> Schema; or, if you use a Table Adapter, it is returned automatically in
> the column properties when you get the field.)
>
> However, given that you want to stick to COBOL and not use OO... how about :
>
> returning ws-name
>
> ... where you check the returned DB Host variable field for numeric or
> alphanumeric before placing it in ws-name or whatever the returning field
> for the call is.
You could do that. I'm not sure whether the Unisys compiler supported
"returning" on the call statement. I've never seen it (but, of course,
that doesn't mean that it doesn't exist).
It could be done.
> ( I don't like the condition being = EOF and the go to uh-oh either, but
> we'll let those pass... :-)
Think Q&D - those would be fleshed out later...
> Bottom line is... it's MUCH more elegant in C# (or Java).
True - but, my idea is how to implement it in my former environment.
The one problem I have with my model is that it would only support one
open dataset at a time. Without being able to create another instance
of my data reader subprogram, I would have to put some kind of
hocus-pocus in it to keep track of multiple result sets, and then the
program would have to specify which one it wanted the "next" of.
(If I could do the object thing, it would be really easy...)
>
> Stored procedures would be a much better solution. The fields for the row
> can be entered into host variables and that's it. You need a trigger for the
> procedure, but it's fairly easy to contrive one. Not sure whether it is
> possible to run a cursor from a stored proc but that has some interesting
> possibilities too... :-)
In our shop, we figured out how to pass CLOBs across a linked database
in Oracle. I'm hoping to post the details on my tech blog when I get
the information pulled together. It uses stored procedures.
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~
~ / \ / ~ Live from Albuquerque, NM! ~
~ / \/ o ~ ~
~ / /\ - | ~ daniel@thebelowdomain ~
~ _____ / \ | ~ http://www.djs-consulting.com/linux/blog ~
~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
~ GEEKCODE 3.12 GCS/IT d s-:+ a C++ L++ E--- W++ N++ o? K- w$ ~
~ !O M-- V PS+ PE++ Y? !PGP t+ 5? X+ R* tv b+ DI++ D+ G- e ~
~ h---- r+++ z++++ ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~
"Who is more irrational? A man who believes in a God he doesn't see, or
a man who's offended by a God he doesn't believe in?" - Brad Stine
| |
|
| (pardon me while I talk to myself...)
LX-i wrote:
> Pete Dashwood wrote:
>
> True - but my old system, the flexibility would *far* outweigh the
> risks, IMO... We had changes that we needed to make, but politically,
> we couldn't make them.
I forgot to mention that, in our current environment, these XML files
are as secure as the rest of our code, and are deployed compressed into
a WAR file. (Not that it's impossible to change things there, it's just
a bit more challenging.)
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~
~ / \ / ~ Live from Albuquerque, NM! ~
~ / \/ o ~ ~
~ / /\ - | ~ daniel@thebelowdomain ~
~ _____ / \ | ~ http://www.djs-consulting.com/linux/blog ~
~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
~ GEEKCODE 3.12 GCS/IT d s-:+ a C++ L++ E--- W++ N++ o? K- w$ ~
~ !O M-- V PS+ PE++ Y? !PGP t+ 5? X+ R* tv b+ DI++ D+ G- e ~
~ h---- r+++ z++++ ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~
"Who is more irrational? A man who believes in a God he doesn't see, or
a man who's offended by a God he doesn't believe in?" - Brad Stine
| |
|
|
|
|
|
|
|