Example field procedure program
Add field procedure FP1 to column C1. The Field Procedure FP1 takes one parameter which indicates the number of bytes of the column the field procedure should operate on.
ALTER TABLE TESTTAB ALTER C1 SET FIELDPROC FP1(10)The following two field procedure programs demonstrate the same operations being done first in ILE RPG and then in C. These programs illustrate how a field procedure program could use the IBM i Cryptographic Services APIs to encrypt and decrypt fixed length character, variable length character, or character LOB data.
The program demonstrates some
of the possibilities with field procedure programs but does not attempt
to handle every possible situation and error. Note how the following
items are handled:
- Changing of the encoded data type from the decoded data type
- The addressing differences required for fixed length character, varying length character, and CLOB data.
- Usage of the optional parm structure (sqlfpOptionalParameterValueDescriptor_T).
More information about IBM i Cryptographic Services APIs, see Cryptographic Services APIs
Example field procedure written in ILE RPG
For a field procedure written in ILE RPG, you should
consider the following items:
- For better performance, ACTGRP(*CALLER) and STGMDL(*INHERIT) is recommended when you compile your program. This example has these keywords in the Control statements (H specs).
- The THREAD keyword must be specified on the Control statements. This example has THREAD(*CONCURRENT), but THREAD(*SERIALIZE) could also be used.
ctl-opt main(SampleFieldProcProgram);
ctl-opt option(*srcstmt);
ctl-opt stgmdl(*inherit);
ctl-opt thread(*concurrent);
/if defined(*crtbndrpg)
ctl-opt actgrp(*caller);
/endif
/copy QSYSINC/QRPGLESRC,QC3CCI
/copy QSYSINC/QRPGLESRC,QUSEC
/copy QSYSINC/QRPGLESRC,SQL
/copy QSYSINC/QRPGLESRC,SQLFP
// QSYSINC/H QC3DTAEN
dcl-pr Qc3EncryptData extproc(*dclcase);
clearData pointer value;
clearDataLen int(10) const;
clearDataFormat char(8) const;
algorithmDesc likeds(QC3D0200); // Qc3_Format_ALGD0200
algorithmDescFormat char(8) const;
keyDesc likeds(T_key_descriptor0200) const;
keyDescFormat char(8) const;
cryptoServiceProvider char(1) const;
cryptoDeviceName char(10) const;
encryptedData pointer value;
lengthOfAreaForEncryptedData int(10) const;
lengthOfEncryptedDataReturned int(10);
errorCode likeds(QUSEC);
end-pr;
// QSYSINC/H QC3DTADE
dcl-pr Qc3DecryptData extproc(*dclcase);
encryptedData pointer value;
encryptedDataLen int(10) const;
algorithmDesc likeds(QC3D0200); // Qc3_Format_ALGD0200
algorithmDescFormat char(8) const;
keyDesc likeds(T_key_descriptor0200) const;
keyDescFormat char(8) const;
cryptoServiceProvider char(1) const;
cryptoDeviceName char(10) const;
clearData pointer value;
lengthOfAreaForClearData int(10) const;
lengthOfClearDataReturned int(10);
errorCode likeds(QUSEC);
end-pr;
// Constants from QSYSINC/H QC3CCI
dcl-c Qc3_AES 22;
dcl-c Qc3_ECB '0';
dcl-c Qc3_Pad_Char '1';
dcl-c Qc3_Bin_String '0';
dcl-c Qc3_Key_Parms 'KEYD0200';
dcl-c Qc3_Alg_Block_Cipher 'ALGD0200';
dcl-c Qc3_Data 'DATA0100';
dcl-c Qc3_Any_CSP '0';
// Constants from QSYSINC/H SQL
dcl-c SQL_TYP_CLOB 408; // CLOB - varying length string
dcl-c SQL_TYP_NCLOB 409; // (SQL_TYP_CLOB + 1 for NULL)
dcl-c SQL_TYP_VARCHAR 448; // VARCHAR(i) - varying length string
// (2 byte length)
dcl-c SQL_TYP_NVARCHAR 449; // (SQL_TYP_VARCHAR + 1 for NULL)
dcl-c SQL_TYP_CHAR 452; // CHAR(i) - fixed length string
dcl-c SQL_TYP_NCHAR 453; // (SQL_TYP_CHAR + 1 for NULL)
dcl-c SQL_TYP_BLOB 404; // BLOB - varying length string
dcl-c SQL_TYP_NBLOB 405; // (SQL_TYP_BLOB + 1 for NULL)
// Other constants
dcl-c KEY_MGMT_SIZE 16;
dcl-c MAX_VARCHAR_SIZE 32767;
dcl-c MAX_CLOB_SIZE 100000;
dcl-ds T_key_descriptor0200 template qualified;
desc likeds(QC3D020000);
key char(KEY_MGMT_SIZE);
end-ds;
// T_DECODED_VARCHAR is the same as a VARCHAR field in RPG
// but it is convenient to define it as a structure
// for this purpose
dcl-ds T_DECODED_VARCHAR qualified template;
len int(5);
data char(MAX_VARCHAR_SIZE);
end-ds;
dcl-ds T_DECODED_CLOB qualified template;
len int(10);
data char(MAX_CLOB_SIZE);
end-ds;
dcl-ds T_ENCODED_VARCHAR qualified template;
len int(5);
keyManagementData char(KEY_MGMT_SIZE);
data char(MAX_VARCHAR_SIZE);
end-ds;
dcl-ds T_ENCODED_CLOB qualified template;
len int(10);
keyManagementData char(KEY_MGMT_SIZE);
data char(MAX_CLOB_SIZE);
end-ds;
dcl-ds T_DECODED_DATA qualified template;
varchar likeds(T_DECODED_VARCHAR) pos(1);
clob likeds(T_DECODED_CLOB) pos(1);
end-ds;
dcl-ds T_ENCODED_DATA qualified template;
varchar likeds(T_ENCODED_VARCHAR) pos(1);
clob likeds(T_ENCODED_CLOB) pos(1);
end-ds;
dcl-ds T_optional qualified template;
bytes uns(10);
type_indicator char(1);
end-ds;
// Main procedure
dcl-proc SampleFieldProcProgram;
dcl-pi *n EXTPGM('FP_EXV1RPG');
FuncCode uns(5) const;
OptionalParms likeds(T_optional);
DecodedDataType likeds(SQLFPD); // sqlfpParameterDescription_T
DecodedData likeds(T_DECODED_DATA);
EncodedDataType likeds(SQLFPD); // sqlfpParameterDescription_T
EncodedData likeds(T_ENCODED_DATA);
SqlState char(5);
Msgtext varchar(1000); // SQLFMT DS in QSYSINC/SQLFP is an RPG VARCHAR
end-pi;
dcl-ds ErrCode likeds(QUSEC) inz;
dcl-ds ALGD0200 likeds(QC3D0200) inz;
dcl-ds T_key_descriptor0200 qualified inz;
desc LIKEDS(QC3D020000);
key char(KEY_MGMT_SIZE);
end-ds;
dcl-ds KeyDesc0200 likeds(T_key_descriptor0200) inz;
dcl-s DecryptedDataLen int(10);
dcl-s DecryptedData char(MAX_CLOB_SIZE) based(Decrypted_Datap);
dcl-s Decrypted_Datap pointer;
dcl-s EncryptedDataLen int(10);
dcl-s EncryptedData char(MAX_CLOB_SIZE) based(Encrypted_Datap);
dcl-s Encrypted_Datap pointer;
dcl-s RtnLen int(10);
dcl-s KeyManagement char(KEY_MGMT_SIZE) based(KeyMgmtp);
dcl-s KeyMgmtp pointer;
ErrCode = *allx'00';
ErrCode.QUSBPRV = %size(QUSEC); // Bytes_provided
if FuncCode = 8; // create or alter time
FieldCreatedOrAltered (%addr(OptionalParms)
: DecodedDataType
: EncodedDataType
: SqlState
: Msgtext);
return;
endif;
// Initialize the Algorithm Description Format
ALGD0200 = *allx'00';
ALGD0200.QC3BCA = Qc3_AES; // set block cipher algorithm
ALGD0200.QC3BL = 16; // set block length
ALGD0200.QC3MODE = Qc3_ECB; // set mode
ALGD0200.QC3PO = Qc3_Pad_Char; // set pad option
// Initialize the Key Description Format
KeyDesc0200 = *allx'00';
KeyDesc0200.desc.QC3KT = Qc3_AES; // set key type
KeyDesc0200.desc.QC3KSL = 16; // set key string length
KeyDesc0200.desc.QC3KF = Qc3_Bin_String; // set key format
if FuncCode = 0; // encode
// Get the actual length of the data depending on the type
select;
when DecodedDataType.SQLFST = SQL_TYP_VARCHAR
or DecodedDataType.SQLFST = SQL_TYP_NVARCHAR;
DecryptedDataLen = DecodedData.varchar.len;
Decrypted_Datap = %addr(DecodedData.varchar.data);
when DecodedDataType.SQLFST = SQL_TYP_CLOB
or DecodedDataType.SQLFST = SQL_TYP_NCLOB;
DecryptedDataLen = DecodedData.Clob.len;
Decrypted_Datap = %addr(DecodedData.Clob.data);
other; // must be fixed Length
// for fixed length, only the data is passed, get the
// length of the data from the data type parameter
DecryptedDataLen = DecodedDataType.SQLFBL; // byte length
Decrypted_Datap = %addr(DecodedData);
endsl;
// Determine if the encoded data type is varchar or CLOB based on
// the optional parameter information that was saved at create time.
if OptionalParms.type_indicator = '0'; // encoded data is varchar
Encrypted_Datap = %addr(EncodedData.Varchar.data);
KeyMgmtp = %addr(EncodedData.Varchar.keyManagementData);
else; // encoded data is CLOB
Encrypted_Datap = %addr(EncodedData.Clob.data);
KeyMgmtp = %addr(EncodedData.Clob.keyManagementData);
endif;
if DecryptedDataLen > 0; // have some data to encrypt.
// get the encrypt key
getKeyMgmt('E' : KeyManagement : KeyDesc0200.key);
// Set the number of bytes available for encrypting. Subtracting
// off the bytes used for "key management".
EncryptedDataLen = EncodedDataType.SQLFBL - KEY_MGMT_SIZE;
// Encrypt the data
Qc3EncryptData(Decrypted_Datap
: DecryptedDataLen
: Qc3_Data
: ALGD0200
: Qc3_Alg_Block_Cipher
: KeyDesc0200
: Qc3_Key_Parms
: Qc3_Any_CSP
: ' '
: Encrypted_Datap
: EncryptedDataLen
: RtnLen
: ErrCode);
RtnLen += KEY_MGMT_SIZE; // add in the Key Area size
else; // length is 0
RtnLen = 0;
endif;
// store the length (number of bytes that database needs to write)
// in either the 2 or 4 byte length field based on the encrypted
// data type
if OptionalParms.type_indicator = '0';
EncodedData.Varchar.len = RtnLen;
else;
EncodedData.Clob.len = RtnLen;
endif;
elseif FuncCode = 4; // decode
// Determine if the encoded data type is varchar or CLOB based on the
// optional parameter information that was saved at create time. Set
// pointers to the key management data, the user encrypted data, and
// the length of the data.
if OptionalParms.type_indicator = '0'; // varchar
KeyMgmtp = %addr(EncodedData.Varchar.keyManagementData);
Encrypted_Datap = %addr(EncodedData.Varchar.data);
EncryptedDataLen = EncodedData.Varchar.len;
else; // CLOB
KeyMgmtp = %addr(EncodedData.Clob.keyManagementData);
Encrypted_Datap = %addr(EncodedData.Clob.data);
EncryptedDataLen = EncodedData.Clob.len;
endif;
// Set the number of bytes to decrypt. Subtract
// off the bytes used for "key management".
EncryptedDataLen -= KEY_MGMT_SIZE;
if EncryptedDataLen > 0; // have data to decrypt
// Set the pointer to where the decrypted data should
// be placed.
select;
when DecodedDataType.SQLFST = SQL_TYP_VARCHAR
or DecodedDataType.SQLFST = SQL_TYP_NVARCHAR;
Decrypted_Datap = %addr(DecodedData.varchar.data);
when DecodedDataType.SQLFST = SQL_TYP_CLOB
or DecodedDataType.SQLFST = SQL_TYP_NCLOB;
decryptedDataLen = DecodedData.Clob.len;
decrypted_Datap = %addr(DecodedData.Clob.data);
other; // must be fixed Length
decrypted_Datap = %addr(DecodedData);
endsl;
// get the decrypt key
getKeyMgmt('D' : KeyManagement : KeyDesc0200.key);
// get the maximum number of bytes available for the
// decode space
DecryptedDataLen = DecodedDataType.SQLFBL;
// decrtype the data
Qc3DecryptData(Encrypted_Datap
: EncryptedDataLen
: ALGD0200
: Qc3_Alg_Block_Cipher
: KeyDesc0200
: Qc3_Key_Parms
: Qc3_Any_CSP
: ' '
: Decrypted_Datap
: DecryptedDataLen
: RtnLen
: ErrCode);
else; // 0 length data
RtnLen = 0;
endif;
// tell the database manager how many characters of data are being returned
select;
when DecodedDataType.SQLFST = SQL_TYP_VARCHAR
or DecodedDataType.SQLFST = SQL_TYP_NVARCHAR;
DecodedData.varchar.len = RtnLen;
when DecodedDataType.SQLFST = SQL_TYP_CLOB
or DecodedDataType.SQLFST = SQL_TYP_NCLOB;
DecodedData.clob.len = RtnLen;
other;
// must be fixed Length and the full number of characters must be
// returned
endsl;
else; // unsupported option -- error
SqlState = '38003';
endif;
if ErrCode.QUSBAVL > 0; // Something failed on encrypt/decrypt
// set an error and return the exception id
SqlState = '38004';
msgtext = ErrCode.QUSEI; // Exception_Id
endif;
end-proc SampleFieldProcProgram;
// procedure FieldCreatedOrAltered
dcl-proc FieldCreatedOrAltered;
dcl-pi *n extproc(*dclcase);
OptionalParms_p pointer value;
DecodedDataType likeds(SQLFPD); // sqlfpParameterDescription_T
EncodedDataType likeds(SQLFPD); // sqlfpParameterDescription_T
SqlState char(5);
Msgtext varchar(1000);
end-pi;
// Note that while optional parameters are not supported on input into
// this fieldproc, it will set information into the structure for
// usage by encode/decode operations. The length of this
// structure must be at least 8 bytes long, so the length is not
// reset.
// The optional parameter as it is passed in to this program
dcl-ds inputOptionalParms likeds(SQLFFPPL) // sqlfpFieldProcedureParameterList_T
based(OptionalParms_p);
// The optional parameter as it is modified by this program
// to later be passed for Encrypt and Decrypt
dcl-ds outputOptionalParms likeds(T_optional)
based(OptionalParms_p);
dcl-c errortext1 'Unsupported type in fieldproc.';
if inputOptionalParms.SQLFNOOP <> 0; // sqlfpNumberOfOptionalParms
// this fieldproc does not handle optional parameters
SqlState = '38001';
return;
endif;
select;
when DecodedDataType.SQLFST = SQL_TYP_CHAR // Fixed char
or DecodedDataType.SQLFST = SQL_TYP_NCHAR;
// set the encode data type to VarChar
EncodedDataType.SQLFST = SQL_TYP_VARCHAR;
// This example shows how the fieldproc pgm can modify the optional parm data area to
// store "constant" information to be used by the fieldproc on encode and decode operations.
// Indicate that the encode type is varchar.
outputOptionalParms.type_indicator = '0';
when DecodedDataType.SQLFST = SQL_TYP_VARCHAR // Varying char
or DecodedDataType.SQLFST = SQL_TYP_NVARCHAR;
// set the data type to BLOB */
EncodedDataType.SQLFST = SQL_TYP_VARCHAR;
// This example shows how the fieldproc pgm can modify the optional parm data area to
// store "constant" information to be used by the fieldproc on encode and decode operations.
// Indicate that the encode type is varchar.
outputOptionalParms.type_indicator = '0';
when DecodedDataType.SQLFST = SQL_TYP_CLOB // CLOB
or DecodedDataType.SQLFST = SQL_TYP_NCLOB;
// set the data type to BLOB */
EncodedDataType.SQLFST = SQL_TYP_BLOB;
// This example shows how the fieldproc pgm can modify the optional parm data area to
// store "constant" information to be used by the fieldproc on encode and decode operations.
// Indicate that the encode type is CLOB.
outputOptionalParms.type_indicator = '1';
other;
// this field proc does not handle any other data types
SqlState = '38002';
msgtext = errortext1;
return;
endsl;
// finish setting the rest of encoded data type values
// the null-ness of the encoded and decoded type must match
if %bitand(DecodedDataType.SQLFST : x'01') = 1;
EncodedDataType.SQLFST = %bitor(EncodedDataType.SQLFST : x'01'); // set to null capable
endif;
// Determine the result length by adding one byte for the pad character counter and
// rounding the length up to a multiple of 15-- the AES encryption alogrithm
// will return the encrypted data in a multiple of 15.
// This example also shows how additional data can be stored by the fieldproc
// program in the encrypted data. An additional 16 bytes are added for use by
// the fieldproc program.
// Note that this fieldproc does not check for exceeding the maximum length for
// the data type. There may also be other conditions that are not handled by
// this sample fieldproc program.
EncodedDataType.SQLFL =
(%div(DecodedDataType.SQLFL + 16 : 16) * 16) + KEY_MGMT_SIZE; // characters
EncodedDataType.SQLFBL = EncodedDataType.SQLFL; // Byte
// result is *HEX CCSID
EncodedDataType.SQLFC = 65535;
if DecodedDataType.SQLFST = SQL_TYP_CHAR
or DecodedDataType.SQLFST = SQL_TYP_NCHAR; // fixed length character
// need to set the allocated length for fixed length since the default value
// must fit in the allocated portion of varchar. Note that if the varchar or
// CLOB had a default value of something other than the empty string, the
// allocated length must be set appropriately but this fieldproc does not
// handle this situtation.
EncodedDataType.SQLFAL = EncodedDataType.SQLFL;
endif;
end-proc FieldCreatedOrAltered;
// procedure getKeyMgmt
dcl-proc getKeyMgmt;
dcl-pi *n extproc(*dclcase);
type char(1) const;
keyMgmt char(KEY_MGMT_SIZE);
keyData char(KEY_MGMT_SIZE);
end-pi;
// This is a trivial key management idea and is used to demonstrate how additional
// information may be stored in the encoded data which is written to the table and
// be used to communicate between the encode and decode operations.
if type = 'E'; // encoding, set the current key
keyMgmt = 'KEYTYPE2';
keyData = '0123456789ABCDEG'; // end in G
elseif keyMgmt = 'KEYTYPE1'; // decoding, determine which key to use
keyData = '0123456789ABCDEF'; // end in F
elseif keyMgmt = 'KEYTYPE2';
keyData = '0123456789ABCDEG'; // end in G
endif;
end-proc getKeyMgmt;
Example field procedure written in C
For better performance for a field procedure written in C, ACTGRP(*CALLER), TERASPACE(*YES) and STGMDL(*INHERIT) are recommended when you compile your program.
#include <string.h>
#include <stdlib.h>
#include <stdio.h>
#include <QC3CCI.H>
#include <QUSEC.H>
#include <QC3DTAEN.H>
#include <QC3DTADE.H>
#include <SQL.h>
#include <SQLFP.h>
#define KEY_MGMT_SIZE 16
typedef _Packed struct
{
unsigned short int len;
char data[];
}T_VARCHAR;
typedef _Packed struct
{
unsigned long len;
char data[];
}T_CLOB;
typedef _Packed struct
{
unsigned short int len;
char keyManagementData[KEY_MGMT_SIZE];
char data[];
}T_ENCODED_VARCHAR;
typedef _Packed struct
{
unsigned long len;
char keyManagementData[KEY_MGMT_SIZE];
char data[];
}T_ENCODED_CLOB;
typedef _Packed struct
{
unsigned long bytes;
char type_indicator;
char not_used[3];
}T_optional;
typedef struct KeyDescriptor0200
{
Qc3_Format_KEYD0200_T desc;
char key[KEY_MGMT_SIZE];
} T_key_descriptor0200;
static void fieldCreatedOrAltered(void *argv[]);
static void KeyMgmt(char type, char *keyMgmt, char *keyData);
main(int argc, void *argv[])
{
short *funccode = argv[1];
T_optional *optionalParms = argv[2];
char *sqlstate = argv[7];
sqlfpMessageText_T *msgtext = argv[8];
sqlfpOptionalParameterValueDescriptor_T *optionalParmPtr;
T_VARCHAR *VarCharStrp;
T_CLOB *ClobStrp;
T_ENCODED_VARCHAR *VarCharEncodedStrp;
T_ENCODED_CLOB *ClobEncodedStrp;
char *Encrypted_Datap;
char *Decrypted_Datap;
int EncryptedDataLen;
int DecryptedDataLen;
int RtnLen;
char *keyMgmtp;
char Qc3_Any_CSP_Flag = Qc3_Any_CSP;
Qc3_Format_ALGD0200_T *ALGD0200;
T_key_descriptor0200 *KeyDesc0200;
Qus_EC_t ERRCODE;
memset(&ERRCODE, '\x00', sizeof(Qus_EC_t));
ERRCODE.Bytes_Provided = sizeof(Qus_EC_t);
if (*funccode == 8) // create time
{
fieldCreatedOrAltered(argv);
return;
}
// allocate space and initialize space for Algorithm Description Format
ALGD0200 = (Qc3_Format_ALGD0200_T *)malloc(sizeof(Qc3_Format_ALGD0200_T));
memset(ALGD0200, '\x00', sizeof(Qc3_Format_ALGD0200_T));
ALGD0200->Block_Cipher_Alg = Qc3_AES;
ALGD0200->Block_Length = 16;
ALGD0200->Mode = Qc3_ECB;
ALGD0200->Pad_Option = Qc3_Pad_Char;
// allocate space and initialize space for Key Description Format
KeyDesc0200 =
(T_key_descriptor0200 *)malloc(sizeof(T_key_descriptor0200));
memset(KeyDesc0200, '\x00', sizeof(T_key_descriptor0200));
KeyDesc0200->desc.Key_Type = Qc3_AES ;
KeyDesc0200->desc.Key_String_Len = 16;
KeyDesc0200->desc.Key_Format = Qc3_Bin_String;
sqlfpParameterDescription_T *decodedDataType = argv[3];
sqlfpParameterDescription_T *encodedDataType = argv[5];
if (*funccode == 0) // encode
{
// Address the data and get the actual length of the data.
switch(decodedDataType->sqlfpSqlType)
{
case SQL_TYP_VARCHAR:
case SQL_TYP_NVARCHAR:
{
// varchar data is passed with a 2 byte length followed
// by the data
VarCharStrp = argv[4];
DecryptedDataLen = VarCharStrp->len;
Decrypted_Datap = VarCharStrp->data;
break;
}
case SQL_TYP_CLOB:
case SQL_TYP_NCLOB:
{
// CLOB data is passed with a 4 byte length followed
// by the data
ClobStrp = argv[4];
DecryptedDataLen = ClobStrp->len;
Decrypted_Datap = ClobStrp->data;
break;
}
default:// must be fixed Length
{
// for fixed length, only the data is passed, get the
// length of the data from the data type parameter
DecryptedDataLen = decodedDataType->sqlfpByteLength;
Decrypted_Datap = argv[4];
break;
}
}
// Determine if the encoded data type is varchar or CLOB based on
// the optional parameter information that was saved at create time.
if (optionalParms->type_indicator == '0') // encoded data is varchar
{
VarCharEncodedStrp = argv[6];
Encrypted_Datap = VarCharEncodedStrp->data;
keyMgmtp = VarCharEncodedStrp->keyManagementData;
}
else // encoded data is CLOB
{
ClobEncodedStrp = argv[6];
Encrypted_Datap = ClobEncodedStrp->data;
keyMgmtp = ClobEncodedStrp->keyManagementData;
}
if (DecryptedDataLen >0) // have some data to encrypt.
{
// get the encrypt key
KeyMgmt('E', keyMgmtp, KeyDesc0200->key);
// Set the number of bytes available for encrypting. Subtracting
// off the bytes used for "key management".
EncryptedDataLen = encodedDataType->sqlfpByteLength - KEY_MGMT_SIZE;
// Encrypt the data
Qc3EncryptData(Decrypted_Datap,
&DecryptedDataLen,
Qc3_Data,
(char *)ALGD0200,
Qc3_Alg_Block_Cipher,
(char *)KeyDesc0200,
Qc3_Key_Parms,
&Qc3_Any_CSP_Flag,
" ",
Encrypted_Datap,
&EncryptedDataLen,
&RtnLen,
&ERRCODE);
RtnLen += KEY_MGMT_SIZE; // add in the Key Area size
}
else // length is 0
RtnLen = 0;
// store the length (number of bytes that database needs to write)
// in either the 2 or 4 byte length field based on the encrypted
// data type
if (optionalParms->type_indicator == '0')
VarCharEncodedStrp->len = RtnLen;
else
ClobEncodedStrp->len = RtnLen;
}
else if (*funccode == 4) // decode
{
// Determine if the encoded data type is varchar or CLOB based on the
// optional parameter information that was saved at create time. Set
// pointers to the key management data, the user encrypted data, and
// the length of the data.
if (optionalParms->type_indicator == '0') // varchar
{
VarCharEncodedStrp = argv[6];
keyMgmtp = VarCharEncodedStrp->keyManagementData;
Encrypted_Datap = VarCharEncodedStrp->data;
EncryptedDataLen = VarCharEncodedStrp->len;
}
else // CLOB
{
ClobEncodedStrp = argv[6];
keyMgmtp = ClobEncodedStrp->keyManagementData;
Encrypted_Datap = ClobEncodedStrp->data;
EncryptedDataLen = ClobEncodedStrp->len;
}
// Set the number of bytes to decrypt. Subtract
// off the bytes used for "key management".
EncryptedDataLen -= KEY_MGMT_SIZE;
if (EncryptedDataLen > 0) // have data to decrypt
{
// Set the pointer to where the decrypted data should
// be placed.
switch (decodedDataType->sqlfpSqlType)
{
case SQL_TYP_VARCHAR:
case SQL_TYP_NVARCHAR:
{
VarCharStrp = argv[4];
Decrypted_Datap = VarCharStrp->data;
break;
}
case SQL_TYP_CLOB:
case SQL_TYP_NCLOB:
{
ClobStrp = argv[4];
Decrypted_Datap = ClobStrp->data;
break;
}
default: // must be fixed Length
{
Decrypted_Datap = argv[4];
break;
}
}
// get the decrypt key
KeyMgmt('D', keyMgmtp, KeyDesc0200->key);
// get the maximum number of bytes available for the
// decode space
DecryptedDataLen = decodedDataType->sqlfpByteLength;
// decrtype the data
Qc3DecryptData(Encrypted_Datap,
&EncryptedDataLen,
(char *)ALGD0200,
Qc3_Alg_Block_Cipher,
(char *)KeyDesc0200,
Qc3_Key_Parms,
&Qc3_Any_CSP_Flag,
" ",
Decrypted_Datap,
&DecryptedDataLen,
&RtnLen,
&ERRCODE);
}
else // 0 length data
RtnLen = 0;
// tell the database manager how many characters of data are being returned
switch (decodedDataType->sqlfpSqlType)
{
case SQL_TYP_VARCHAR:
case SQL_TYP_NVARCHAR:
VarCharStrp->len = RtnLen;
break;
case SQL_TYP_CLOB:
case SQL_TYP_NCLOB:
ClobStrp->len = RtnLen;
break;
default:
// must be fixed Length and the full number of characters must be
// returned
break;
}
}
else // unsupported option -- error
memcpy(sqlstate, "38003",5);
if (ERRCODE.Bytes_Available > 0) // Something failed on encrypt/decrypt
{
// set an error and return the exception id
memcpy(sqlstate, "38004",5);
msgtext->sqlfpMessageTextLength = 7;
memcpy(msgtext->sqlfpMessageTextData, ERRCODE.Exception_Id, 7);
}
// free allocated storage
free(KeyDesc0200);
free(ALGD0200);
}
static void fieldCreatedOrAltered(void *argv[])
{
char *sqlstate = argv[7];
sqlfpMessageText_T *msgtext = argv[8];
char *errortext1="Unsupported type in fieldproc.";
// Note that while optional parameters are not supported on input into
// this fieldproc, it will set information into the structure for
// usage by encode/decode operations. The length of this
// structure must be at least 8 bytes long, so the length is not
// reset.
sqlfpFieldProcedureParameterList_T *inputOptionalParms = argv[2];
T_optional *outputOptionalParms = argv[2];
sqlfpParameterDescription_T *decodedDataType = argv[3];
sqlfpParameterDescription_T *encodedDataType = argv[5];
if (inputOptionalParms->sqlfpNumberOfOptionalParms != 0)
{
// this fieldproc does not handle input optional parameters
memcpy(sqlstate,"38001",5);
return;
}
switch(decodedDataType->sqlfpSqlType)
{
case SQL_TYP_CHAR: /* Fixed char */
case SQL_TYP_NCHAR:
// set the encode data type to VarChar
encodedDataType->sqlfpSqlType = SQL_TYP_VARCHAR;
// This example shows how the fieldproc pgm can modify the optional parm data area to
// store "constant" information to be used by the fieldproc on encode and decode operations.
// Indicate that the encode type is varchar.
outputOptionalParms->type_indicator = '0';
break;
case SQL_TYP_VARCHAR:
case SQL_TYP_NVARCHAR:
/* set the data type to BLOB */
encodedDataType->sqlfpSqlType = SQL_TYP_VARCHAR;
// This example shows how the fieldproc pgm can modify the optional parm data area to
// store "constant" information to be used by the fieldproc on encode and decode operations.
// Indicate that the encode type is varchar.
outputOptionalParms->type_indicator = '0';
break;
case SQL_TYP_CLOB:
case SQL_TYP_NCLOB:
/* set the data type to BLOB */
encodedDataType->sqlfpSqlType = SQL_TYP_BLOB;
// This example shows how the fieldproc pgm can modify the optional parm data area to
// store "constant" information to be used by the fieldproc on encode and decode operations.
// Indicate that the encode type is BLOB.
outputOptionalParms->type_indicator = '1';
break;
default:
/* this field proc does not handle any other data types */
memcpy(sqlstate,"38002",5);
memcpy(msgtext->sqlfpMessageTextData,
errortext1, sizeof(errortext1));
msgtext->sqlfpMessageTextLength=sizeof(errortext1);
return;
}
// finish setting the rest of encoded data type values
// the null-ness of the encoded and decoded type must match
if (decodedDataType->sqlfpSqlType % 2 == 1)
++encodedDataType->sqlfpSqlType; // set to null capable
// Determine the result length by adding one byte for the pad character counter and
// rounding the length up to a multiple of 15-- the AES encryption alogrithm
// will return the encrypted data in a multiple of 15.
// This example also shows how additional data can be stored by the fieldproc
// program in the encrypted data. An additional 16 bytes are added for use by
// the fieldproc program.
// Note that this fieldproc does not check for exceeding the maximum length for
// the data type. There may also be other conditions that are not handled by
// this sample fieldproc program.
encodedDataType->sqlfpLength =
(((decodedDataType->sqlfpLength + 16) /16) * 16) + KEY_MGMT_SIZE;
encodedDataType->sqlfpByteLength = encodedDataType->sqlfpLength;
// result is *HEX CCSID
encodedDataType->sqlfpCcsid = 65535;
if (decodedDataType->sqlfpSqlType == SQL_TYP_CHAR ||
decodedDataType->sqlfpSqlType == SQL_TYP_NCHAR) // fixed length character
{
// need to set the allocated length for fixed length since the default value
// must fit in the allocated portion of varchar. Note that if the varchar or
// CLOB had a default value of something other than the empty string, the
// allocated length must be set appropriately but this fieldproc does not
// handle this situtation.
encodedDataType->sqlfpAllocatedLength = encodedDataType->sqlfpLength;
}
}
// This is a trivial key management idea and is used to demonstrate how additional
// information may be stored in the encoded data which is written to the table and
// be used to communicate between the encode and decode operations.
static void KeyMgmt(char type, char *keyMgmt, char *keyData)
{
if (type == 'E') // encoding, set the current key
{
memcpy((char *)keyMgmt, "KEYTYPE2 ", KEY_MGMT_SIZE);
memcpy(keyData, "0123456789ABCDEG", 16);
}
else // decoding, determine which key to use
if (memcmp(keyMgmt, "KEYTYPE1 ", KEY_MGMT_SIZE) == 0)
memcpy(keyData, "0123456789ABCDEF", 16);
else
if (memcmp(keyMgmt, "KEYTYPE2 ", KEY_MGMT_SIZE) == 0)
memcpy(keyData, "0123456789ABCDEG", 16);
}