RE: FieldProc Error - column has returned invalid data on a Zoned Numeric column

Mike,

Your first line
Yes, convert the column(s) to VARCHAR FOR BIT DATA...

Are you referring to doing this in my FieldProc, or the actual column on the table?

Thanks,
Nathan Hughes
Software Developer


601.499.2131 Office
280 Trace Colony Park
Ridgeland, MS 39157

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Mike Jones
Sent: Wednesday, March 25, 2020 2:37 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: FieldProc Error - column has returned invalid data on a Zoned Numeric column

Hi Nathan,

Yes, convert the column(s) to VARCHAR FOR BIT DATA, so the content is treated as a binary string (uses CCSID 65535). Length 16 should be fine if the raw data was that length or shorter. That is very likely one of your issues. IIRC, you want to define encoded columns in multiples of 16 bytes long when using AES encryption.

If you use a DDS definition for the table instead of SQL DDL, the field must be a fixed or varying length character field with CCSID(65535) specified.

The SQL DDL choices for a data type to store encrypted content are all binary ones: BINARY, VARBINARY, CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BLOB.

Initially, try applying the field procedure to an empty copy of the table, to eliminate the extra work / failure risk of encrypting data. Once that works, then try applying the field procedure to a copy of the table with rows of data containing non-null values to encrypt.

Townsend may well have implemented something special to encrypt a zoned or packed decimal and store it in a zoned or packed decimal, but I doubt that would be standard AES encryption, and I don't think that would it be as secure, because the hex values allowed to be stored in a zoned or packed decimal are a much more limited set of values.

If you get stuck with more issues, post the details here and I should be able to help.

Vernon's posts illustrate additional concerns when consuming the encrypted data. Some concerns could result in incorrect functional behavior, and some concerns are for the potential of dramatic, negative performance impact. Some applications may experience functional and performance issues, that could easily be related to the central change that you're now accessing encoded data values instead of non-encoded, raw values. The sorted order of encoded values changes the behavior of keyed RPG operations like SETLL, SETGT, etc.. Using SQL alleviates some of that, but be on the lookout for negative performance impact even using SQL. I ran some tests to see if SQL was smart enough to automatically encrypt search criteria that was used in a WHERE clause predicate against an encrypted, field procedure column, and it was smart enough to do that, but is dependent on the setting of QAQQINI query option FIELDPROC_ENCODED COMPARISON, read this
-
https://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.ibm.com%2Fsupport%2Fknowledgecenter%2Fssw_ibm_i_74%2Frzajq%2Frzajqfieldprocsexamples.htm&amp;data=01%7C01%7Cnathan.hughes%40badgepass.com%7C3869c992d40e4537f39208d7d0f3fb7e%7C224758f072634e52a6e4f5db2afdf99e%7C0&amp;sdata=BhGa4BbdPWB53n4GoxU0bqLrBbX1lVJtBIVtaW6LfE4%3D&amp;reserved=0
.
The query optimizer will rewrite the query, avoiding a full table scan that would otherwise decode every row.

As Vernon mentions, I can see how an OA handler program might help with dealing with RPG's shortcomings with processing encoded data. But, I've never built an OA program, nor have I spent many brain cycles thinking about leveraging them for encryption. I'm not very involved with our company's consumption of encoded data.

Mike

On Wed, Mar 25, 2020 at 9:21 AM Nathan Hughes <nathan.hughes@xxxxxxxxxxxxx>
wrote:

Mike,

Thanks for all that information, I'll certainly put it to use, and
start reading up on those topics/documents.
I read the same about SQL in this FieldProc program, and I'm not using
any, or any external calls that use them. I'm simply trying to
encrypt 2 different types of columns with the IBM i Cryptographic
Services APIs, which should not be using SQL. So what you're saying
is that I'll need to convert them to VARCHAR before the encryption?
Speaking of Townsend, they have several articles and videos on the
topic of FieldProcs...and they claim that you can encrypt numerics
using their software, so I'm sure it can be done.

Thanks,
Nathan Hughes
Software Developer


601.499.2131 Office
280 Trace Colony Park
Ridgeland, MS 39157

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Vernon Hamberg
Sent: Tuesday, March 24, 2020 3:06 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: FieldProc Error - column has returned invalid data on a
Zoned Numeric column

