Wed, Jul
0 New Articles

Create an MD5 Hash Using RPG and SQL

  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

Using an MD5 hash offers potential integration, performance, and security benefits.


When developing modern applications or enhancing legacy ones, an RPG or DB2 routine may need to apply an MD5 hash to application data. Fortunately, IBM i/OS offers a machine interface operation called CIPHER to make the task of implementing MD5 relatively simple.

What Is MD5, Anyway?

To answer this question, I'll just be lazy and quote from Wikipedia:


In cryptography, MD5 (Message-Digest algorithm 5) is a widely used cryptographic hash function with a 128-bit hash value. Specified in RFC 1321, MD5 has been employed in a wide variety of security applications, and is also commonly used to check the integrity of files.… An MD5 hash is typically expressed as a 32-digit hexadecimal number.


If you're unfamiliar with cryptography, your next question may be, "What is a cryptographic hash function?" Again Wikipedia gives a good intro:


A cryptographic hash function is a deterministic procedure that takes an arbitrary block of data and returns a fixed-size bit string, the (cryptographic) hash value, such that an accidental or intentional change to the data will change the hash value. The data to be encoded is often called the "message", and the hash value is sometimes called the message digest or simply digest.


The ideal cryptographic hash function has four main or significant properties:

  • it is easy to compute the hash value for any given message,
  • it is infeasible to find a message that has a given hash,
  • it is infeasible to modify a message without hash being changed,
  • it is infeasible to find two different messages with the same hash.

OK, so putting it all together, an MD5 hash is an algorithm that operates on a block of data and comes up with a unique 16-byte (128-bit) "signature" or "hash" that uniquely identifies the data. For the record, cryptographic algorithms are often referred to as "ciphers."

Hash vs. Encryption

It's important to note that a hash is different from encryption. A hash algorithm will simply come up with a unique identifier (i.e., hash) for the data; it does not alter the data itself. Further, the original data cannot be derived from its hash; hence, it is a "one-way" algorithm.


In contrast, encryption transforms data intended to be protected into an illegible format. Ideally, data that has been encrypted can't be decrypted into its original state without knowing the original algorithm and encryption key.


Generally, hash algorithms are used to protect or verify data integrity (such as verifying a zip file from a public FTP site hasn't been tampered with), whereas encryption is intended to scramble data for storage and later decryption and retrieval (such as a credit card or Social Security number).

Programming with MD5

Many languages and database systems have the ability to calculate an MD5 hash function (usually via an API), including the .NET family of languages (C#,VB, etc.), Java, PHP, SQL Server, Oracle, and even the IBM i languages (RPG, COBOL, and C.)

What Can I Do with an MD5 Hash on the IBM i?

Having an MD5 hash available has the following potential applications:


Create Hashes to Uniquely Identify Data

If you create a file on the IFS and need to verify that its contents have not been tampered with, you can calculate and store a unique hash for the file. The same application applies to database data.


MD5 hashes can be used to compare query result sets. For example, if I rewrite a query for performance reasons, I often want to make sure that the new result set matches the original result set. If I have a large result set created by Query A involving character data expressions, and I want to compare the results (in total) to Query B, I can let a hash algorithm verify that the data is the same between the two result sets both in data values and the order of the rows. If anything is different between the two sets, the hashes will be different.


Store Hashes Instead of Private Data

If you have an application that requires a custom-written user security system (instead of using i/OS user profiles) such as a Web application with a large number of users, when a user first defines his or her password, you can simply compute and store the password hash instead of storing the password itself. Every time a user attempts to log in, the login process only needs to re-compute the hash and check to see if the hash of the login password matches the original value stored in the database. Not storing the password at all prevents the potential security exposure of stolen passwords. Remember, the original password cannot be reconstructed from its hash.


Hashes Can Reduce Storage and Improve Performance

If you have an application that has to do a unique value lookup on a large character column, then an MD5 hash will allow you to save index storage and potentially increase performance. For example, if you have a CHAR(100) indexed column (or even a group of columns), you can create a unique hash and store the value in a BINARY(16) column. The lookup can be done against the hash value instead of the data columns. Building an index on a 16-byte column is preferable to a 100-byte column!

Currently on DB2 for i, you can't use a user-defined function in an index expression. Therefore, you need to store the hash value in a separate column so that the hash value can be indexed. Keep in mind, this index built on a hash column will be useful only for "equal value" lookups; SQL LIKE or RPG SETLL operations will not work. Further, if the index has to be updated often, it could negatively affect performance because the binary hash values will basically be random and can cause additional index maintenance overhead.


Provide Compatibility with Other Languages and Environments

Here are a couple of examples:


  • An RPG program needs to verify an MD5 password hash that's stored in Oracle.
  • You're converting a SQL Server stored procedure to DB2 for i and need to replicate the functionality of the HashBytes function. You can now write your own limited HashBytes equivalent using a DB2 user-defined function.

An RPG Service Program Example

As a sample, RPG service program MD5R contains subprocedures MD5Hash_BINARY and MD5Hash_HEX, which will create an MD5 hash on a string value and return the hash value as either a 128-bit binary value or a 32-character hex value.


Both subprocedures are very small. The CIPHER machine interface (MI) operation does all the hard work of calculating the hash. All the RPG program needs to do is correctly define the "controls" (aka input or parameters) and be able to retrieve and interpret the output of CIPHER.


These subprocedures can be called from another ILE program or be invoked by DB2 as a user-defined function (UDF). Sample DB2 for i CREATE FUNCTION statements are included in the source header documentation to demonstrate how to make these subprocedures available to DB2. For example, the following statement shows how UDF md5hash_hex can be invoked in a query:


SELECT md5hash_hex('mypassword') FROM qsqptabl


The result is a 32-character hex string: 34819D7BEEABB9260A5C854BC85B3E44.


Please note that the sample DB2 user-defined functions have their input parameters defined with CCSID 1208 (UTF-8). This means that the input data will be translated by DB2 to 8-bit Unicode (similar to ASCII in many respects) before passing the data to the RPG program so that the function will perform similarly to hash functions found on non-EBCDIC systems. If you want the hash applied against the EBCDIC data, then the parameter CCSID should be changed to the appropriate value (CCSID 37 is US EBCDIC). 


Likewise, if you want an ILE program to operate in a mode compatible with other systems, such as a Java program or SQL Server, then the EBCDIC data will need to be converted to an ASCII code page or Unicode first before calling the hash subprocedure. The iconv API offers one possible mechanism to accomplish this conversion within an RPG program.


One special "control" or "parameter" of the CIPHER operation to note is "sequence." Although not demonstrated in the sample program, specifying the sequence correctly can allow CIPHER to build a hash on multiple non-contiguous values that are passed across multiple calls to the MI. This means you can create a single hash from three independent pieces of data, such as name, address, and phone. Implementing this feature in a DB2 function (using the SCRATCHPAD feature of external user-defined functions) allows you to create a "running" hash on all the values in a column.

A New Tool for the Box

Using an MD5 hash opens up all kinds of integration, cross-database compatibility, performance, and security options for your applications. Take advantage of the i/OS built-in APIs to give your applications these advanced capabilities.

as/400, os/400, iseries, system i, i5/os, ibm i, power systems, 6.1, 7.1, V7,

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. He can be contacted at This email address is being protected from spambots. You need JavaScript enabled to view it..



Support MC Press Online


Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: