Post-Truth PL/SQL

We’re living in a Post-truth age. I know this because I read it in my Fake News Feed.
Taking advantage of this, I’ve updated the definition of PL/SQL.
Up until now, it would be true to say that PL/SQL is a 3GL based on ADA that’s incorporated into the Oracle RDBMS.
Post truth, the definition is that PL/SQL is a 3GL that comes with it’s own built-in Oracle RDBMS.

By a stroke of good fortune, my son recently bought me a copy of Ghost in the Wires by Kevin Mitnick and William L Simon, which begins each chapter with an encrypted phrase.
If your anything like me, you’d spend a fair amount of time geeking over this sort of problem, most likely using some fashionable programming language to help solve the riddles with which you were presented.

In my house at least, PL/SQL is back in fashion…

Some true (and post-true) statements

The code presented here has been written on and tested on an Oracle 11g Express Edition database.
The purpose of this exercise is to demonstrate the power and utility of PL/SQL as a language in it’s own right. Yes, I make use of the database. To ignore it would be a bit like trying to write C without using pointers.

The point is that PL/SQL is not simply another stored procedure language, SQL tricked out to make it Turing Complete. It’s a proper 3GL in it’s own right.

The PL/SQL “standards” I’ve adopted here are intended for aesthetic reasons as well as those of readability. In other words, not using “l_” as a prefix for a local variable makes the code look less spikey.

I’ve followed the C convention with constants, which I’ve defined and used in uppercase.
I still can’t bring myself to use Camel Case in PL/SQL for reasons which I go into here.
If I look back at this code in six months time and try to figure out just what I was doing I’ll know whether the “aesthetic” standards, was a good idea.

The cryptograms contained in this post are taken from the Hardback edition of the book. The ones in the paperback are different.

Before we go any further, I would like it to be known that I managed to solve all of the encrypted phrases without any help at all. I did this by googling “Ghost in the Wires encrypted phrases” and then read the comprehensive solution offerred by Fabien Sanglard.
In the old days, this may have been considered akin to “looking in the back of the book for the answers”. As we’ve already established, times have changed…

The Caesar Cipher

Let’s take a look at the first encrypted phrase in the book :

yjcv ku vjg pcog qh vjg uauvgo wugf da jco qrgtcvqtu vq ocmg htgg rjqpg ecnnu ?

Pretending that I haven’t already “looked in the back of the book” for the answers, we can deduce a couple of things from the way this phrase is formatted.

The fact that the letter groupings separated by spaces are not a constant number would seem to suggest that these are possibly words.
The question mark at the end would seem to re-enforce this notion and tell us that the encrypted phrase is a question.
This being the case, the repeating three letter pattern “vjg” might represent the word “THE”.
If this were the case, then it would mean that all of the letters in the phrase had been shifted forwards by 2 letters in the alphabet. Thus, V = T, J = H and G = E.
This method of encoding is known as a Caesar Cipher.

If we’re going to “have a stab” at cracking a Caesar Cipher then brutus.sql would seem to be an appropriate name for the following program…

set serveroutput on size unlimited
declare
    UPPER_A constant pls_integer := 65; -- Ascii code for 'A'
    UPPER_Z constant pls_integer := 90; -- Ascii code for 'Z'
    LOWER_A constant pls_integer := 97; -- Ascii code for 'a'

    phrase varchar2(4000); 
    offset pls_integer;
    this_char varchar2(1);
    decrypted_char varchar2(1);
    decrypted_phrase varchar2(32767);
    ascii_a pls_integer;
begin
    
    phrase := 'yjcv ku vjg pcog qh vjg uauvgo wugf da jco qrgtcvqtu vq ocmg htgg rjqpg ecnnu ?';
    offset := 2;

    for i in 1..length( phrase) loop
        this_char := substr( phrase, i, 1);
        if ascii( upper( this_char)) not between UPPER_A and UPPER_Z then
            -- not a letter, just use the character unchanged
            decrypted_char :=  this_char;
        else
            -- Make sure that the character stays within the bounds of
            -- alpha ascii codes after the offset is applied
            ascii_a := case when ascii(this_char) between UPPER_A and UPPER_Z then UPPER_A else LOWER_A end;

            -- now apply the offset...
            decrypted_char := chr( ascii( this_char) - offset);

            if ascii(decrypted_char) < ascii_a then
                -- character needs to "wrap around" to the other end of the alphabet
                decrypted_char := chr( ascii(decrypted_char) + 26);
            end if;
        end if;
        decrypted_phrase := decrypted_phrase||decrypted_char;
    end loop;
    dbms_output.put_line( decrypted_phrase);
end;
/

Using the fact that uppercase letters have ASCII codes between 65 and 90, we can apply the offset by easily enough by subtracting the offset from the ASCII code of each letter then converting the result back into a character.
The ASCII function shows an ASCII code for a given character. The CHR function converts an ASCII value to it’s corresponding character.
In pre-truth terms, these are both SQL, rather than PL/SQL functions. However, SQL is merely a component in the Oracle RDBMS and therefore a subset of the all-encompassing Post Truth PL/SQL.

Leaving the semantics aside, when we run this we get :

@brutus.sql
what is the name of the system used by ham operators to make free phone calls ?

PL/SQL procedure successfully completed.

… so our hypothesis is correct.

One down, 37 to go. Whilst the script we’ve got at the moment is fine for cracking a single code, we could probably do with something a bit more parameterized…

Brute Forcing with Brutus

As you may have observed already, the Caesar Cipher is rather susceptible to brute forcing as the offset used to encode a phrase can only be up to 25.
OK, you can offset to 26 (the number of letters in the English alphabet) but then your encoded string will be the same as the unencoded one which rather defeats the whole object of the exercise.

To start with then, we can make things a bit easier for ourselves by persisting the phrases we want to crack. We’ll also want to record the plain text for each of them once we’ve managed to figure it out. Hang on, we’ve got a database lying around somewhere…

create table giw_codes
(
    chapter_no number(2) not null,
    cryptogram varchar2(4000) not null,
    encryption_method varchar2(50),
    message varchar2(4000),
    answer varchar2(4000),

    constraint giwc_pk primary key (chapter_no)
)
/

