フィールド・プロシージャー・プログラムの例
フィールド・プロシージャー FP1 を列 C1 に追加します。 フィールド・プロシージャー FP1 には、フィールド・プロシージャーが操作の対象とする列のバイト数を示す 1 つのパラメーターがあります。
ALTER TABLE TESTTAB ALTER C1 SET FIELDPROC FP1(10)以下に示す 2 つのフィールド・プロシージャー・プログラムは、最初は ILE RPG で、次に C で、同じ操作を行います。 これらのプログラムは、フィールド・プロシージャー・プログラムで IBM i Cryptographic Services API を使用して、固定長文字、可変長文字、または文字 LOB データの暗号化と暗号化解除を行う方法を示しています。
プログラムは、フィールド・プロシージャー・プログラムができることの一部を示していますが、
あらゆるシチュエーションおよびエラーを処理しようとはしていません。以下の項目がどのように処理されているのかに注意してください。
- デコードされたデータ・タイプからエンコードされたデータ・タイプへの変更
- 固定長文字、可変長文字、および CLOB データに必要なアドレッシングの相違点
- オプションのパラメーター構造の使用法 (sqlfpOptionalParameterValueDescriptor_T)
IBM i Cryptographic Services API について詳しくは、 Cryptographic Services API を参照してください。
ILE RPG で書かれたフィールド・プロシージャーの例
ILE RPG で書かれたフィールド・プロシージャーでは、以下の項目を考慮する必要があります。
- パフォーマンスをよくするため、プログラムをコンパイルするときに ACTGRP(*CALLER) および STGMDL(*INHERIT) が推奨されます。この例では、 制御ステートメント中にこれらのキーワードがあります (H 仕様)。
- 制御ステートメントに THREAD キーワードを指定する必要があります。 この例では、THREAD(*CONCURRENT) が指定されていますが、THREAD(*SERIALIZE) を使用することもできます。
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;
C で書かれたフィールド・プロシージャーの例
C で書かれたフィールド・プロシージャーのパフォーマンスをよくするため、 プログラムをコンパイルするときに、ACTGRP(*CALLER)、TERASPACE(*YES)、および STGMDL(*INHERIT) が推奨されます。
#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);
}