Swiss Army Oracle

So the other day I was trying to debug issues with an SMTP server — it seemed to allow me to send email when I connected via a WiFi connection, but not from my phone over EVDO.  That seemed odd to me — why would the transport or network connection make a difference?

So I started to dig into SMTP relaying permissions and learned a fair amount about how you can configure SMTP to allow relaying only from specific IP addresses (reference here).

I tested it by telnet-ing to the SMTP server using each network (and a copy of PocketPuTTY for my phone — I had to find a version that allowed specific ports) — and that seemed to validate that from certain IPs I could use the SMTP server to send mail, but not from EVDO.  All of this was done without validating myself to the SMTP server.

After my anonymous tests seemed to validate the selective relaying, I thought I’d try tests with authentication.  I did some research and learned that my SMTP server supports SMTP AUTH using the AUTH LOGIN method (here).

(I know, what does all of this have to do with Oracle?  Patience — I’m getting there).

On the page about using AUTH LOGIN, it mentions that SMTP server exchanges username and password information using BASE64 encoded strings.  So, when you say AUTH LOGIN, it says “VXNlcm5hbWU6″, which is Vogon for “Username” :-)

You respond with your username in Vogon, er, BASE64 and the server comes back with “UGFzc3dvcmQ6″ (“Password”).

(Why it just doesn’t use Username and Password instead of VXNlcm5hbWU6 and UGFzc3dvcmQ6 is beyond me — especially if they’re consistent anyway.  Why not speak French?).

Anyway, to test this I needed a quick way to convert my username and passwords into BASE64 so I could interact with the SMTP server via telnet.

I could have gone here to do this online, but since I’m an Oracle guy, I just fired up SQL*Plus and typed:

select utl_raw.cast_to_varchar2(
utl_encode.base64_encode(
utl_raw.cast_to_raw(
‘superbunny@loontunes.com’
))) vogon_username
from dual;

And got back:

VOGON_USERNAME
——————————————————————————–
c3VwZXJidW5ueUBsb29udHVuZXMuY29t

A long time ago I had a developer come to me and tell me that we needed to “encode” a data field (in a table with 30,000) rows.  He proposed writing a Java program to loop through the data, retrieve it, encode it, and store it back into the database.  Since he made out that the encoding was some super special encryption that only Java could do, I saw no problem with it.  However, in a spare cycle I asked him what the super-secret Java encoding was — he said “base64″.  I was pretty sure Oracle had uuencode / decode packages, so I figured that someone had either written a base64 encoder in PL/SQL or that Oracle shipped one.  Lo and behold Oracle shipped one.  No more looping and updating — one SQL command and we were done.

I’m current writing a hypervisor for x86 VMs in PL/SQL so that I can run my old copy of Microsoft Adventure.  :-)

“You are in a maze of twisty little passages, all alike”

2 Responses to “Swiss Army Oracle”

  1. Cliff Palmer Says:

    Java is Vogon for “Pimply-faced wimp” ;)
    While you’re at it can you bet the VM to run my DOS 3.1 version of BRIEF?

    Cliff “Have you used an undocumented opcode today?” Palmer

  2. Martlark Says:

    update zzzmas.person set password =
    utl_raw.cast_to_varchar2(
    utl_encode.base64_encode(
    dbms_crypto.hash(utl_raw.cast_to_raw(upper(userid)||’bob’),2)
    )
    )

    And everyone’s password is a base64 has of bob. Yay! You beat out Tom Kyte on this one.

Leave a Reply

Posting code can be a pain. To make sure your code doesn't get eaten, you may want to pre-format it first by using HTML Encoder