iSeries – AES field level encryption.

155 pts.
Tags:
AS/400
IBM iSeries
iSeries V5R4MO
I have a need to encrypt 1 field in a file that I'm sending outside the company. I know the file level encryption, but have never dealt with Field level. Is it possible to have AES field level? Possible without purchasing a package? iSeries V5R4M0. Any Help would be greatly appreciated!

Software/Hardware used:
iseries V5R4M0.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Discuss This Question: 30  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • BigKat
    you could use SQL to create a "work" file with that field encrypted and then send that file to the recipient
    8,350 pointsBadges:
    report
  • dpa
    that's my goal...  my question is how do i encrypt that field?  the recipient requested AES encryption on that field, and that's what i do not know how to do.  :)
    155 pointsBadges:
    report
  • TomLiotta
    I have a need to encrypt 1 field in a file that I'm sending outside the company.   Please define "sending outside". If it must be encrypted, why send the field at all? How will it be sent? What will the transfer format be? How will the recipient use the encrypted field?   Tom
    125,585 pointsBadges:
    report
  • dpa
    i'll be sending a "work" file to a vendor with this encrypted tag-along field.  once that vendor does what they need to do, they are going to append informatin, and send it to second location.  it's the second location that needs the encrypted field.  i've tried to talk the project mgr's into alternate methods, but no go...  so, is this possible?  the system decrypting said that an AES solution would be best...  Help (if possible).
    155 pointsBadges:
    report
  • TomLiotta
    The encryption is easy enough; no additional packages needed. But that's the easy part. The hard part is going to be getting the file sent while including an encrypted column. That doesn't make a lot of sense. So, how is it going to be sent? What form or format will it be in? (And how is an encrypted value going to make any sense at all to a recipient? How will they know it's not just a random value?) -- Tom
    125,585 pointsBadges:
    report
  • dpa
    the file will be sent via SFTP to the first vendor.  i'm not sure how it's getting to the 2nd vendor.  i'm assuming i share a key to the field with the 2nd vendor, and then we'd test that out to make sure they get the correct decrypt.  the file will be just a text file, and this column with be a 30-character text field.
    155 pointsBadges:
    report
  • TomLiotta
    the file will be just a text file, and this column with be a 30-character text field.   Can you clarify that a little more? If it's a "text file", does that mean it will be a streamfile, e.g., a .CSV export or something similar? Or will it be a non-delimited fixed-format text file? How will the "field" be determined within a record? By offset and length? Is the 30-char size before or after encryption?   Tom
    125,585 pointsBadges:
    report
  • dpa
    it will be a file that will contain several fields, the fields will be either pipe or tab delimited... 
    155 pointsBadges:
    report
  • TomLiotta
    How will the encrypted characters be differentiated from delimiters? And it should be clear that there shouldn't be anything like ASCII/EBCDIC conversion allowed during transfer. -- Tom
    125,585 pointsBadges:
    report
  • dpa
    "How will the encrypted characters be differentiated from delimiters? And it should be clear that there shouldn’t be anything like ASCII/EBCDIC conversion allowed during transfer" - i don't know. i've never done this before?  would a tab (or pipe) be part of the encrypted characters?  the data within the field will be 0-9, A-Z, so no special characters.  the data will start on my as400, and then the two receiving boxes are UNIX or LINUX...
    155 pointsBadges:
    report
  • dpa
    Tom:  a little more information (just found out).  the field that i'll be encrypting is going to be going in an email out to clients, to provide them a link to click, and when they get to the website, the website will decrypt, and know which client was on the link..  does that make sense?  is that do-able?
    155 pointsBadges:
    report
  • TomLiotta
    would a tab (or pipe) be part of the encrypted characters?   Basic random distribution should result in a delimiter character showing up in one out of every 256 characters. The encrypted values will have effectively random bytes. Not truly random, of course, but it would take sophisticated code to verify that they weren't random. That's why encryption works.   ...then the two receiving boxes are UNIX or LINUX…   The characteristics of the characters is also why you can't allow an encoding change from EBCDIC to ASCII. You'll need to convert your file data to ASCII (or a Unicode CCSID) first, then encrypt your field, and then do the transfer in binary mode. The same transfer mode will need to be done between the other two boxes. The encrypted value is essentially going to be a binary field.   the field that i’ll be encrypting is going to be going in an email out to clients, to provide them a link to click...   It can't work exactly like that. (Well, I suppose it could if the encrypted value is URL-encoded, but that's going to result in a URL that's maybe a couple hundred characters long.) More likely the encypted value would be base64 encoded, but even that will give a pretty long URL.   The AES encryption will just require some programming modules you can copy from a couple places on the internet. If you don't want to use ILE RPG, I can supply COBOL or even ILE CL to do it. (C is available, too.)   But shipping the file with encrypted values across multiple systems is going to take some agreement among all participating systems. The bits in the value can't be even slightly disturbed.   Best might be to build a file effectively in the same form as will be used on the final target system. There probably won't be any delimiters; it'll need to be fixed-length columns. Then zip it up and send it; that should help handle the binary nature of this. If the intermediate system alters the file, they'll have to do whatever is needed to maintain its integrity.   Think about this, and find out what the other two systems will need for encoding. That's where the real effort is going to be. Post back with whatever gets agreed upon.   Tom
    125,585 pointsBadges:
    report
  • dpa
    the email will come from the 1 vendor (i thought they said 2, but the 2nd outbound was the email)...so, just 1 vendor to worry about.  that was my bad (mis understanding).  this is the example of what they're looking for (they sent to me):  (We would want any symmetric encryption with passphrase. Example AES. http://www.everpassword.com/aes-encryptor) to me, it looks like something like this wouldn't have a <tab> in the string, doesn't look binary.  so, my 20-char field would encrypt to a much larger field, which is no problem.  i'm an rpg programmer (since 1989). this field level encryption is a very different concept to me.  would it be easier to email directly? (darndt@hrblock.com) i truly appreciate the time and effort!  i hope we can get something going!  i hope i can repay you at some point! -dave.
    155 pointsBadges:
    report
  • TomLiotta
    Results that I see at everpassword.com look to me to be base64 encoded, which they would need to be to be displayed on a web page. Judging from the size difference, I'd guess that the values are encrypted via AES, then base64 encoded. (See the 'Table 1: The Base 64 Alphabet' section in RFC 4648 to compare the 'alphabet' to the results.)   And after more thought, the string wouldn't be as long as I was thinking. I was running tests against a 64-byte block "with pad". You'd end up with a 64-byte encrypted and encoded string at the end of the URL.Not too bad, I suppose.   You'd still need to go through the same transfer process though if you want the AES encryption -- convert your data to appropriate encoding, then encrypt the field, then send fixed-length columns in a binary mode (or a "zip" kind of format). The recipient would probably need to do the base64 encode/decode, so you won't care about that.   What exactly is in your 30 (or 20?) character string? Why does it need to be encrypted? So far, I can't quite see a need. Why not just use a basic coded/unique ID, e.g., a UUID? How does encryption help? Some issues would almost disappear if it was dropped.   Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    Note that direct e-mail isn't appropriate. The entire conversation needs to be open to all members. By taking it off-line, it becomes more of a "project" that you're probably not looking for. This is a volunteer site for everyone's benefit, so it should remain an open dialog. And BTW, one good starting point for the encryption is Simplify Encryption with the Use of RPG Reusable Procedures. That might let you see actual encrypt/decrypt results. -- Tom
    125,585 pointsBadges:
    report
  • dpa
    makes sense about keeping this on the forum. the data will be a 4 to 12digit (internal only) customer id concatenated with a date.  we do have a UID (32-char), but the ending system that the client will get to via clicking the link does NOT have the UID.  the UID is unique to a transaction, whereas the customerid is unique to the customer...  so, we need to get the customerid to them.  my argument to not do this was that the customerid is not trace-able back to any identifying information, unless someone hacked our production systems, and found that field in the databases.  other than being able to do that, it's just a string of numbers, no ssn, no nothing...  but, our legal dept said we could not send this particular field unencrypted...  so, the field would be:  123456789101120121108...    this would need to be a batch process, sending 100k-200k records per night.
    155 pointsBadges:
    report
  • dpa
    makes sense about keeping this on the forum. the data will be a 4 to 12digit (internal only) customer id concatenated with a date.  we do have a UID (32-char), but the ending system that the client will get to via clicking the link does NOT have the UID.  the UID is unique to a transaction, whereas the customerid is unique to the customer...  so, we need to get the customerid to them.  my argument to not do this was that the customerid is not trace-able back to any identifying information, unless someone hacked our production systems, and found that field in the databases.  other than being able to do that, it's just a string of numbers, no ssn, no nothing...  but, our legal dept said we could not send this particular field unencrypted...  so, the field would be:  123456789101120121108...    this would need to be a batch process, sending 100k-500k records per night.
    155 pointsBadges:
    report
  • BigKat
    sorry, was laid up for a few days with Migrain headaches.  You may have this already figured out but I was referring to the use of the ENCRYPT_AES SQL function to build the data in your work file and the DECRYPT_CHAR function to read the encrypted data from the work file ... select 'ABC123', decrypt_char(encrypt_aes('ABC123','AnyPasswordString'), 'AnyPasswordString') from SYSIBM/SYSDUMMY1 Constant value DECRYPT_CHAR ABC123 ABC123  
    8,350 pointsBadges:
    report
  • BigKat
    select 'ABC123', decrypt_char(encrypt_aes('ABC123','AnyPasswordString'), 'AnyPasswordString') from SYSIBM/SYSDUMMY1 Constant value DECRYPT_CHAR ABC123 ABC123
    8,350 pointsBadges:
    report
  • dpa
    sorry about the migrans bigkat, those aren't fun. i tried the sql (manipulated with my test data), but i get the "encrypt_aes" not found...?  we are on release 5.4... do i need to be on a different release?  or, are those functions part of an add-on function of some sort? 
    155 pointsBadges:
    report
  • BigKat
    hmmm, at v5r4 you only have encrypt_rc2 and encrypt_tdes functions available
    8,350 pointsBadges:
    report
  • BigKat
    encrypt_aes is in 6.1+
    8,350 pointsBadges:
    report
  • dpa
    drat.  for encrypt_rc2, and tdes, is the syntax the same?  i'm going to research those, to see if one of those would be good.  i tested on my sql, and i get a "query cannot be run" error..  but, if the sytax is the same, i'll research that issue.  
    155 pointsBadges:
    report
  • TomLiotta
    The SQL functions wouldn't seem useful since this isn't a database encryption operation. I don't know that I've ever heard of anyone doing what's in this question before. This is a situation where the database itself is unencrypted but the retrieved result is encrypted, and that's the opposite of what would usually be done.   I've been unable to grasp why any encryption at all needs to be done on the sending side if the recipient is simply going to be doing the decrypting. It's not at all clear why the recipient doesn't just do the encryption if it's needed. If "Legal" says it has to be done, there's (probably) not much choice. But I'd be demanding a clear explanation from them if it was me. Once it's in the hands of the recipient, there's not much point to any requirements from "Legal". That's the way it goes, though.   Tom
    125,585 pointsBadges:
    report
  • dpa
    so, the net result that i'm looking for...  i need to get a key piece of data from my system, to an email provider, and they're going to put that data in an email going out to clients.  the data will allow another system at our company to know which client link was clicked from the email.  so, if my mom was a client of ours, we want to send her an email with a link she can click to get a prize from our company.  so, i need to send my mom's key field to our email vendor, so they can put that in the link that my mom will click.  when she clicks it, our backend system will know who it is, and allow her to log on.  that's it in a nutshell.  the encrypted field would be on the link in the email... that's the goal.
    155 pointsBadges:
    report
  • TomLiotta
    when she clicks it, our backend system will know who it is, and allow her to log on.  that’s it in a nutshell.  the encrypted field would be on the link in the email…   As already mentioned, it's pretty it won't work exactly that way. The encrypted value is not going to be clickable (unless it's URL-encoded which will be messy). The clickable value can be a base64-encoded version of the encrypted value, but the encrypted value itself contains characters that are not valid in a URL.   I can't see any way to correctly get an image into this editor, so you might open this image link in a second window and refer to it. (I'll test that after I post this comment.)   The intended image shows a dump (COBOL) of an AES-encrypted value. The clear-text value is just "123456-2012-11-11". That's a 6-digit number with a date stuck on the end. I ran the encryption with a 30-char length this time. The WS-CIPHER-DATA value is the result of AES encryption using x'0123456789ABCDEF0123456789ABCDEF' as the key.   You can review the resulting encrypted string in the image to get an idea what you're going to get. In order to get such a string into a URL, the encrypted string needs to be base64 encoded to fit the example you gave earlier or encoded in some other acceptable way.   So as already stated, your first step has to be to determine which fields from your table will be sent to the recipient. Next step will be to retrieve those columns and store them in the CCSID that the recipient needs. The form that data will be stored in will probably be a streamfile. Then, read that file and encrypt the field that needs encryption, doing it in the appropriate CCSID, into a new file (streamfile). Then that final file can be sent to the recipient in a binary transfer mode.   You might eliminate the binary transfer by doing the base64 encoding on your system (or perhaps by zipping the file). I use "base64 encoding" to stand for whatever encoding method will work to avoid character code conversions during transfers. You might choose a different encoding, but base64 is a widely understand one. No conversion during transfers can be allowed for encrypted data. Transfers will be between your system and the initial recipient, then to an e-mail server that will send to another e-mail server. Then to an e-mail client on some unpredictable platform. Then out of the client through a browser into some web server. Then somehow out of the web server into your database server (with possible intermediate/middleware steps).   The encrypted value cannot be used for much of this, so it must be encoded. Of course, it also must later be decoded in order to be decrypted. Note that the decoded value will be the encrypted value, and the encrypted value has to be carefully handled to avoid character-set conversions on its way back exactly the same as is needed on the original outbound side.   Now, your latest comment refers to your back-end system. Is this the same system that holds your original file? How is the 'click' going to be communicated to that system? Will your back-end system also be running the web server? Once clicked, what data exactly is going to be communicated to what systems? What server will be responding to the click? What will it receive, and how does anything from there get into your "back-end system"?   Simply put, encrypted values cannot simply be sent around system to system. Very specific protocols must be followed in order to maintain data integrity. You must adhere precisely to the needed CCSIDs at every point of transfer. Otherwise the result will be gibberish. This is more important with encryption than it is for easier things like simple CCSID conversions (e.g., EBCDIC/ASCII or different EBCDIC character sets or even "convert CCSID 65535"). A messed up bit or byte in a code conversion might only result in misspelled words or in strange characters like asterisks in place of vertical bars ('pipe' characters) or similar visible oddities. But a single incorrect bit in an ecrypted value will result in a totally different undecipherable value.   The previous link that I supplied shows RPG code that can be compiled and tested on your system. With that code, you can send a single simple encrypted word or phrase to the initial recipient to determine what's needed for a transfer to work. If the value that is received can be decrypted by the recipient back to the correct initial value, then you know that that single part of the whole process will work.   Take it a step at a time.   Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    I've added another comment, but we'll have to wait for it to show up. -- Tom
    125,585 pointsBadges:
    report
  • BigKat
    The SQL ENCRYPT_xxx functions are built-in scalar field functions.  So they return a value for the field in each row in the result set of the select.  That result set may be used programatically in a cursor.  It could be written to a file in a QMQRY. 
    8,350 pointsBadges:
    report
  • dpa
    thanks BigKat..  i can get the _rc2 thing to work, and that is working to encrypt.  but, now i see what Tom is saying about the binary characters...  (sorry, it took me long on that one).  so, last question, and then you two can drop me like a lead balloon...  is there a way to get the binary data to a base64 format in the select statement, or via an RPG program?
    155 pointsBadges:
    report
  • TomLiotta
    It could be written to a file in a QMQRY.   To what end? That is, once written to a file, you need to read that file in order to get the value back and to do something with it. You might as well just use the value directly from the SELECT.   But you're right. Since the function allows a password to be an argument, it should work to get hold of its result. Then you just have to determine what to do with the resulting value without losing its integrity. I haven't seen a working example that would fit with the current scenario, but it's plausible.   Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following