Hi Mike

Thanks for the clarification. It leaves me with a question, does a
field have to be non-numeric in order for a FIELDPROC to work correctly.

This goes back to the reason I was asked to write that OA handler. IBM
had suggested it to Patrick Townsend when the order of records
returned was incorrect when one of the key fields had a FIELDPROC on
it. In a simplistic way, it seems that SQL does the decryption on the
field, then does the sort on the decrypted values. I believe I can
describe RPG's process as reading the file in the key order, but it is
reading it with encrypted values. Then it decrypts the values.

So I wonder if, in your case, RPG is pulling the encrypted value into
a buffer or some such where that field is defined as numeric - hence
the message. And using SQL instead of RLA might get the decryption done first.
This does seem to fit the scenario you describe.

But I might be off somewhere, so verify if you can, maybe using SQL for IO.

Good luck and stay well!
Vern

On 3/24/2020 7:45 AM, Mike Jones 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://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fw
ww
.ibm.com%2Fsupport%2Fknowledg...yfpexample.htm&amp;data=01%7C01%7Cn
at
han.hughes%40badgepass.com%7C046102b234904ab2e04308d7d02d4716%7C224
75
8f072634e52a6e4f5db2afdf99e%7C0&amp;sdata=8Rms64JG%2FNBIN4Ch7DAXS2Z
Yp jNnnhjtcDLRULkHdFM%3D&amp;reserved=0<
https://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fw
ww
.ibm.com%2Fsupport%2Fknowledgecenter%2Fssw_ibm_i_73%2Fsqlp%2Frbafyf
pe
xample.htm&amp;data=01%7C01%7Cnathan.hughes%40badgepass.com%7C04610
2b
234904ab2e04308d7d02d4716%7C224758f072634e52a6e4f5db2afdf99e%7C0&am
p;
sdata=7BNErcvYNKloKMRCG%2FJVdLDp%2BkccvACG7XCJQP3QRek%3D&amp;reserv
ed
=0

),

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://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2
Fl
ists.midrange.com%2Fmailman%2Flistinfo%2Frpg400-l&amp;data=01%7C0
1%
7Cnathan.hughes%40badgepass.com%7C046102b234904ab2e04308d7d02d471
6%
7C224758f072634e52a6e4f5db2afdf99e%7C0&amp;sdata=r7FbJmC3kkq864B7
ZG
nxWWISxGiITPF%2FA%2BhOZ6BcXyM%3D&amp;reserved=0
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at

https://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Farch
ive.midrange.com%2Frpg400-l&amp;data=01%7C01%7Cnathan.hughes%40badgepa
ss.com%7C3869c992d40e4537f39208d7d0f3fb7e%7C224758f072634e52a6e4f5db2a
fdf99e%7C0&amp;sdata=6RFeL0MY5rx37VlzFa2srZq%2F4b8l2wDnxmxis0KDDvY%3D&
amp;reserved=0
.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link:
https://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2
Fa
mazon.midrange.com&amp;data=01%7C01%7Cnathan.hughes%40badgepass.c
om
%7C046102b234904ab2e04308d7d02d4716%7C224758f072634e52a6e4f5db2af
df
99e%7C0&amp;sdata=KMsdz5SsySl%2Bvgpj8y4q5ibliZYYFYESdq%2BR7r0M0JU
%3
D&amp;reserved=0

--
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://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fl
is
ts.midrange.com%2Fmailman%2Flistinfo%2Frpg400-l&amp;data=01%7C01%7C
na
than.hughes%40badgepass.com%7C046102b234904ab2e04308d7d02d4716%7C22
47
58f072634e52a6e4f5db2afdf99e%7C0&amp;sdata=r7FbJmC3kkq864B7ZGnxWWIS
xG
iITPF%2FA%2BhOZ6BcXyM%3D&amp;reserved=0
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at