comment on table giw_codes is 'Cryptograms from the book Ghost in the Wires by Kevin Mitnick with William L. Simon. Table Alias : GIWC'
/

comment on column giw_codes.chapter_no is 'The chapter number in which the cryptogram appears. Part of Primary Key'
/

comment on column giw_codes.cryptogram is 'The encrypted phrase'
/

comment on column giw_codes.encryption_method is 'Method of encryption used'
/

comment on column giw_codes.message is 'The deciphered message'
/

comment on column giw_codes.answer is 'The answer to the question in the deciphered message.'
/

Now to populate it. Post-Truth PL/SQL still allows you to be a bit lazy with your inserts

declare

    -- because I can't be bothered to type the insert statement 38 times...

    procedure ins( i_chapter giw_codes.chapter_no%type, i_cryptogram giw_codes.cryptogram%type)
    is
    begin
        insert into giw_codes( chapter_no, cryptogram, encryption_method, message, answer)
        values( i_chapter, i_cryptogram, null, null, null);
    end ins;
    
begin
    -- ... I'll just call the procedure...
    
    ins(1, 'yjcv ku vjg pcog qh vjg uauvgo wugf da jco qrgtcvqtu vq ocmg htgg rjqpg ecnnu ?');
    ins(2, 'wbth lal voe htat oy voe wxbirtn vfzbqt wagye C poh aeovsn vojgav ?');
    ins(3, 'Nyrk grjjnfiu uzu Z xzmv kf jvklg re rttflek fe Kyv Rib ?');
    ins(4, q'[Flle ujw esc wexp mo xsp kjr hsm hiwwcm, 'Wplpll stq lec qma e wzerg mzkk!' ?]');
    ins(5, 'Bmfy ytbs ini N mnij tzy ns zsynq ymj Ozajsnqj Htzwy qtxy ozwnxinhynts tajw rj ?');
    ins(6, 'Kyoo olxi rzr Niyovo Cohjpcx ojy dn T apopsy ?');
    ins(7, 'Kvoh wg hvs boas ct hvs Doqwtwq Pszz sadzcmss kvc fsor hvs wbhsfboz asac opcih am voqywbu oqhwjwhwsq cjsf hvs voa forwc ?');
    ins(8, 'Iwh xwqv wpvpj fwr Vfvyj qks wf nzc ncgsoo esg psd gwc ntoqujvr ejs rypz nzfs ?');
    ins(9, 'Hsle td esp epcx qzc dzqehlcp mfcypo zy esp nsta esle Yzglepw dpye xp ?');
    ins(10, 'Bprf cup esanqneu xmm gtknv amme U biiwy krxheu Iwqt Taied ?');

    ins(11, 'Lwpi idlc sxs bn upiwtg axkt xc lwtc X bdkts xc lxiw wxb ?');
    ins(12, q'[Yhlt xak tzg iytfrfad RanBfld squtpm uhst uquwd ce mswf tz wjrwtsr a wioe lhsv Ecid mwnlkoyee bmt oquwdo't ledn mp acomt ?]');
    ins(13, 'Zkdw lv wkh qdph ri wkh SL ilup wkdw zdv zluhwdsshg eb Sdflilf Ehoo ?');
    ins(14, 'Plpki ytw eai rtc aaspx M llogw qj wef ms rh xq ?');
    ins(15, 'Ituot oaybmzk ymwqe ftq pqhuoq ftmf Xqiue geqp fa buow gb mzk dmpua eusmxe zqmd Qduo ?');
    ins(16, q'[Kwth qzrva rbq lcq rxw Svtg vxcz zm vzs lbfieerl nsem rmh dg ac oef'l cwamu ?]');
    ins(17, 'Epib qa bpm vium wn bpm ixizbumvb kwuxtmf epmzm Q bziksml lwev Mzqk Pmqvh ?');
    ins(18, 'Khkp wg wve kyfcqmm yb hvh TBS oeidr trwh Yhb MmCiwus Wko ogvwgxar hr ?');
    ins(19, q'[Rcvo dn ivhz ja ocz omvinvxodji oj adiy v kzmnji'n njxdvg nzxpmdot iphwzm pndib oczdm ivhz viy yvoz ja wdmoc ?]');

    ins(20, q'[Wspa wdw gae ypte rj gae dilan lbnsp loeui V tndllrhh gae awvnh 'HZO, hzl jaq M uxla nvu']');
    ins(21, '4A 75 6E 67 20 6A 6E 66 20 62 68 65 20 61 76 70 78 61 6E 7A 72 20 74 76 69 72 61 20 67 62 20 47 72 65 65 6C 20 55 6E 65 71 6C 3F ');
    ins(22, 'Gsig cof dsm fkqeoe vnss jo farj tbb epr Csyvd Nnxub mzlr ut grp lne ?');
    ins(23, 'Fqjc nunlcaxwrl mnerln mrm cqn OKR rwcnwcrxwjuuh kanjt fqnw cqnh bnjalqnm vh jyjacvnwc rw Ljujkjbjb ?');
    ins(24, 'Xvof jg qis bmns lg hvq thlss ktffb J cifsok EAJ uojbthwsbhlsg ?');
    ins(25, 'Cngz zuct ngy znk grsg sgzkx lux znk xkgr Kxoi Ckoyy ?');
    ins(26, 'Aslx jst nyk rlxi bx ns wgzzcmgw UP jnsh hlrjf nyk TT seq s cojorpdw pssx gxmyeie ao bzy glc ?');
    ins(27, '85 102 121 114 32 103 113 32 114 102 99 32 108 121 107 99 32 109 100 32 114 102 99 32 122 109 105 113 '
        ||'114 109 112 99 32 71 32 100 112 99 111 115 99 108 114 99 98 32 103 108 32 66 99 108 116 99 112 63');
    ins(28, 'Phtm zvvvkci sw mhx Fmtvr VOX Ycmrt Emki vqimgv vowx hzh L cgf Ecbst ysi ?');
    ins(29, '126 147 172 163 040 166 172 162 040 154 170 040 157 172 162 162 166 156 161 143 040 145 156 161 '
        ||'040 163 147 144 040 115 156 165 144 153 153 040 163 144 161 154 150 155 172 153 040 162 144 161 165 '
        ||'144 161 040 150 155 040 122 172 155 040 111 156 162 144 077');

    ins(30, q'[Ouop lqeg gs zkds ulv V deds zq lus DS urqstsn't wwiaps ?]');
    ins(31, 'Alex B25 rixasvo hmh M ywi xs xli HQZ qemrjveqi ?');
    ins(32, q'[Caem alw Ymek Xptq'd tnwlchvw xz lrv lkkzxv ?]');
    ins(33, 'Ozg ojglw lzw hshwj gf AH Khggxafy lzsl BKR skcww ew stgml ?');
    ins(34, q'[Nvbx nte hyv bqgs pj gaabv jmjmwdi whd hyv UVT'g Giuxdoc Gctcwd Hvyqbuvz hycoij ?]');
    ins(35, '2B 2T W 2X 2Z 36 36 2P 36 2V 3C W 3A 32 39 38 2Z W 3D 33 31 38 2V 36 3D W '
        ||'2R 2Z 3C 2Z W 3E 3C 2V 2X 2Z 2Y W 3E 39 W 2R 32 2V 3E W 2V 3A 2V 3C 3E 37 2Z 38 3E '
        ||'W 2X 39 37 3A 36 2Z 2S 1R');
    ins(36, 'Lsar JSA cryoi ergiu lq wipz tnrs dq dccfunaqi zf oj uqpctkiel dpzpgp I jstcgo cu dy hgq ?');
    ins(37, 'V2hhdCBGQkkgYWdlbnQgYXNrZWQgU3VuIE1pY3Jvc3lzdGVtcyB0byBjbGFpbSB0aGV5IGxvc3QgODAgbWlsbGlvbiBkb2xsYXJzPw==');
    ins(38, '100-1111-10-0 011-000-1-111 00-0100 1101-10-1110-000-101-11-0-1 '
        ||'0111-110-00-1001-1-101 111-0-11-0101-010-1-101 111-10-0100 110011');


    commit;
        
