Home » Other » General » New Puzzle topic: Vigenere Cipher
New Puzzle topic: Vigenere Cipher [message #451625] |
Thu, 15 April 2010 15:07 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Aussiedev
Messages: 4 Registered: 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
start with 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
start with 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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Aussiedev
Messages: 4 Registered: 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
start with rn = 1
|
|
|
|
Re: New Puzzle topic: Vigenere Cipher [message #451698 is a reply to message #451625] |
Fri, 16 April 2010 04:01 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
Aussiedev
Messages: 4 Registered: 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
start with rn = 1
[Updated on: Fri, 16 April 2010 06:21] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Sat Sep 07 18:45:07 CDT 2024
|