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



Vern,

No sql at all actually.
Though way underneath the covers the method does use the sql
engine, I believe.

The solutions comes by the way of Mark Waterbury. I myself, was in the middle of developing an OA handler as well when he graciously shared his alternative. It was truly a gift at the time as my entire projects success was hinging on the ability to overcome this “predicament”.

Any pgm preceeding the usage of the encrypted file/field can be issued an ovrdbf/opnqryf combination and way under the covers the sql engine is invoked and processes the setll/read as it should by using the clear txt value and not the encoded value. An incredibly simple yet effective method. Thanks again Mark.

Anyhow this functionality is demonstrated in my download.

Jay


On Mar 29, 2020, at 8:01 PM, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx> wrote:

Hi Jay

I suspect my mention last week of the handler I wrote has muddied the waters. I did not at first understand that Mike was talking about creating the FIELDPROC. I don't remember the exact timing, but I think we got through that early in this thread.

So my question, related to your post about an end to end FIELDPROC solution (very cool and generous, BTW) - do you require that people use embedded SQL for this, or SQL procedures? To avoid the problem with a FIELDPROC over key fields? The vendor for who i wrote the handler was faced with customers who could not afford to change everything to embedded SQL.

Regards and stay well!
Vern

On 3/29/2020 6:06 PM, Jay Vaughn wrote:
The only thing Vern's OA handler is going to address is the side effect
that comes along with encrypted key fields paired with native I/O
combination of setll/read. Not to sell the handler short. It was a huge
accomplishment to have an handler developed to translate any native i/o
operation to the sql equivalent., so not trying to sell it short... but as
it relates to encryption and field procedures, this is the area in which it
is needed.

And this is applied to the application layer far after the field procedure
is created and attached to the file//field

jay

Jay

On Tue, Mar 24, 2020 at 8:45 AM Mike Jones <mike.jones.sysdev@xxxxxxxxx>
wrote:

Hi Vernon,

It means field procedure programs can't run SQL statements. It also means
field procedure programs can't call other commands or programs that run SQL
statements. This even applies to calling IBM commands and programs.

IBM's V7R3 SQL Reference manual page 1175, for the CREATE TABLE statements
for the FIELDPROC attribute says "Designates an external-program-name as
the field procedure exit routine for the column. It must be an ILE program
that does not contain SQL. It cannot be a service program."