end;
/

Now, whilst we could modify our Brutus script to simply select the encrypted phrases from the table and just loop through all possible offsets for each of them, that would result in the output of 950 phrases, almost all of which would be gibberish. It would be good then, if we could persuade the program to be a bit more discerning about it’s output.

According to Wikipedia, the 25 most common words make up about one third of all printed material in English. So, by searching for these, we can potentially filter out most of the junk from our result set.
Another point to consider is that many of the messages we’re trying to decrypt appear to be questions, so throwing in some common ‘question’ words ( e.g. how, why, who, what etc) may be helpful.
I’m going to exclude ‘I’ and ‘A’ from this list as matching on them is likely to generate a lot of false positives.
In the end, what I’m left with then (using the boringly conventional method of inserting rows to a table) is…

-- Creating this as an Index Organized Table ensures that no words are duplicated
-- whilst avoiding the overhead of copying the entire contents of the table to an index.

create table common_words( word varchar2(30), constraint cw_pk primary key(word))
    organization index
/

insert into common_words( word) values('THE');
insert into common_words( word) values('BE');
insert into common_words( word) values('TO');
insert into common_words( word) values('OF');
insert into common_words( word) values('AND');
insert into common_words( word) values('IN');
insert into common_words( word) values('THAT');
insert into common_words( word) values('HAVE');
insert into common_words( word) values('IT');
insert into common_words( word) values('FOR');
insert into common_words( word) values('NOT');
insert into common_words( word) values('ON');
insert into common_words( word) values('WITH');
insert into common_words( word) values('HE');
insert into common_words( word) values('AS');
insert into common_words( word) values('YOU');
insert into common_words( word) values('DO');
insert into common_words( word) values('AT');
insert into common_words( word) values('THIS');
insert into common_words( word) values('BUT');
insert into common_words( word) values('HIS');
insert into common_words( word) values('BY');
insert into common_words( word) values('FROM');

-- Throw in some lexemes (whatever they are)...

insert into common_words( word) values('IS');
insert into common_words( word) values('WERE');
insert into common_words( word) values('WAS');
insert into common_words( word) values('SHE');
insert into common_words( word) values('HERS');
insert into common_words( word) values('THEIRS');

-- And some 'question' words...

insert into common_words( word) values('WHO');
insert into common_words( word) values('WHAT');
insert into common_words( word) values('HOW');
insert into common_words( word) values('WHERE');
insert into common_words( word) values('WHEN');
insert into common_words( word) values('WHY');

-- Add past tense of the verb to do...
insert into common_words(word) values('DID');

-- and a conditional...possibly not as gramatically correct as it should be but hey...
insert into common_words(word) values('IF');
insert into common_words(word) values('ELSE');
insert into common_words(word) values('DOES');
insert into common_words(word) values('WHILE');

-- and whatever other random stuff seems reasonable...
insert into common_words(word) values('WE');
insert into common_words(word) values('US');
insert into common_words(word) values('THEM');
insert into common_words(word) values('THEIR');
insert into common_words(word) values('OUR');

commit;

Now we’ve persisted our encrypted phrases and some of the most common English words, we can save ourselves a fair bit of typing by incorporating the required code into a package.

First we need to create a package header to define the signature of the public package members (functions and procedures that are available to callers from outside of the package) :

create or replace package decrypt as
    --
    -- utilities for decrypting the cryptograms in the GIW_CODES table.
    --

    -- These constants were originally in the brutus (now caesar) function.
    -- However, I have a funny feeling that they may be needed elsewhere...

    UPPER_A constant pls_integer := 65; -- Ascii code for 'A'
    UPPER_Z constant pls_integer := 90; -- Ascii code for 'Z'
    LOWER_A constant pls_integer := 97; -- Ascii code for 'a'

    -- If i_string contains at least i_num_matches common words then return true
    function has_common_words( i_string in varchar2, i_num_matches in pls_integer default 2)
        return boolean;

    -- update GIW_CODES with any decrypted messages.
    procedure save_decrypted
    (
        i_chapter_no giw_codes.chapter_no%type,
        i_encryption_method giw_codes.encryption_method%type,
        i_message giw_codes.message%type
    );

    -- Call this function to decrypt a single cryptogram generated from a single cipher with a known offset
    function caesar( i_cryptogram in giw_codes.cryptogram%type, i_offset in pls_integer)
        return giw_codes.message%type;

    -- For the impatient, this will attempt to decrypt all cryptograms in the GIW_CODES table...
    procedure brute_force_caesar;
