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
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 messageGo to next message
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

Report message to a moderator

Re: New Puzzle topic: Vigenere Cipher [message #451644 is a reply to message #451626] Thu, 15 April 2010 22:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account 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

Report message to a moderator

Re: New Puzzle topic: Vigenere Cipher [message #451698 is a reply to message #451625] Fri, 16 April 2010 04:01 Go to previous message
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

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

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Nov 26 04:04:04 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.