I tried to make a field procedure program that would largely self-deploy,
where it would create a key store file (via IBM's API), and generate /
populate it with an encryption key, but calling the API to create a key
store file would not run. When I looked into why, it was because IBM's API
to create a key store file runs SQL, and I found documentation that you
can't do that.

I don't know why it is like that, just that it is.

Perhaps using an Open Access handler is a workaround, I don't know.

Those were my findings at the V7R3 level. I've not tried it on V7R4.

Mike

On Mon, Mar 23, 2020 at 9:39 PM Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
wrote:

Mike - I'm not sure exactly what your statement means. If you mean that
the programs themselves can't have embedded SQL, that's something I
don't know.

Now what I do know is that if a key field has a FIELDPROC added to it,
that the resulting order in RPG is probably incorrect. I wrote an Open
Access handler to turn all DISK IO in RPG into SQL statements - only
change in the RPG is to add the handler to the F-spec. I wrote this for
Townsend Security (this was publicly announced, so no secrets being
revealed here), I believe there is a relationship now with Syncsort, if
anyone has a need for this - I'm not working for either company, so this
is not a <verndor option>!!

Vern

On 3/23/2020 11:00 PM, Mike Jones wrote:
Nathan

Field proc programs don't yet allow the use of any SQL. They also
don't
yet support calling a program or command that directly or indirectly
runs
SQL statements. Example: you can't call IBM's API to create a key
store
file, from inside a field procedure program, because that API runs SQL
in
its plumbing.

I've gotten all that encryption field procedure stuff to work well,
but i
was a PITA getting it all to work. I utilized code from at least a
handful
of sample programs.

- AES 256-bit encryption
- DEKs (data encryption keys) stored in key store files.
- Using KEKs (key encrypting keys) to encrypt the DEKs (data
encryption
keys), also stored in key store files.
- Using master keys to encrypt the key store files.
- Using tokens with the encryption APIs so the keys can't be
retrieved
even under debug.

If you're trying to take a zoned decimal number, encrypt it, and store
it
in the database, you need to store the result in:

- A CHAR or VARCHAR column with the FOR BIT DATA attribute applied,
which uses CCSID 65535.
- A BINARY column
- A VARBINARY column
- A BLOB column
- Or the DDS file defined equivalent of one of the above.

The encrypted data is a binary string that requires one of those data
types
to store the results. Encrypted data doesn't conform to the set of
hex
values that are allowable for storage in a zoned or packed decimal
column,
Get the IBM Redbook "IBM System i Security: Protecting i5/OS Data with
Encryption". In the July 2008 version of that book, see chapter 7
"Database Considerations", section 7.2, pages 78 and 79 in particular,
talk
about storage requirements of the encrypted data.

Another great PDF book is "Protecting IBM i data with encryption" by
Kent
Milligan and Beth Hagemeister (March 2014). Read pages 31 through 33
for
storage requirements.

When you apply the field procedure to a column (ALTER TABLE ALTER
COLUMN),
it calls the field procedure to encrypt that column for ALL rows of the
table.
When you drop a field procedure from a column (ALTER TABLE ALTER
COLUMN),
it calls the field procedure to decrypt that column for ALL rows of the
table.

I imagine a field procedure could be used for a non-encryption purpose,
although I've not tried that (no use case comes to mind at the moment).
Assuming that is allowed, a field procedure that returns zoned decimal
data
for storage in a zoned decimal column is fine to do, but not with
encrypted
data.

HTH,

Mike

On Mon, Mar 23, 2020 at 4:48 PM Vernon Hamberg <
vhamberg@xxxxxxxxxxxxxxx
wrote:

Nathan

Is the numeric column in question a key of the file? Are you using
native record-level access? If either, then RPG might give you
problems.
I thought it was only with keyed columns, but maybe not.

You might try using SQL to process the file.

Vern

On 3/23/2020 3:26 PM, Nathan Hughes wrote:
First I want to apologize for my ignorance in RPGLE...we typically
don't
use this programming language, but have to for FieldProc purposes.
I've
researched this issue for a couple weeks now, and cannot find a
resolution.
I'm currently trying to write a field procedure program. I have taken
the
IBM RPGLE example (
https://www.ibm.com/support/knowledg...yfpexample.htm<
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/sqlp/rbafyfpexample.htm
),
and started to modify it to work for us.
Here is the table that was created for testing purposes:
********** Beginning of data *************************************
A R REC
A ECNUM 16S 0 COLHDG('NUMBER')
A ALIAS(EC_NUMBER)
A ECALPHA 32A COLHDG('ALPHA')
A ALIAS(EC_ALPHA)
************* End of data ****************************************
The program from the link above allows for several SQL types, clob,
varchar, char, blob, etc., but I needed to add zoned numeric. To
begin,
I
studied and stepped through the program to see what it was doing, and
thought I had a good understanding of how it worked. I then started
adding
what appeared as necessary for the SQL_TYP_ZONED (488).
When I send the 'ALTER TABLE' command and set the FieldProc program
on
a
column, the first call is to Register(function code: 8), the next is
Encode(function code: 0) to encode the values in said column.
I set a breakpoint at the end of the program, and evaluated the
parameters when setting the FieldProc on a VARCHAR and ZONED NUMERIC
columns, and besides being different data types all appears correct,
but
the ZONED NUMERIC column abruptly stops with the error below after the
register call.
Message ID . . . . . . : SQL0685
Date sent . . . . . . : 03/18/20 Time sent . . . . . . : 16:17:04

Message . . . . : Field procedure on column ECNUM has returned
invalid
data.
Cause . . . . . : Field procedure on column ECNUM has returned
invalid
data.
Recovery . . . : Change the field procedure to return valid data.

Thank you in advance for any help you can give on this.

Thanks,
Nathan Hughes
Software Developer

[BadgePass]
601.499.2131 Office
280 Trace Colony Park
Ridgeland, MS 39157

CONFIDENTIALITY NOTICE: This e-mail message, including any
attachments,
is for the sole use of the intended recipient(s) and may contain
confidential and privileged information or otherwise protected by law.
Any
unauthorized review, use, disclosure or distribution is prohibited. If
you
are not the intended recipient, please contact the sender by reply
e-mail
and destroy all copies of the original message.

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link: https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com


--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com

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.