end decrypt;
/

…now for the code itself, which is in the package body…

create or replace package body decrypt as

    function has_common_words( i_string in varchar2, i_num_matches in pls_integer default 2)
        return boolean
    is
    --
    -- check i_string for whole words in COMMON_WORDS.
    -- if we get i_num_matches then return true
    --
    
        SPACE constant varchar2(1) := chr(32);
        matches pls_integer := 0;
        
    begin
        for r_words in (select SPACE||word||SPACE as word from common_words) loop
            if instr( upper( i_string), r_words.word, 1, 1) > 0 then
                matches := matches + 1;
                if matches = i_num_matches then
                    return true;
                end if;
            end if;
        end loop;
        return false;
    end has_common_words;

    procedure save_decrypted
    (
        i_chapter_no giw_codes.chapter_no%type,
        i_encryption_method giw_codes.encryption_method%type,
        i_message giw_codes.message%type
    )
    is
    --
    -- Update the GIW_CODES record identified by i_book_edition and i_chapter_no
    -- with i_encryption_method and i_message
    --
    begin
        update giw_codes
        set encryption_method = i_encryption_method,
            message = i_message
        where chapter_no = i_chapter_no;
    end save_decrypted;

    function caesar( i_cryptogram in giw_codes.cryptogram%type, i_offset in pls_integer)
        return giw_codes.message%type
    is
        --
        -- Translate i_cryptogram using an offset of i_offset this_chars.
        -- This is essentially brutus.sql made a bit more respectable now it's part of a package...
        --
        phrase varchar2(4000); 
        offset pls_integer;
        this_char varchar2(1);
        decrypted_char varchar2(1);
        decrypted_phrase varchar2(32767);
        ascii_a pls_integer;
    begin
        -- Parameter sanity check...
        if i_cryptogram is null or i_offset is null then
            raise_application_error(-20000, 'Both the cryptogram and the number of this_chars to offset must be supplied');
        end if;

        for i in 1..length( i_cryptogram) loop
            this_char := substr( i_cryptogram, i, 1);
            if ascii( upper( this_char)) not between UPPER_A and UPPER_Z then
                -- not a letter, just use the this_char unchanged
                decrypted_char :=  this_char;
            else
                -- Make sure that the this_char stays within the bounds of
                -- alpha ascii codes after the offset is applied
                ascii_a := case when ascii(this_char) between UPPER_A and UPPER_Z then UPPER_A else LOWER_A end;

                -- now apply the offset...
                decrypted_char := chr( ascii( this_char) - i_offset);

                if ascii(decrypted_char) < ascii_a then
                    -- this_char needs to "wrap around" to the other end of the alphabet
                    decrypted_char := chr( ascii(decrypted_char) + 26);
                end if;
            end if;
            decrypted_phrase := decrypted_phrase||decrypted_char;
        end loop;
        return decrypted_phrase;
    end caesar;
    
    procedure brute_force_caesar is
    --
    -- Cycle through all of the undeciphered cryptograms in GIW_CODES.
    -- Check the "decrypted" string for common words and if it passes this test, update the record in GIW_CODES
    --
    
        candidate_string giw_codes.message%type;
        
    begin
        for r_phrase in
        (
            select chapter_no, cryptogram
            from giw_codes
            where encryption_method is null
            order by 1,2
        )
        loop
            for i in 1..25 loop
                -- Loop through each possible Caesar cipher, stop if we get a match
                candidate_string := caesar( r_phrase.cryptogram, i);
                if has_common_words( candidate_string) then
                    save_decrypted( r_phrase.chapter_no, 'CAESAR', candidate_string);
                    exit;
                end if;
            end loop;
        end loop;
    end brute_force_caesar;
end decrypt;
/

After all of that, let’s see how many cryptograms we can decipher…

begin
    decrypt.brute_force_caesar;
end;
/

PL/SQL procedure successfully completed.

commit;

…and through the medium of sqlcl, we can see…

crack_caesar

So, it looks like 14 of our cryptograms are using a Caesar Cipher. Now for the the other 22…

Chipping away with Vigenere

It’s rather appropriate in an era of post-truth that we now come to a Cipher that’s not named after the person who invented it.
The Vigenere Cipher works in the same way as the Caesar Cipher but adds a key phrase into the mix. The key dictates the offset for each individual letter in the message.
It’s possible to lookup the letter code for each letter of the key using a grid known as a Vigenere Square or Vigenere Table.
Hmmm, table, that gives me an idea…

create table vigenere_squares
(
    cipher_key varchar2(1),
    plain_value varchar2(1),
    cipher_value varchar2(1)
)
/

comment on table vigenere_squares is
    'Table to translate the Vigenere Cipher. For a given key character (cipher_key), the cipher_value translates to the plain_value'
/

declare
    cipher varchar2(1);
    key varchar2(1);
    plain varchar2(1);
begin
    for i in 65..90 loop
        key := chr(i);
        for j in 0..25 loop
            plain := chr( j + 65);
            if (ascii(key) + j) > 90 then
                cipher := chr(ascii( key) + j - 26);
            else
                cipher := chr( ascii( key) + j);
            end if;
            insert into vigenere_squares( cipher_key, plain_value, cipher_value)
            values( key, plain, cipher);
        end loop;
    end loop;
end;
/

So, if our key phrase contains, say F ( for Fabien), we can lookup the translation for any letter…

vigenere_f

Alexandre Dumas is reputed to have remarked that English is French, badly spoken. As a true Englishman, with requisite apalling French accent, when someone mentions Vigenere, I automatically think of vinegar…malt vinegar – the condiment that defines the Great British Chip and distinguishes it from those continental frites which are usually subjected to mayonnaise.
Let’s see if we can ensure that the cipher used in chapter 2 has had it’s chips( or French Fries if your American).
Once again, let’s pretend I haven’t just copied Fabien but have instead been struck by an inspiration and have tried to use the answer to the question in chapter one as the key for the cipher in chapter2…

set serveroutput on size unlimited
declare
    phrase varchar2(4000);
    key varchar2(100);
    ptr pls_integer := 1;
    enc_char varchar2(1);
    plain_char varchar2(1);
    message varchar2(4000);
    
