Home » Other » General » New Puzzle topic: Vigenere Cipher
New Puzzle topic: Vigenere Cipher Thu, 15 April 2010 15:07
 Aussiedev Messages: 4Registered: March 2010 Location: Australia Junior Member
Hi all - I just thought I'd propose another puzzle. I was working on this today in my spare time just out of interest.

I had previously (years ago) written something in PL/SQL to encrypt and decrypt using the standard Vigenere cipher (Wikipedia explanation) but I thought that it could be done in a single SQL statement.

I'd be interested to see how many different ways there are to do this. I'd particularly be interested in seeing if it's possible using a model clause or something.

Here's what I came up with:

```select regexp_replace(max(sys_connect_by_path(decode(mod(rn,5),0,cy||' ',cy),'-')),'-','') CYPHERTEXT
from (select row_number() over (order by L) rn, cy  from
(select chr(mod(ascii(substr(P,level,1))+
ascii(substr(K,decode(mod(level,length(K)),0,length(K)
,mod(level,length(K))),1))-130,26)+65) cy
,level L
from (select regexp_replace('SOME SUPER SECRET TEXT PROBABLY CONTAINING THE MEANING OF LIFE THE UNIVERSE AND EVERYTHING',' ') P
,regexp_replace('SUPER SECRET PASS PHRASE',' ') K
from dual)
connect by level <= length(P)))
connect by rn = prior rn + 1

Result:

KIBIJ MTGIW XRRWL ILOTH VGVPF CQGQE XTXNA FVAYE EISHX RXGJN ZJXIH WMCPM EJWWU CHVNI TPXAX NY

```

Here it is a little bit more expanded out, the way I originally wrote it so it wouldn't look quite so nightmarish:

```with t as (
select --C = mod(P + K,26) assuming A=0
chr(mod(p+k,26)+65) Cy
,p
,k
,L
from
(select ascii(P)-65 p
,ascii(K)-65 k
,L
from (
select substr(P,level,1) P
,substr(K,decode(mod(level,length(K)),0,length(K),mod(level,length(K))),1) K
,level L
from (select regexp_replace('SOME SUPER SECRET TEXT PROBABLY CONTAINING THE MEANING OF LIFE THE UNIVERSE AND EVERYTHING',' ') P
,regexp_replace('SUPER SECRET PASS PHRASE',' ') K
from dual)
connect by level <= length(P)))
)
select regexp_replace(max(sys_connect_by_path(decode(mod(rn,5),0,cy||' ',cy),'-')),'-','') CYPHERTEXT
from (select row_number() over (order by L) rn, cy  from t)
connect by rn = prior rn + 1
/

```

The splitting into blocks of 5 characters is just a bit of icing traditionally spread over substitution ciphers like this.

Any thoughts?

[Mod-Edit: Frank inserted correct URL to Wikipedia]

[Updated on: Fri, 16 April 2010 00:50] by Moderator

Report message to a moderator

Re: New Puzzle topic: Vigenere Cipher [message #451626 is a reply to message #451625] Thu, 15 April 2010 15:57
 Aussiedev Messages: 4Registered: March 2010 Location: Australia Junior Member
also, the corresponding decrypt... this was a pain because oracle's mod function doesn't work the way it should:

```select regexp_replace(max(sys_connect_by_path(P,'-')),'-','') plaintext
from (select row_number() over (order by L) rn, P  from
(select chr(mod(26 - (ascii(substr(K,mod(level,length(K)),1)) -65) + (ascii(substr(cy,level,1)) -65),26) + 65) P
,level L
from (select regexp_replace('KIBIJ MTGIW XRRWL ILOTH VGVPF CQGQE XTXNA FVAYE EISHX RXGJN ZJXIH WMCPM EJWWU CHVNI TPXAX NY',' ') cy
,regexp_replace('SUPER SECRET PASS PHRASE',' ') K
from dual)
connect by level <= length(cy)))
connect by rn = prior rn + 1
```
Re: New Puzzle topic: Vigenere Cipher [message #451644 is a reply to message #451626] Thu, 15 April 2010 22:53
 Michel Cadot Messages: 64620Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Quote:
this was a pain because oracle's mod function doesn't work the way it should:

Please, expand this a little bit.

Regards
Michel
Re: New Puzzle topic: Vigenere Cipher [message #451698 is a reply to message #451625] Fri, 16 April 2010 04:01
 Aussiedev Messages: 4Registered: March 2010 Location: Australia Junior Member
well, normally the decrypt would be:

```Plaintext = mod(key-cyphertext,26)
```

but oracle's mod function doesn't work the same way as standard modulus (download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions088.htm#SQLRF00668)

so with K='D'(3) and C='X'(24) the plain text should = 'U'(20)

but oracle gives the following result:

```SQL > select mod(-17,26) from dual;

MOD(-17,26)
-----------
-17
```

oracle seems to wrap regardless of the negative sign:
```SQL > select mod(-28,26) from dual;

MOD(-28,26)
-----------
-2
```

edit: actually there was a bug in the decrypt statement above:

when mod(level,length(K)) = 0 then we need to select the last character of the key, currently it is selecting the first character of the key twice for each cycle through it

(sorry, I can no longer edit the original post)

```select regexp_replace(max(sys_connect_by_path(P,'-')),'-','') plaintext
from (select row_number() over (order by L) rn, P  from
(select chr(mod(26 - (ascii(substr(K,decode(mod(level,length(K)),0,length(K),mod(level,length(K))),1)) -65) + (ascii(substr(cy,level,1)) -65),26) + 65) P
,level L
from (select upper(regexp_replace('&CYPHERTEXT',' ')) cy
,upper(regexp_replace('&KEY',' ')) K
from dual)
connect by level <= length(cy)))
connect by rn = prior rn + 1
```

[Updated on: Fri, 16 April 2010 06:21]

Report message to a moderator

 Previous Topic: Static and Dynamic Analysis Next Topic: ip adress
Goto Forum:

Current Time: Wed Mar 29 04:03:26 CDT 2017

Total time taken to generate the page: 0.14910 seconds