https://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Farch
ive.midrange.com%2Frpg400-l&amp;data=01%7C01%7Cnathan.hughes%40badgepa
ss.com%7C3869c992d40e4537f39208d7d0f3fb7e%7C224758f072634e52a6e4f5db2a
fdf99e%7C0&amp;sdata=6RFeL0MY5rx37VlzFa2srZq%2F4b8l2wDnxmxis0KDDvY%3D&
amp;reserved=0
.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link:
https://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fa
ma
zon.midrange.com&amp;data=01%7C01%7Cnathan.hughes%40badgepass.com%7
C0
46102b234904ab2e04308d7d02d4716%7C224758f072634e52a6e4f5db2afdf99e%
7C
0&amp;sdata=KMsdz5SsySl%2Bvgpj8y4q5ibliZYYFYESdq%2BR7r0M0JU%3D&amp;
re
served=0

--
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://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Flist
s.midrange.com%2Fmailman%2Flistinfo%2Frpg400-l&amp;data=01%7C01%7Cnath
an.hughes%40badgepass.com%7C3869c992d40e4537f39208d7d0f3fb7e%7C224758f
072634e52a6e4f5db2afdf99e%7C0&amp;sdata=3FDOtKhhryOo%2FTffFn%2F7fFxkze
FAyVc6%2FIVfaXKfyTc%3D&amp;reserved=0
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Farch
ive.midrange.com%2Frpg400-l&amp;data=01%7C01%7Cnathan.hughes%40badgepa
ss.com%7C3869c992d40e4537f39208d7d0f3fb7e%7C224758f072634e52a6e4f5db2a
fdf99e%7C0&amp;sdata=6RFeL0MY5rx37VlzFa2srZq%2F4b8l2wDnxmxis0KDDvY%3D&
amp;reserved=0
.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Famaz
on.midrange.com&amp;data=01%7C01%7Cnathan.hughes%40badgepass.com%7C386
9c992d40e4537f39208d7d0f3fb7e%7C224758f072634e52a6e4f5db2afdf99e%7C0&a
mp;sdata=ObhmjYKJUEmGY7ePdL6cUrQZ62Jkl6yBlSIDQ0TmDO4%3D&amp;reserved=0
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://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Flist
s.midrange.com%2Fmailman%2Flistinfo%2Frpg400-l&amp;data=01%7C01%7Cnath
an.hughes%40badgepass.com%7C3869c992d40e4537f39208d7d0f3fb7e%7C224758f
072634e52a6e4f5db2afdf99e%7C0&amp;sdata=3FDOtKhhryOo%2FTffFn%2F7fFxkze
FAyVc6%2FIVfaXKfyTc%3D&amp;reserved=0
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Frpg400-l&amp;data=01%7C01%7Cnathan.hughes%40badgepass.com%7C3869c992d40e4537f39208d7d0f3fb7e%7C224758f072634e52a6e4f5db2afdf99e%7C0&amp;sdata=6RFeL0MY5rx37VlzFa2srZq%2F4b8l2wDnxmxis0KDDvY%3D&amp;reserved=0.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Famaz
on.midrange.com&amp;data=01%7C01%7Cnathan.hughes%40badgepass.com%7C386
9c992d40e4537f39208d7d0f3fb7e%7C224758f072634e52a6e4f5db2afdf99e%7C0&a
mp;sdata=ObhmjYKJUEmGY7ePdL6cUrQZ62Jkl6yBlSIDQ0TmDO4%3D&amp;reserved=0

--
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://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Frpg400-l&amp;data=01%7C01%7Cnathan.hughes%40badgepass.com%7C3869c992d40e4537f39208d7d0f3fb7e%7C224758f072634e52a6e4f5db2afdf99e%7C0&amp;sdata=3FDOtKhhryOo%2FTffFn%2F7fFxkzeFAyVc6%2FIVfaXKfyTc%3D&amp;reserved=0
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Frpg400-l&amp;data=01%7C01%7Cnathan.hughes%40badgepass.com%7C3869c992d40e4537f39208d7d0f3fb7e%7C224758f072634e52a6e4f5db2afdf99e%7C0&amp;sdata=6RFeL0MY5rx37VlzFa2srZq%2F4b8l2wDnxmxis0KDDvY%3D&amp;reserved=0.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com&amp;data=01%7C01%7Cnathan.hughes%40badgepass.com%7C3869c992d40e4537f39208d7d0f3fb7e%7C224758f072634e52a6e4f5db2afdf99e%7C0&amp;sdata=ObhmjYKJUEmGY7ePdL6cUrQZ62Jkl6yBlSIDQ0TmDO4%3D&amp;reserved=0
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.