begin
    phrase := 'wbth lal voe htat oy voe wxbirtn vfzbqt wagye C poh aeovsn vojgav ?';
    key := 'AUTOPATCH';

    for i in 1..length(phrase) loop
        enc_char := substr( phrase, i, 1);
        if ascii( upper( enc_char)) not between 65 and 90 then
            -- not an alpha character...
            plain_char := enc_char;
        else
            -- lookup the plain value, preserving case
            select case when ascii( enc_char) between 65 and 90 then plain_value else lower(plain_value) end
            into plain_char
            from vigenere_squares
            where cipher_value = upper( enc_char)
            and cipher_key = upper( substr( key, ptr, 1));

            -- Move to the next character in the key phrase 
            ptr := ptr + 1;
            if ptr > length( key) then
                -- we've reached the end of the key, loop around to the start
                ptr := 1;
            end if;
        end if;

        message := message||plain_char;
    end loop;
    dbms_output.put_line(message);
end;
/

…sure enough…

what was the name of the central office where I was almost caught ?

The next step then, would be to fill in the answers to all of the questions we’ve already decoded in case the answer for the Caesar Ciphered question in one chapter always provides the key for the Vigenere Ciphered question in the next…*sound of pages being turned*

update giw_codes set answer = 'AUTOPATCH' where chapter_no = 1;
update giw_codes set answer = 'JELLY' where chapter_no = 3;
update giw_codes set answer = 'OROVILLE' where chapter_no = 5;
update giw_codes set answer = 'BILLCOOK' where chapter_no = 7;
update giw_codes set answer = 'FIRMWARE' where chapter_no = 9;
update giw_codes set answer = 'CALABASAS' where chapter_no = 11;
update giw_codes set answer = 'TELTEC' where chapter_no = 13;
update giw_codes set answer = 'OPTOELECTRONICS' where chapter_no = 15;
update giw_codes set answer = 'OAKWOOD' where chapter_no = 17;
update giw_codes set answer = 'ALPHADENT' where chapter_no = 19;
update giw_codes set answer = 'BOOMBOX' where chapter_no = 23;
update giw_codes set answer = 'ELLENSBURG' where chapter_no = 25;
update giw_codes set answer = 'GTETELENET' where chapter_no = 31;
update giw_codes set answer = 'ROBERTMORRIS' where chapter_no = 33;

commit;

We can add vigenere decryption functionality into the package by means of a new couple of new procedures. In the package header, we add :

-- Decrypt i_cryptogram using vigenere cipher with i_key as key
function vigenere( i_cryptogram in giw_codes.cryptogram%type, i_key in varchar2)
    return giw_codes.message%type;

-- If we have enough data, check to see if any un-cracked cryptograms could be Vigenere Ciphered
procedure chip_away;

The first is to apply the Vigenere decryption itself…

...
    function vigenere( i_cryptogram in giw_codes.cryptogram%type, i_key in varchar2)
        return giw_codes.message%type
    is 
    --
    -- Decrypt i_cryptogram using the letters in phrase i_key to determine the offset for each character.
    --
    ptr pls_integer := 1;
    enc_char varchar2(1);
    plain_char varchar2(1);
    message giw_codes.message%type;
    begin
        -- parameter sanity check...
        if i_cryptogram is null or i_key is null then
            -- Same error as for Caesar above but use a different error number to distinguish it
            raise_application_error(-20001, 'Both the cryptogram and the number of characters to offset must be supplied');
        end if;
        for i in 1..length(i_cryptogram) loop
            enc_char := substr( i_cryptogram, i, 1);
            if ascii( upper( enc_char)) not between UPPER_A and UPPER_Z then
                -- not an alpha character...
                plain_char := enc_char;
            else

                select case when ascii( enc_char) between UPPER_A and UPPER_Z then plain_value else lower(plain_value) end
                into plain_char
                from vigenere_squares
                where cipher_value = upper( enc_char)
                and cipher_key = upper( substr( i_key, ptr, 1));

                -- Move the pointer to the next character in the key
                ptr := ptr + 1;
                if ptr > length( i_key) then
                    ptr := 1;
                end if;
            end if;

            message := message||plain_char;
        end loop;
        return message;
    end vigenere;
...

… and the second is to loop through the GIW_CODES table and see what records we can apply this to …

...
    procedure chip_away is
    --
    -- Attempt to decrypt any cryptograms that have not yet been cracked where we
    -- have an answer to the previous chapter's message to use as a key.
    --
        candidate_string giw_codes.message%type;
        key_string giw_codes.answer%type;
    begin
        for r_phrase in
        (
            select chapter_no, cryptogram, encryption_method, answer
            from giw_codes
            order by 1,2
        )
        loop
            -- Go through each record in the table...
            if r_phrase.encryption_method is not null
                and r_phrase.answer is not null
            then
                -- although this cryptogram has already been solved, the answer may serve as the
                -- key for the next record if it has been encrypted with a Vigenere Cipher...
                key_string := r_phrase.answer;
                continue;
             elsif r_phrase.encryption_method is null
                and key_string is not null
             then
                candidate_string := vigenere( r_phrase.cryptogram, key_string);
                if has_common_words( candidate_string) then
                    save_decrypted(r_phrase.chapter_no, 'VIGENERE', candidate_string);
                end if;
            end if;
        end loop;
    end chip_away;
...

…we can use it to plug some more of the gaps we have…

In the best British tradition, if it's not working, blame the French

In the best British tradition, if it’s not working, blame the French

Whilst some of the remaining uncracked cryptograms look suspiciously like they could be encrypted with Vigenere, others look rather different.
Our next challenge is rather more to do with numbers than letters (at least initially)…

Hello Hexy

Taking a look at the cryptogram for chapter 21, it does appear to be comprised of a series of hexadecimal numbers. What was that ? Fabien who ?…

select cryptogram
from giw_codes
where chapter_no = 21
/

CRYPTOGRAM                                                                                                                         

4A 75 6E 67 20 6A 6E 66 20 62 68 65 20 61 76 70 78 61 6E 7A 72 20 74 76 69 72 61 20 67 62 20 47 72 65 65 6C 20 55 6E 65 71 6C 3F   

