I am an experienced programmer with a lot of time spent in Excel everyday for my paying job. East Sierra Sage’s article on Cipher Security got me thinking again about One Time Pads and other secure message techniques. I really enjoy automating things with Excel’s powerful formulas and macros. So I set off to create a tool for a One Time Pad spreadsheet that would create the One Time Pads and also encode and decode the messages being sent.
How to Use a One Time Pad (OTP)
You will need a Shift Chart and an OTP Chart as the basic components for this type of message security. The Shift Chart is used to look up a character in your message and then offset or shift to a position in the Shift Chart using another character found in the OTP Chart. Your message will be encoded one character at a time.
To Encode Your Message
To encode your message, you take the first letter of your message and go down the left most column of your Shift Chart. When you find that letter in your Shift Chart, you will then find the next character available in your OTP Chart. To do that, go to the row of your OTP Chart and find the next unused character. If you have already used all of a row, just go down to the next available row. With your OTP character you find it by going across the top row of the Shift Chart. Use the character where the column and row intersect as your encoded letter. Cross off your OTP character in the OTP Chart, so that you will not ever use it again. Continue with that process until you have your entire message encoded.
To Decode a Received Message
To decode a received message, find your next OTP character in the OTP Chart and then find it by going across the top of your Shift Chart. Then look for the first received character in the message and go down that column until you find that character. Follow that row all the way to the left to see what the decoded message character is. Cross off your OTP and repeat that process until your message has been decoded.
Note: If you are new to this type of technique, it might be a good idea to print off the example picture in this article and then reread this section on how to use and OTP.
Creating the OTP Chart
Decide What Characters To Use
On a tab called OneTimePad, I allow the user to decide what characters they wish to use for the OTP. I decided to allow for 50 unique characters. The user can choose basically anything on the keyboard except an asterisk, a question mark, or a space. (Each of those characters is more difficult to program around.) These 50 characters are what will be used to create your OTP as well as the characters used in your encoded message.
Note About Use of High Numbered ASCII Codes
Advanced Note: You can even use some of the higher numbered ASCII codes to create something that is visibly harder to discern because they are characters or symbols that we aren’t used to looking at. (Examples of these are œ,ž,Ÿ,¡,¢,£,¤, and ¥.) If you are just getting started, I would suggest sticking with just uppercase letters, 0-9, and a few of the characters above the numbers on the keyboard, like in the example.
Create a 50×50 Grid of Randomly Created OTPs
Once the characters are selected, the user clicks a macro button to create a 50×50 grid of those randomly created characters using Excel’s random number generator. It is widely known that Excel’s random number isn’t so random, so I decided to take the characters that it generated and do some additional sorting to mix up the randomness even farther. I did this through sorting each of the rows and columns several times each. The result is 2,500 (50×50) randomly created OTPs.
Note: In the screen shot, you can see the Usable Letters at the top. The numbers above them are a count of how many times it appears in the 50×50 OTP Chart.
Creating the Shift Chart
Creating Column and Row Headers
On a tab called Shift, I have my Shift Chart setup to be another 50×50 grid. I start by creating column and row headers. The row headers are the actual characters that I want to type as my message (A-Z, 0-9, et cetera). The column headers are the same characters that I will be using in my OTP. Those 50 characters can also be A-Z, 0-9, et cetera, or they can be some of the ASCII characters that I referred to earlier.
Start Offsetting the Next Row
Once your headings are in place, copy your column headings down as your first row of data in your 50×50 grid. You will then want to start offsetting with the next row. By this, I mean that you will copy the row above by offsetting it one position to the right. Then, the first character that you didn’t copy is placed on the end of the row.
Follow that process until you complete your 50×50 grid Shift Chart.
Encoding and Decoding
On a tab called Convert, I have a cell to type in the message I want to encode. Below that I have a cell that will be filled in with the results of the encoding. Several cells below that, I have a cell to put in an encoded message that was received from your contact and below that a cell that will show the results after it is decoded.
So in this example, I have typed in www.survivalblog.com in cell C4. After clicking the “Convert to Send” button, it is encoded to be B^EYD#_79/+OI^E8S36$. The macro does several things. Before encoding starts, it:
- checks to make sure that the message only contains characters that are in the left column of my Shift Chart,
- makes sure that there are no more than 140 characters in the message (we are not writing a book here after all), and then after encoding
- copies the message into the next row available, starting in row 19 so that you can record the conversation
A received encoded message is copied into cell C8. It is decoded and the results placed into cell C9 as well as copied into the conversation area. The message I received was HI4KOEN$E)UA3D&E11&4Z&^), which decoded to “one of my favorite sites”.
Note about Use of Spaces
Programming Note: I do not allow spaces in the Shift or OTP charts, but I was expecting the user to use spaces between words as they typed their message. To allow for that, before any encoding takes place I substitute any space (“ ”) that I find in the message with an underscore (“_”). Likewise, after decoding has taken place, I substitute all underscores with spaces to show the original message.
How to Use
The Shift Chart and the OTP Chart are the key data pieces of this system. They can be created, printed, and used in a manual way without the aid of a computer. I created this spreadsheet, however, for more of a base station role that might have the aid of a computer.
This now brings to mind many suggestions that I am sure we are all thinking about, like that the computer should be air gapped with no way for it to get an Internet connection and many other security concerns. I will allow you to figure those out or read the many suggestions that will come in from the comments section.
How to Get Message to Contact
So how do you get the message to your contact(s)? You could send them over the Internet with a separate computer via text message or email, via radio (though there can be no coded message over Ham radio until the SHTF), dead drop, Morse code, et cetera.
Extra Bells and Whistles
I have also created a way for multiple conversations to take place in the same spreadsheet. The potential issue is making sure to have an OTP for each person that you want/need to communicate with. So I will have one OTP Chart on its own tab for each group or person with whom I message. I have a drop down in cell A14 that allows me to select which contact that this conversation will be with and what OTP Chart will be used for encoding or decoding.
Watch Outs and Suggestions
- Use as few words as possible
- Do not try to use full sentences or necessarily proper sentence structure
- Do not use punctuation unless absolutely necessary.
- Messages have to be processed in the order they were sent and received.
- Do not create a new message until you have processed all received messages.
Pros & Cons
- It is portable or could easily be used at a base station
- It is simple to do with or without a computer
- Very secure as long as the OTP Chart is secure and not reused
- It is possible to corrupt the OTP, if messages are processed out of order.
I hope this inspires you to create something like this. I have really enjoyed creating this spreadsheet. I look forward to reading your comments. I am sure there will be suggestions for making it better and more secure.
In tomorrow’s Part 2, I will detail another encoding spreadsheet that I created after reading another article on SurvivalBlog several years ago.
- Creating Your Own Secure Messages– Part 2, by DaytonPrepper1 (Active on 4/22/18)
SurvivalBlog Writing Contest
This has been part one of a two part entry for Round 76 of the SurvivalBlog non-fiction writing contest. The nearly $11,000 worth of prizes for this round include:
- A $3000 gift certificate towards a Sol-Ark Solar Generator from Veteran owned Portable Solar LLC. The only EMP Hardened Solar Generator System available to the public.
- A Gunsite Academy Three Day Course Certificate. This can be used for any one, two, or three day course (a $1,095 value),
- A course certificate from onPoint Tactical for the prize winner’s choice of three-day civilian courses, excluding those restricted for military or government teams. Three day onPoint courses normally cost $795,
- DRD Tactical is providing a 5.56 NATO QD Billet upper. These have hammer forged, chrome-lined barrels and a hard case, to go with your own AR lower. It will allow any standard AR-type rifle to have a quick change barrel. This can be assembled in less than one minute without the use of any tools. It also provides a compact carry capability in a hard case or in 3-day pack (an $1,100 value),
- Two cases of Mountain House freeze-dried assorted entrees in #10 cans, courtesy of Ready Made Resources (a $350 value),
- A $250 gift certificate good for any product from Sunflower Ammo,
- Two cases of Meals, Ready to Eat (MREs), courtesy of CampingSurvival.com (a $180 value), and
- American Gunsmithing Institute (AGI) is providing a $300 certificate good towards any of their DVD training courses.
- A Model 175 Series Solar Generator provided by Quantum Harvest LLC (a $439 value),
- A Glock form factor SIRT laser training pistol and a SIRT AR-15/M4 Laser Training Bolt, courtesy of Next Level Training, which have a combined retail value of $589,
- A gift certificate for any two or three-day class from Max Velocity Tactical (a $600 value),
- A transferable certificate for a two-day Ultimate Bug Out Course from Florida Firearms Training (a $400 value),
- A Three-Day Deluxe Emergency Kit from Emergency Essentials (a $190 value),
- A $200 gift certificate good towards any books published by PrepperPress.com,
- RepackBox is providing a $300 gift certificate to their site.
- A Royal Berkey water filter, courtesy of Directive 21 (a $275 value),
- A large handmade clothes drying rack, a washboard, and a Homesteading for Beginners DVD, all courtesy of The Homestead Store, with a combined value of $206,
- Expanded sets of both washable feminine pads and liners, donated by Naturally Cozy (a $185 retail value),
- Two Super Survival Pack seed collections, a $150 value, courtesy of Seed for Security, LLC,
- Mayflower Trading is donating a $200 gift certificate for homesteading appliances, and
- Two 1,000-foot spools of full mil-spec U.S.-made 750 paracord (in-stock colors only) from www.TOUGHGRID.com (a $240 value).
Round 76 ends on May 31st, so get busy writing and e-mail us your entry. Remember that there is a 1,500-word minimum, and that articles on practical “how to” skills for survival have an advantage in the judging.