Now PL/SQL has whizzy built-in function to make conversion from hex to to varchar2 that little bit easier…

set serveroutput on size unlimited
declare
    SPACE constant varchar2(1) := chr(32);
    phrase varchar2(4000);
    ptr pls_integer := 1;
    hex_num varchar2(3);
    dec_phrase varchar2(4000);

    candidate_string varchar2(4000);
begin
    phrase := '4A 75 6E 67 20 6A 6E 66 20 62 68 65 20 61 76 70 78 61 6E 7A 72 20 74 76 69 72 61 20 67 62 20 47 72 65 65 6C 20 55 6E 65 71 6C 3F ';
    while ptr < length( phrase) loop
        -- take each hex number in the string...
        hex_num := substr( phrase, ptr, instr( phrase, SPACE, ptr, 1)- ptr);
        -- ...and use a standard package and function together to convert to decimal
        dec_phrase := dec_phrase|| utl_raw.cast_to_varchar2( hextoraw( hex_num));
        ptr := instr( phrase, SPACE, ptr, 1) + 1;
    end loop;
    dbms_output.put_line( dec_phrase);
    -- Now see if we can decrypt the resulting string...
    for i in 1..25 loop
        candidate_string := decrypt.caesar( dec_phrase, i);
        if decrypt.has_common_words( candidate_string) then
            dbms_output.put_line('Saving : '||candidate_string);
            decrypt.save_decrypted( 21, 'HEX-CAESAR', candidate_string);
            exit;
        end if;
    end loop;
end;
/

…which looks rather promising…

@hexy_beast
Jung jnf bhe avpxanzr tvira gb Greel Uneql?
Saving : What was our nickname given to Terry Hardy?


PL/SQL procedure successfully completed.

commit;

Commit complete.
 

As we can now provide an answer to this question, we can potentially re-visit the cryptogram for chapter 22 which may well be a Vigenere string…

select cryptogram
from giw_codes
where chapter_no = 22
/

CRYPTOGRAM                                                              
----------------------------------------------------------------------
Gsig cof dsm fkqeoe vnss jo farj tbb epr Csyvd Nnxub mzlr ut grp lne ?  

First of all, we need to update the chapter 21 record with the answer…

update giw_codes set answer = 'KLINGON' where chapter_no = 21;
commit;

As I’m feeling lazy, I’ll just re-run the chip_away procedure…

ch22

If we look at what remains unsolved, most of them appear to follow the pattern of some numeric code followed by what may well be a vigenere enciphered string.
There is, however, one obvious exception to this…

Base64 Encoding

Looking at Fabien’s observations the cryptogram for chapter 37, the “==” at the end of the string seems typical of the padding for a Base64 encoded string :

select cryptogram
from giw_codes
where chapter_no = 37
/
CRYPTOGRAM                                                                                                
-----------------------------------------------------------------------
V2hhdCBGQkkgYWdlbnQgYXNrZWQgU3VuIE1pY3Jvc3lzdGVtcyB0byBjbGFpbSB0aGV5IGxvc3QgODAgbWlsbGlvbiBkb2xsYXJzPw==  

Once again, PL/SQL makes decrypting this somewhat simpler than you migh think…

select utl_raw.cast_to_varchar2( utl_encode.base64_decode( utl_raw.cast_to_raw( cryptogram)))
from giw_codes
where chapter_no = 37
/

UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(CRYPTOGRAM)))  
--------------------------------------------------------------------------------
What FBI agent asked Sun Microsystems to claim they lost 80 million dollars?   

That makes things simple then….

update giw_codes
set message = utl_raw.cast_to_varchar2( utl_encode.base64_decode( utl_raw.cast_to_raw( cryptogram))),
    encryption_method = 'BASE64'
where chapter_no = 37
/
commit;

Right, now to turn our attention back to those “numeric” cryptograms…

Converting other bases to Decimal

If we examine the cryptograms for chapters 27…

85 102 121 114 32 103 113 32 114 102 99 32 108 121 107 99 32 109 100 32 114 102 99 32 122 109 105 113 114 109 112 99 32 71 32 100 112 99 111 115 99 108 114 99 98 32 103 108 32 66 99 108 116 99 112 63 

…29…

126 147 172 163  040  166 172 162  040  154 170  040  157 172 162 162 166 156 161 143  040  145 156 161 040  163 147 144  040  115 156 165 144 153 153  040  163 144 161 154 150 155 172 153  040  162 144 161 165 144 161  040  150 155  040  122 172 155 040 111 156 162 144 077 

…and 35…

2B 2T W 2X 2Z 36 36 2P 36 2V 3C W 3A 32 39 38 2Z W 3D 33 31 38 2V 36 3D W 2R 2Z 3C 2Z W 3E 3C 2V 2X 2Z 2Y W 3E 39 W 2R 32 2V 3E W 2V 3A 2V 3C 3E 37 2Z 38 3E W 2X 39 37 3A 36 2Z 2S 1R

…they all look like numeric representations of ascii character values in a variety of bases.

Whilst we could write a program for each of these cryptograms, it’s so much less effort to do something that covers all three.
It also gives me the opportunity to use another package name which may not be entirely consistent with a real-world naming convention…

create or replace package under_crackers
as
--
-- Translate numeric cryptograms to characters
--
    -- Package constants, some of which may look familiar...

    ASCII_0 constant pls_integer := ascii('0');
    ASCII_9 constant pls_integer := ascii('9');
    SPACE constant varchar2(1) := chr(32); -- a space

    -- take i_number in base i_base and return the decimal equivalent.
    -- NOTE that i_number is a character string because bases above 10 include non-numeric characters.
    function base_to_decimal( i_number in varchar2, i_base in pls_integer)
        return pls_integer;

    -- return the character string represented by the ascii codes in i_cryptogram, which is currently in i_base.
    function code_to_string( i_cryptogram in giw_codes.cryptogram%type, i_base in pls_integer)
        return varchar2;
end under_crackers;
/

create or replace package body under_crackers
as
    function base_to_decimal( i_number in varchar2, i_base in pls_integer)
        return pls_integer
    is
        revnum varchar2(38);
        rtnval pls_integer := 0;
        digit varchar2(1);

        e_invalid_digit_for_base exception;
    begin
        --
        -- Sanity checks
        --
        if i_number is null or i_base is null then
            raise_application_error( -20000, 'Both a number and a base must be specified');
        elsif i_base not between 2 and 36 then
            raise_application_error( -20001, 'This function only converts bases 2 - 36');
        elsif i_base > 10 then
            -- make sure this is a valid i_base number
            if instr( i_number, chr(55 + i_base),1,1) > 0 then
                raise e_invalid_digit_for_base;
            end if;
        elsif i_base < 10 then
            if instr( i_number, i_base, 1, 1) > 0 then
                raise e_invalid_digit_for_base;
            end if;
        end if;
        -- Reverse the "digits" in i_number. That way we can loop through and add the decimal numbers represented by the
        -- characters in i_number without having to check how long it is first.
        -- the REVERSE function is a SQL, rather than PL/SQL built-in, hence...
        select reverse(i_number) into revnum from dual;
        for i in 1..length(revnum) loop
            digit := substr(revnum, i, 1);
            if ascii(digit) between ASCII_0 and ASCII_9 then
                rtnval := rtnval + ( digit * power(i_base, i - 1));
            else
                -- letters in bases above 10 are always offset from 10 - e.g. A = 10, B = 11 etc.
                -- so, subtracting 55 from the ascii code of the upper case letter will give us the decimal value
                rtnval := rtnval + ( ( ascii( upper( digit)) - 55) * power( i_base, i - 1) );
            end if;
         end loop;
         return rtnval;
    exception when e_invalid_digit_for_base then
        raise_application_error( -20002, 'Number '||i_number||' is not a valid '||i_base||' number.');
    end base_to_decimal;

    function code_to_string( i_cryptogram in giw_codes.cryptogram%type, i_base in pls_integer)
        return varchar2
    is
        ptr pls_integer := 1;
        this_num varchar2(38);
        decval pls_integer;
        rtn_string varchar2(4000);
    begin
        -- loop through each of the numbers in i_cryptogram and convert them to decimal...
        while ptr < length( i_cryptogram)
        loop
            -- add a trailing space to the string so we can easily move the pointer to the end of it
            this_num := substr( i_cryptogram||SPACE, ptr, instr( i_cryptogram||SPACE, SPACE, ptr, 1) - ptr);
            if i_base != 10 then
                decval := base_to_decimal(this_num, i_base);
            else
                decval := this_num;
            end if;
            -- convert the number ( ascii code) to the character it represents and append it to the output string
            rtn_string := rtn_string||chr(decval);
            -- increment the pointer to the next number in the string
            ptr := instr( i_cryptogram||SPACE, SPACE, ptr, 1) + 1;
        end loop;
        return rtn_string;
    end code_to_string;
end under_crackers;
/

Now for a test. the fact that “040” crops up quite a lot in the chapter 29 cryptogram may suggest that it is an octal representation of the ASCII code for a space ( decimal value of 32)…

select under_crackers.code_to_string(cryptogram, 8)
from giw_codes
where chapter_no = 29
/

UNDER_CRACKERS.CODE_TO_STRINGCRYPTOGRAM,8)
----------------------------------------------------------------
Vgzs vzr lx ozrrvnqc enq sgd Mnudkk sdqlhmzk rdqudq hm Rzm Inrd?

The acid test of all this is whether we can now decipher is using Caesar…

set serveroutput on size unlimited
declare
    message giw_codes.message%type;
begin
    for i in 1..25 loop
        message := decrypt.caesar('Vgzs vzr lx ozrrvnqc enq sgd Mnudkk sdqlhmzk rdqudq hm Rzm Inrd?', i);
        if decrypt.has_common_words( message) then
            dbms_output.put_line(message);
            exit;
        end if;
    end loop;
end;
/
What was my password for the Novell terminal server in San Jose?


PL/SQL procedure successfully completed.

That looks promising. Using the same “spot the space character” approach ( and checking back on Fabien’s work), we can hypothesise that chapter 27 is in straight decimal whilst chapter 35 is in base 36.
So, we can now run this :

set serveroutput on size unlimited
declare
    candidate_string giw_codes.message%type;
    encryption_method giw_codes.encryption_method%type;
begin
    for r_phrase in
    (
        select chapter_no,
            under_crackers.code_to_string
            (
                cryptogram,
                case chapter_no
                    when 27 then 10
                    when 29 then 8
                    when 35 then 36
                end
            ) as message
        from giw_codes
        where chapter_no in (27,29,35)
        order by chapter_no
    )
    loop
        encryption_method :=
            'BASE'||case r_phrase.chapter_no when 27 then 10 when 29 then 8 when 35 then 36 end||'-CAESAR';
        for i in 1..25 loop
            candidate_string := decrypt.caesar( r_phrase.message, i);
            if decrypt.has_common_words( candidate_string) then
                dbms_output.put_line('Chapter : '||r_phrase.chapter_no);
                dbms_output.put_line('Message : '||candidate_string);
                decrypt.save_decrypted( r_phrase.chapter_no, encryption_method, candidate_string);
                exit;
             end if;
         end loop;
    end loop;
    dbms_output.put_line('Review updated records and commit if OK. Rollback if not.');
end;
/

…and we’re rewareded with…

Chapter : 27
Message : What is the name of the bokstore I frequented in Denver?
Chapter : 29
Message : What was my password for the Novell terminal server in San Jose?
Chapter : 35
Message : My cellular phone signals were traced to what apartment complex?
Review updated records and commit if OK. Rollback if not.

We can now commit these updates.

Better still, providing some more answers….

update giw_codes set answer = 'TATTEREDCOVER' where chapter_no = 27;
update giw_codes set answer = 'SNOWBIRD' where chapter_no = 29;
update giw_codes set answer = 'PLAYERSCLUB' where chapter_no = 35;

commit;

…allows us to solve the outstanding vigenere ciphers…

base_answers

You’re probably wondering why I haven’t included the last cipher in this base conversion exercise. After all, it has to be binary, right ?

100-1111-10-0 011-000-1-111 00-0100 1101-10-1110-000-101-11-0-1 0111-110-00-1001-1-101 111-0-11-0101-010-1-101 111-10-0100 110011

Well, apparently, it isn’t…

One morse time

Yes, it is indeed, a representation of morse code.
The ‘1’s are the ‘.’s and the ‘0’s are the ‘-‘.

Fortunately, it’s easy enough to teach PL/SQL a bit of morse code…

create table morse_codes
(
    letter varchar2(1),
    morse varchar2(10)
)
/

--
-- Codes as per - https://en.wikipedia.org/wiki/Morse_code#Symbol_representations
--
insert into morse_codes( letter, morse) values ('A', '.-');
insert into morse_codes( letter, morse) values ('B', '-...');
insert into morse_codes( letter, morse) values ('C', '-.-.');
insert into morse_codes( letter, morse) values ('D', '-..');
insert into morse_codes( letter, morse) values ('E', '.');
insert into morse_codes( letter, morse) values ('F', '..-.');
insert into morse_codes( letter, morse) values ('G', '--.');
insert into morse_codes( letter, morse) values ('H', '....');
insert into morse_codes( letter, morse) values ('I', '..');
insert into morse_codes( letter, morse) values ('J', '.---');
insert into morse_codes( letter, morse) values ('K', '-.-');
insert into morse_codes( letter, morse) values ('L', '.-..');
insert into morse_codes( letter, morse) values ('M', '--');
insert into morse_codes( letter, morse) values ('N', '-.');
insert into morse_codes( letter, morse) values ('O', '---');
insert into morse_codes( letter, morse) values ('P', '.--.');
insert into morse_codes( letter, morse) values ('Q', '--.-');
insert into morse_codes( letter, morse) values ('R', '.-.');
insert into morse_codes( letter, morse) values ('S', '...');
insert into morse_codes( letter, morse) values ('T', '-');
insert into morse_codes( letter, morse) values ('U', '..-');
insert into morse_codes( letter, morse) values ('V', '...-');
insert into morse_codes( letter, morse) values ('W', '.--');
insert into morse_codes( letter, morse) values ('X', '-..-');
insert into morse_codes( letter, morse) values ('Y', '-.--');
insert into morse_codes( letter, morse) values ('Z', '--..');
--
-- Numbers
--
insert into morse_codes( letter, morse) values ('1', '.----');
insert into morse_codes( letter, morse) values ('2', '..---');
insert into morse_codes( letter, morse) values ('3', '...--');
insert into morse_codes( letter, morse) values ('4', '....-');
insert into morse_codes( letter, morse) values ('5', '.....');
insert into morse_codes( letter, morse) values ('6', '-....');
insert into morse_codes( letter, morse) values ('7', '--...');
insert into morse_codes( letter, morse) values ('8', '---..');
insert into morse_codes( letter, morse) values ('9', '----.');
insert into morse_codes( letter, morse) values ('0', '-----');

--
-- Punctuation
--
insert into morse_codes( letter, morse) values ('.', '.-.-.-');
insert into morse_codes( letter, morse) values (',', '--..--');
insert into morse_codes( letter, morse) values ('?', '..--..');
insert into morse_codes( letter, morse) values (q'[']', '.----.');
insert into morse_codes( letter, morse) values ('!', '-.-.--');
insert into morse_codes( letter, morse) values ('/', '-..-.');
insert into morse_codes( letter, morse) values ('(', '-.--.');
insert into morse_codes( letter, morse) values (')', '-.--.-');
insert into morse_codes( letter, morse) values ('&', '.-...');
insert into morse_codes( letter, morse) values (':', '---...');
insert into morse_codes( letter, morse) values (';', '-.-.-.');
insert into morse_codes( letter, morse) values ('=', '-...-');
insert into morse_codes( letter, morse) values ('+', '.-.-.');
insert into morse_codes( letter, morse) values ('-', '-....-');
insert into morse_codes( letter, morse) values ('_', '..--.-');
insert into morse_codes( letter, morse) values ('"', '.-..-.');
insert into morse_codes( letter, morse) values ('$', '...-..-');
insert into morse_codes( letter, morse) values ('@', '.--.-.');

commit;

The structure of the cryptogram itself is slightly different from the “base” strings in that it contains both character separators (“-“) and word separators ( space).
We can utilise this characteristic to split the string into words and then translate each word a character at a time.
As for converting “.-” to “10”, that’s rather neatly handled by the SQL TRANSLATE function…

set serveroutput on size unlimited
declare
    SPACE constant varchar2(1) := chr(32);
    delimiter varchar2(1) := chr(45); -- '-'
    phrase varchar2(32767);
    word varchar2(100);
    ptr pls_integer := 1;
    wptr pls_integer;
    this_code varchar2(100);
    this_char varchar2(1);
    candidate_string giw_codes.message%type;
    
begin
    -- Append a "word" delimiter to the end of the phrase to make splitting it a bit easier
    select cryptogram||SPACE
    into phrase
    from giw_codes
    where chapter_no = 38;

    for i in 1..regexp_count(phrase, SPACE) loop
        -- Get each complete word in the phrase...
        word := substr( phrase, ptr, instr( phrase, SPACE, ptr, 1) - ptr)||delimiter;
        wptr := 1;
        for j in 1..regexp_count(word, delimiter) loop
            -- ...and do a character-by-character translation
            this_code := substr( word, wptr, instr( word, delimiter, wptr, 1) - wptr);

            select letter into this_char
            from morse_codes
            where translate(morse, '.-', '10') = this_code;

            candidate_string := candidate_string||this_char;
            wptr := instr( word, delimiter, wptr, 1) + 1;
        end loop;
        -- Maintain the word boundaries
        candidate_string := candidate_string||SPACE;
        ptr := instr(phrase, SPACE, ptr, 1) + 1;
    end loop;
    dbms_output.put_line(candidate_string);
    decrypt.save_decrypted( 38, 'MORSE', candidate_string);
end;
/

Run this and we get…

WHAT DOES MY FAVORITE BUMPER STICKER SAY ? 

After all of that, we can now review the results of all that deciphering.

If we now run …

select chapter_no, encryption_method, message
from giw_codes
order by chapter_no
/

…we get…

all_answers

In keeping with the theme of these post, I’ll conclude by saying :

Ar tzs Cgyk Xvnbu Gyw TL/KEY kzzpp kwpqk !

…and leave you with Seasons Greetings.

2 thoughts on “Post-Truth PL/SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s