Recursive Pipelined function in PL/SQL

I've always thought pipelined functions were awesome, even more awesome though is a recursive pipelined function. For me, each function iteration calls a web service that returns xml describing a list of files and folders in a given directory. I then call the function on each folder.

The awesomeness:
Piping piped rows.

To use this you will need to replace <TABLE_SOURCE> with the function you use to get your table. For me, I make a web service call and throw the result into an XMLtable, and select from that.

 FUNCTION LIST_PIPE
(
    LIST_URL VARCHAR2
    ,RECURSIVE VARCHAR DEFAULT 'false'
) RETURN LIST_TABLE_TYPE
    PIPELINED IS
    
BEGIN
    
    FOR I IN (SELECT A.ITEM AS ITEM
       ,A.IS_FOLDER AS IS_FOLDER
      FROM <TABLE_SOURCE> A )
    LOOP
       PIPE ROW(LIST_ROW_TYPE(I.ITEM, I.IS_FOLDER));
       IF RECURSIVE = 'true' AND I.IS_FOLDER = 'true'
       THEN
          FOR J IN (SELECT * FROM TABLE(LIST_PIPE(I.ITEM, RECURSIVE)))
          LOOP
             PIPE ROW(LIST_ROW_TYPE(J.ITEM, J.IS_FOLDER));
          END LOOP;
       END IF;
    END LOOP;

END LIST_PIPE;

You will need the following two global types.

CREATE OR REPLACE TYPE LIST_ROW_TYPE AS OBJECT
(
              ITEM          VARCHAR2(4000),
              IS_FOLDER     VARCHAR2(10)
)

..and..

create or replace type LIST_TABLE_TYPE as TABLE OF LIST_ROW_TYPE

Deleting files from SharePoint (PL/SQL)

Developing with SharePoint web services is just painful sometimes. In order to delete a file, you must write a CAML (Collaborative Application Markup Language) batch with a delete command in it. The odd thing is you must include the absolute location and the ID of the file. Its odd because the ID just has to be any valid ID of a file, not actually the file you want to delete. Can you imagine the BS code logic behind this? This requires the developer to call the the web service once more to lookup the ID (unless you use a statically typed ID of a known persistent file). The call you must make to lookup this ID also retrieves the file contents, ugg, too much extra work when you obviously don't need it, MS.

In order to retrieve the file ID, I use the 'GetItem' call to the 'Copy' web service. If all goes well you well be returned a large set of attribute "Fields" and the file contents in base64, all we need is the "ID" so use something like the following, replace 'response' with the xml(in clob format) you are returned from the web service. ID can also be found on SharePoint if you enable the ID column for the view.

ID := XMLTYPE('response')
     .EXTRACT('//Fields/FieldInformation[@DisplayName="ID"]/@Value'
     ,'xmlns="http://schemas.microsoft.com/sharepoint/soap/')
     .GETSTRINGVAL();

The SOAP web service call to delete a file. The 'UpdateListItems' method in the 'Lists' web service.
Be sure to enter your 'list_name', 'file_id', and 'http://path_to_file'. Be sure to remove any extra whitespace between the batch tags "because CAML is strongly typed" (because MS cant handle squat).

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:soap="http://schemas.microsoft.com/sharepoint/soap/">
   <soapenv:Header/>
   <soapenv:Body>
      <soap:UpdateListItems>
         <soap:listName>list_name</soap:listName>
         <soap:updates>
       <Batch OnError="Continue" 
          ListVersion="1" 
          ViewName="">
         <Method ID="1" Cmd="Delete">
           <Field Name="ID">file_id</Field>
           <Field Name="FileRef">http://path_to_file</Field>
         </Method>
       </Batch>
    </soap:updates>
      </soap:UpdateListItems>
   </soapenv:Body>
</soapenv:Envelope>

Selecting web service methods out of a WSDL using oracle SQL

I was tasked with listing all the methods for all web services that our Sharepoint instance provides. This is about 35 different web services, each with anywhere between 1 and forty methods, so looking up each WSDL and copy/pasting each method would take forever and us programmers are too lazy for repetitive tasks.

I use java to make the call because we have to deal with NTLM authentication, if you don't, there is almost too much information on the web about sending a "GET" using the UTL_HTTP package from oracle. You will be returned XML data describing the web service functionality. If you are cheap, direct your browser to the WSDL and just copy/paste the WSDL xml into the query.

The standard location of the WSDL is http(s)://host.com/pathToService/webservice.asmx?WSDL

To use this query, place your WSDL xml in the placeholder "< WSDL_XML >" below.
The result will be a table of methods.

SELECT SUBSTR(A.METHOD, REGEXP_INSTR(A.METHOD, ':') + 1)
FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'http://schemas.xmlsoap.org/wsdl/')
             ,'$d/*:definitions/*:message' PASSING XMLTYPE(< WSDL_XML >) 
             AS "d" COLUMNS METHOD VARCHAR2(4000) PATH 'part/@element') A
WHERE A.METHOD NOT LIKE '%Response'

Converting to base-64 CLOB in PL/SQL

Recently I have been working on a project to send and retrieve files from a Microsoft SharePoint site using it's web service. The file contents are base-64 encoded and transmitted in a SOAP envelope.

Problem:
Encode a clob to base-64.

Trouble:
Oracle provides a utility package (UTL_ENCODE) that will encode VARCHAR2s to base-64, so we must chunk up the clob and convert it piece at a time. What size chunk turns out to be the problem.

Confusing:
The data density between ascii and base64 is different, meaning it takes more bits to store base64 as ascii then ascii as ascii. In PL/SQL, all base64 transformations take place in the RAW datatype which can only be an even number of characters (two characters represent a byte).

What this means:
If we take a chunk of data and convert it to base64 and the result doesn't fit perfectly into the RAW, the data will be padded with zeros. Not a problem if there is only one chunk, but if you concatenate several of these base64 chunks, you will have extra zeros thrown in seemingly randomly.

How I fixed this:
I tried a handful of chunk-sizes before I made the computer figure it out for me. I took a clob that could be converted in one chunk (less than the size of a VARCHAR2(4000)) and saved the result so that I had initial clob and my desired output. Then I wrote my loop the would chunk and convert the initial clob given a chuck-size. I stuck that in another loop that would test every chunk-size between 1 and half the clob plus 1. The procedure would print out whether the chunk size worked. Turns out 48, 96, 144, 192, ... showed up as 'Success'. Multiples of 48, I would not have guessed.

So here is the function, pass it a clob and you shall be returned a clob in base64.

FUNCTION ENCODE64_CLOB(IN_CLOB CLOB) RETURN CLOB IS
       --The chunk size must be a multiple of 48
       CHUNKSIZE INTEGER := 576;
       PLACE   INTEGER := 1;
       FILE_SIZE INTEGER;
       TEMP_CHUNK VARCHAR(4000);
       OUT_CLOB  CLOB;
BEGIN
       FILE_SIZE := LENGTH(IN_CLOB);
       WHILE (PLACE <= FILE_SIZE)
       LOOP
              TEMP_CHUNK := SUBSTR(IN_CLOB, PLACE, CHUNKSIZE);
              OUT_CLOB := OUT_CLOB 
              || UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(TEMP_CHUNK)));
              PLACE := PLACE + CHUNKSIZE;
       END LOOP;
       RETURN OUT_CLOB;
END;

Building SOAP Envelopes in PL/SQL

There are multiple popular ways of communicating with a web service. While my co-worked uses REST (Representational State Transfer) for communicating with a Google API, Im using SOAP (Simple Object Access Protocol) to interact with a SQL Server Reporting Service (SSRS) web service.

Context:
Say I want to pull a list of all objects that exist in the home directory of my SSRS instance. I would look at the server's WSDL (http://location_of_web_service.asmx?WSDL) and find choose the method ListChildren (Side Note: soapUI is an awesome open-source tool for this kind of thing, goolge it).

The WSDL tells us that the 'ListChildren' method takes the parameters 'Item' and 'Recursive.' Item is the folder that you list and Recursive (true/false) is whether or not you want to recursively print the sub-folders. The only way you can figure this out is trial and error, or if you dare, Microsoft documentation.

Parameters:
METHOD: Your SOAP action, example: ListChildren.
PARAMS: Tad trickier. I concatonate the params together then break them apart later due to the fact there may by zero or many. Seperate the parameter name and value with a '$' and multiple name-value pairs with a '^'.

PARAMS example: "parameter1$value1^parameter2$value2"
will yield:
"<namespace:parameter1>value1</namespace:parameter1>
<namespace:parameter2>value2</namespace:parameter2>"

How to use this code:
To use this you will need to set your own envelope_tag and namespace as variables, mine are "rep" and "soap," respectively.

FUNCTION BUILD_ENVELOPE
(
     METHOD VARCHAR2
     ,PARAMS CLOB
          
) RETURN CLOB
     PARALLEL_ENABLE is
     ENVELOPE  CLOB;
     PARAM_SECTION CLOB;
     PARAM_REG VARCHAR2(10) := '[^^]+';
     VALUE_REG VARCHAR2(10) := '[^$]+';

BEGIN
     --Add namespaces to envelope. Add an empty header.
     ENVELOPE := '<' || ENVELOPE_TAG || ':Envelope xmlns:' || ENVELOPE_TAG || '="http://schemas.xmlsoap.org/soap/envelope/" ' 
         || 'xmlns:' || NAMESPACE || '="http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices">' 
         || '<' || ENVELOPE_TAG || ':Header>' 
         || '</' || ENVELOPE_TAG || ':Header>' 
         || '<' || ENVELOPE_TAG || ':Body>' 
         || '<' || NAMESPACE || ':' || METHOD || '>';
      
     --each method can take zero or many parameters, this function will take a list as "<param1>$<value1>^<param2>$<value2>^..."
     IF TRIM(PARAMS) IS NOT NULL
     THEN
          FOR I IN 1 .. REGEXP_COUNT(PARAMS, PARAM_REG)
          LOOP
              --Select out the parameter value pair.
              PARAM_SECTION := TRIM(REGEXP_SUBSTR(PARAMS, PARAM_REG, 1, I));
              IF (PARAM_SECTION) IS NOT NULL
              THEN
                   BEGIN  
                        -- Concatonate the parameter-value pair as <namespace:param>value<namespace:param>
                        ENVELOPE := ENVELOPE 
                        || '<' || NAMESPACE || ':' || TRIM(REGEXP_SUBSTR(PARAM_SECTION, VALUE_REG, 1, 1)) || '>' 
                        || TRIM(REGEXP_SUBSTR(PARAM_SECTION, VALUE_REG, 1, 2)) 
                        || '</' || NAMESPACE || ':' || TRIM(REGEXP_SUBSTR(PARAM_SECTION, VALUE_REG, 1, 1)) || '>';
                   END;
              END IF;
          END LOOP;
     END IF;
          
     -- Close the envelope
     ENVELOPE := ENVELOPE 
         || '</' || NAMESPACE || ':' || METHOD || '>' 
         || '</' || ENVELOPE_TAG || ':Body>' 
         || '</' || ENVELOPE_TAG || ':Envelope>';
     RETURN(ENVELOPE);
EXCEPTION
     WHEN OTHERS THEN
          LOG_TO_TABLE('BUILD_ENVELOPE'
              ,'Exception thrown on action: ' || METHOD || ', params: ' || PARAMS || ' error: ' || SQLERRM , 'E');
          RETURN NULL;
          
END BUILD_ENVELOPE;

Tokenizing in Java

So Im playing with the idea of making a compiler built on Java. All reasons why that is a horrible idea aside, I want to.

Problem:
Tokenize a dirty source file.
Ive worked with bison and flex for C++ and know the basics of how they work, I plan on mimicking that functionality.

Situation:
We have a 'Source string' which is the code we are tokenizing(analyzing and breaking down into data thats easier to work with) and we have regex-token pairs. The regex determines what part of the source the token represents.

Boring:
I have a file of regex and the tokens they produce. Just to make some progress I looped through the regex-token pairs, replaced all the regex matches with their respective token's and was left with with a string of tokens. My tokens however where just a few characters surrounded by sideways-carrots (yes thats what I call them) with the literals added something like "<string_constant value="test">".

Fun:
All I can say is that previous part depressed me by how boring it was. So lets have some fun. Instead of repeatedly having to parse text, lets throw each token's data into an object as they're matched by the regex. If we have a collection of these token objects they need to be sorted by their occurrence in the source string, not by the order by which the regex evaluates them. Instead of replacing the token with a string, lets remove the regex and replace it with a CHAR, say, #01 (not null but also not an actual character). Then we take that token object and and put in a map where the key is the memory location of the dummy char. As the source string gets evaluated is should break down into a string of #01 characters. After that, all you have to do is iterate through the string, for each character, pull out the corresponding token object from the map and add it to you're favorite sorted list (mine's ArrayList). BAM!

Although, this is Java, and Java doesn't like to share addresses. I think its time to bust out the java.misc.Unsafe.

Type Matching between PL/SQL and Java

PL/SQL is great for some things, but built for a somewhat specific environment. Lately, I've needed to get some extra functionality and have turned to Java for things.

Problem:
Call a Java method from PL/SQL that will in turn make a call to a web service and return large amount of data that can then be used in PL/SQL.

Initial Problem Analysis:
-The amount of data that will be returned from Java is greater than a PL/SQL VARCAHR2, so we must use a clob.
-The java.sql.clob constructor is protected in Java because it was meant to be used in conjunction with a java.sql.ResultSet. This stumped me for a while, but I ended up passing an initialized clob from PL/SQL that I could then write to and pass back. In the end however, I ended up needing to pass so much data to the web service that I used the clob is input as well as output.

Getting started.
First off, however PL/SQL is an object-oriented programming language, you will not be initializing a Java class in your PL/SQL code, this was odd to me at first due to my experience being mostly with Java and C++. Without access to the constructor, variables you want to use in multiple methods must be passed in the single method you call from PL/SQL and assigned to static variables that are in the class scope. This is a small inconvenience here but could be huge in other cases.

Basic Structure:

create or replace and compile java source named CLOB_TEST as

import java.sql.Clob;

public class clobTest {

  public static Clob getResponse(Clob p_clob) throws Exception {
    // pull the string from the clob
    String stringAsClob = p_clob.getSubString(1, (int) p_clob.length());

    // make the string clean so you can write back to it.
    p_clob.truncate(0);
    
    // ...
    //mess with the string
    // ...
    
    //stuff the string back into the clob
    p_clob.setString(1, stringAsClob);
    return p_clob;
  }
}

You will need a function like this in Oracle that you will be calling in you PL/SQl code.

CREATE OR REPLACE FUNCTION JAVA_CLOB_TEST(P_CLOB IN CLOB) RETURN CLOB AS
              LANGUAGE JAVA NAME 'clobTest.getResponse( java.sql.Clob ) 
              return java.sql.Clob';

And finally, your PL/SQL code will look something like this.

DECLARE
              TEMP_CLOB CLOB;
BEGIN
              --Even if you dont need to pass data in the clob, you must initialize it.
              TEMP_CLOB := 'input data';
              TEMP_CLOB := JAVA_CLOB_TEST(P_CLOB => TEMP_CLOB);
END;

NTLM Authentication in PL/SQL -- Part 2 Type1 Message

The first of three NTLM tokens in the authentication handshake is called a Type 1 message, easy enough. In standard practice, the client will make a request and the server will return a packet that has a status of 401 (unauthorized) and checks the headers for supported authentication methods. If the client finds a header named “WWW-Authenticate” with the value “NTLM” it proceeds to build the first part of the NTLM handshake.

A Type1 message is sent from the client to the server as a header in another HTTP request. The Type1 message contains information on the domain and workstation along with flags denoting the version of NTLM supported and content type.

Update:
I have not been able to get authenticated via this method, I can build the NTLM tokens seemingly perfectly, yet no success. I think the problem is with network permissions from my oracle DB to the called web service.

The following is a function that will build a Type 1 message. It uses other simpler functions such a SWAP_ENDIAN and DEC2HEX that I will post below.

FUNCTION BUILD_TYPE1
(
DOMAIN      VARCHAR2
,WORKSTATION VARCHAR2
) RETURN VARCHAR2 IS
SIGNATURE        VARCHAR2(1000);
INDICATOR        VARCHAR2(1000);
FLAGS            VARCHAR2(1000);
DOMAIN_BUFF      VARCHAR2(1000);
WORKSTATION_BUFF VARCHAR2(1000);
OS_VERSION       VARCHAR2(1000);
WORKSTATION_STR  VARCHAR2(1000);
DOMAIN_STR       VARCHAR2(1000);

HEX_RESULT  VARCHAR2(4000);
NTLM_RESULT VARCHAR2(4000);
SIGNATURE_R RAW(16);
NTLM_TYPE   NUMBER := 1;
-- use default flags                            
NTLM_FLAGS VARCHAR2(50) := '06520000';

-- Bytes used for specific parts of type1 message, used to find offsets. 1 Byte = 2 CHARs 
NTLM_SIGNATURE_LENGTH  NUMBER := 16;
NTLM_TYPE_LENGTH       NUMBER := 8;
NTLM_FLAG_LENGTH       NUMBER := 8;
NTLM_BUFFSIZE_LENGTH   NUMBER := 4;
NTLM_BUFFOFFSET_LENGTH NUMBER := 8;

DOMAIN_LENGTH      INTEGER;
WORKSTATION_LENGTH INTEGER;
DOMAIN_OFFSET      INTEGER;
WORKSTATION_OFFSET INTEGER;

BEGIN

DOMAIN_LENGTH := LENGTH(DOMAIN);
WORKSTATION_LENGTH := LENGTH(WORKSTATION);

DOMAIN_OFFSET := (NTLM_SIGNATURE_LENGTH 
+ NTLM_TYPE_LENGTH 
+ NTLM_FLAG_LENGTH 
+ NTLM_BUFFSIZE_LENGTH * 4 
+ NTLM_BUFFOFFSET_LENGTH * 2) / 2 
+ WORKSTATION_LENGTH;

WORKSTATION_OFFSET := (NTLM_SIGNATURE_LENGTH 
+ NTLM_TYPE_LENGTH 
+ NTLM_FLAG_LENGTH 
+ NTLM_BUFFSIZE_LENGTH * 4 
+ NTLM_BUFFOFFSET_LENGTH * 2) / 2;

--signature
SIGNATURE_R := UTL_RAW.CAST_TO_RAW('TlRMTVNTUAA');
SIGNATURE := UTL_ENCODE.BASE64_DECODE(SIGNATURE_R);

--type indicator
INDICATOR := NTLM.SWAP_ENDIAN(NTLM.DEC2HEX(NTLM_TYPE, NTLM_TYPE_LENGTH));

--flags
FLAGS := NTLM_FLAGS;

--domain buffer
DOMAIN_BUFF := NTLM.SWAP_ENDIAN(NTLM.DEC2HEX(DOMAIN_LENGTH, NTLM_BUFFSIZE_LENGTH)) ||
NTLM.SWAP_ENDIAN(NTLM.DEC2HEX(LENGTH(DOMAIN), NTLM_BUFFSIZE_LENGTH)) ||
NTLM.SWAP_ENDIAN(NTLM.DEC2HEX(DOMAIN_OFFSET, NTLM_BUFFOFFSET_LENGTH));

--UTL_RAW.CAST_FROM_NUMBER
WORKSTATION_BUFF := NTLM.SWAP_ENDIAN(NTLM.DEC2HEX(WORKSTATION_LENGTH, NTLM_BUFFSIZE_LENGTH)) ||
NTLM.SWAP_ENDIAN(NTLM.DEC2HEX(LENGTH(WORKSTATION), NTLM_BUFFSIZE_LENGTH)) ||
NTLM.SWAP_ENDIAN(NTLM.DEC2HEX(WORKSTATION_OFFSET, NTLM_BUFFOFFSET_LENGTH));

--workstation
WORKSTATION_STR := UTL_RAW.CAST_TO_RAW(UPPER(WORKSTATION));

--domain
DOMAIN_STR := UTL_RAW.CAST_TO_RAW(UPPER(DOMAIN));

HEX_RESULT := SIGNATURE 
|| INDICATOR 
|| FLAGS 
|| DOMAIN_BUFF 
|| WORKSTATION_BUFF 
|| NVL(OS_VERSION, '') 
|| NVL(WORKSTATION_STR, '') 
||NVL(DOMAIN_STR, '');

NTLM_RESULT := UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(HEX_RESULT));

RETURN NTLM_RESULT;

END BUILD_TYPE1;

The following is the makings of an anonymous block that will call the preceding function. It is incomplete because you need to build your own SOAP message to send as the packet contents and of course use your own credentials, workstation, domain, and action.

DECLARE

USERNAME VARCHAR2(100) := 'username';
PASSWORD VARCHAR2(100) := 'password';
--this is the workstation that you are calling.
CALLING_WORKSTATION VARCHAR2(100) := 'workstation';
--this is the domain that your account belongs to.
DOMAIN    VARCHAR2(100) := 'domain';
USERAGENT VARCHAR2(1000) := 'Mozilla/4.0';
URL       VARCHAR2(2000) := 'http://url_of_host.asmx';

TYPE1 VARCHAR2(4000);
REQ   UTL_HTTP.REQ;
RESP  UTL_HTTP.RESP;
--I have a function in my package that I will post later that builds my soap 
--message but that is a different topic.
ENVELOPE VARCHAR2(4000);
--check your server WSDL for the correct action for your SOAP call.
ACTION       VARCHAR2(4000) := 'http://action';
ENVELOPE_TAG VARCHAR2(50) := 'soap';

--for use in printing headers
NAME  VARCHAR2(1000);
VALUE VARCHAR2(4000);

BEGIN
--This is where we will call the BUILD_TYPE1 function, I have mine in a PL/SQL package called NTLM
TYPE1 := 'NTLM ' || NTLM.BUILD_TYPE1(DOMAIN => DOMAIN, WORKSTATION => CALLING_WORKSTATION);

REQ := UTL_HTTP.BEGIN_REQUEST(URL, 'POST', UTL_HTTP.HTTP_VERSION_1_1);

UTL_HTTP.SET_HEADER(REQ, 'Accept-Encoding', 'gzip,deflate');
UTL_HTTP.SET_HEADER(REQ, 'Content-Type', 'text/xml;charset=UTF-8');
UTL_HTTP.SET_HEADER(REQ, 'SOAPAction', ACTION);
UTL_HTTP.SET_HEADER(REQ, 'User-Agent', USERAGENT);
UTL_HTTP.SET_HEADER(REQ, 'Authorization', TYPE1);
UTL_HTTP.SET_HEADER(REQ, 'Content-Length', LENGTH(ENVELOPE));

--send and recieve
UTL_HTTP.WRITE_TEXT(REQ, ENVELOPE);
RESP := UTL_HTTP.GET_RESPONSE(REQ);

DBMS_OUTPUT.PUT_LINE('HTTP response status code: ' || RESP.STATUS_CODE);
DBMS_OUTPUT.PUT_LINE('HTTP response reason phrase: ' || RESP.REASON_PHRASE);

--at this point you will still be unauthorized, but should have a type 2 message in the response header

--print response headers
FOR I IN 1 .. UTL_HTTP.GET_HEADER_COUNT(RESP)
LOOP
UTL_HTTP.GET_HEADER(RESP, I, NAME, VALUE);
DBMS_OUTPUT.PUT_LINE(NAME || ': ' || VALUE);
END LOOP;

--close response
UTL_HTTP.END_RESPONSE(RESP);
END;

The following function treats the input string as a string of bytes, and swaps to and from big and little endian using a chunksize of two bytes.

FUNCTION SWAP_ENDIAN(INITIAL_STR VARCHAR2) RETURN VARCHAR2 IS
RESULT_STR VARCHAR2(1000);
PLACE      INTEGER := 1;
BEGIN
IF MOD(LENGTH(INITIAL_STR), 2) <> 0
THEN
--error
RETURN NULL;
END IF;

WHILE (PLACE < LENGTH(INITIAL_STR))
LOOP
RESULT_STR := SUBSTR(INITIAL_STR, PLACE, 2) || RESULT_STR;
PLACE := PLACE + 2;
END LOOP;
RETURN RESULT_STR;

EXCEPTION
WHEN OTHERS THEN
--error
RETURN NULL;
END SWAP_ENDIAN;

The following function originated from the following location:
http://www.orafaq.com/wiki/Hexadecimal
I edited it slightly for use in this project so the return value would be certain size.

FUNCTION DEC2HEX
(
N        IN NUMBER
,STR_SIZE IN NUMBER
) RETURN VARCHAR2 IS
HEXVAL   VARCHAR2(64);
N2       NUMBER := N;
DIGIT    NUMBER;
HEXDIGIT CHAR;
BEGIN
WHILE (N2 > 0)
LOOP
DIGIT := MOD(N2, 16);
IF DIGIT > 9
THEN
HEXDIGIT := CHR(ASCII('A') + DIGIT - 10);
ELSE
HEXDIGIT := TO_CHAR(DIGIT);
END IF;
HEXVAL := HEXDIGIT || HEXVAL;
N2 := TRUNC(N2 / 16);
END LOOP;
HEXVAL := LPAD(HEXVAL, STR_SIZE, '0');

RETURN HEXVAL;
END DEC2HEX;

A Little Something on NTLM Authentication

At work I've been tasked with creating a channel of communication between an Oracle database and an IIS web service. Turns out Microsoft doesn't like to play fair with, well, anyone but Microsoft. I wrote up this blob to describe the awesome fun it will be to replicate this process.

NTLM Authentication
NTLM is a proprietary network authentication protocol that is implemented in various Windows network services. The standard NTLM authentication consists of three messages that will be used in the authentication handshake. Although there are many variations of the NTLM authentication, for this explanation we will be using NTLMv1 to authenticate packets containing SOAP content sent to SQL Server Reporting Services, a server using IIS.

The overall procedure is as follows:
1) The client sends a HTTP request with SOAP content attached.

2) Since the request does not provide the required authentication, the server responds with supported authentication methods in the response header.

3) The client receives this packet that has a status of 401 (unauthorized) and checks the headers for supported authentication methods. If the client finds a header named “WWW-Authenticate” with the value “NTLM” it proceeds to build the first part of the NTLM handshake. A Type1 message is sent from the client to the server as a header in another HTTP request. The Type1 message contains information on the domain and workstation along with flags denoting the version of NTLM supported and content type.

4) The server receives this packet, decodes the type1 message and responds with a packet with 401 status and a Type2 message. A Type2 message contains information about the network environment such as server and domain NETBIOS and DNS. More importantly, the Type2 message contains an 8-byte challenge which will be used by the client to prove that it knows the password without actually sending it across the network.

5) The client takes the challenge from the Type2 message and builds the Type3 message, the final part of the handshake. This is the first time that the username and password of the service are used. The username is not obfuscated, however the password is. The password is encrypted to create what is called the LM response.

Creating the LM response:
a. The password in uppercase is null padded to 14 bytes then split in two halves.
b. For each 7-byte half, after every 7th bit a parity bit is added, expanding the overall length to 8 bytes.
c. Each half is then used as a key to DES-encrypt the ASCII string “KGS!@#$%”.
d. The two cipher text values are then concatenated to create a 16-byte LM hash.
e. This hash is then null padded to 21 bytes and split into three 7-byte parts.
f. For each 7-byte part, parity is added expanding the overall length of each part to 8 bytes.
g. Using each of the three parts as individual keys, the challenge from the Type2 message is DES-encrypted.
h. Concatenate the three resulting cipher text pieces into one to create your LM response.

6) After the server receives the Type3 message and checks the username and password against its database, it returns a packet with status 200 (OK) and the requested content attached.

NTLM Token Structure:
The NTLM messages that at passed in HTTP packets are base 64. After casting the varchar token to raw and decoding it from base 64, you have a hex result that consists of both a fixed and variable length part. The fixed length part contains NTLM standard variables and references to where the viable length variables can be found. Strings are ASCII and numbers are little-endian (least significant first). The hex starts off with the signature “NTLMSSP” and then the type being 1-3. The rest of the content varies between types and is out of the scope of this document but is best described here: http://davenport.sourceforge.net/ntlm.html.

NTLM Authentication in PL/SQL -- Part 1 Project Overview

Goal:
Be able to call a SQL Server Web Service (SSRS) that used NTLM authentication from a PL/SQL package. End goal is to communicate from an Oracle 11g Database to SQL Server Reporting Server using SOAP (Simple Object Access Protocol) to return data that we cant currently get due to the expensive driver required to create a DB link between the Oracle and SQL Server.

Problem:
NTLM is a Microsoft proprietary authentication system and doesn't play nice with PL/SQL developers. Currently (11g r2) Oracle does not offer support for NTLM authentication, in Java however, that is a different story.

Steps I've taken:
-Use SoapUI to send requests to the web service. SoapUI is a handy program that provides an XML template for creating your SOAP requests and then displays the response in cleanly formatted XML. SoapUI also supports various authentication methods including Basic Authentication (plain text password exchange) and NTLM v1, which is what the web service I'm working with requires.

-Use Wireshark (a great open-source packet analyzer) to observe the NTLM authentication process and get an understanding of what needs to be done. Wireshark can decode the NTLM auth tokens pretty well but I ran into some quirks, mostly incorrectly showing the location in the packet where certain information was derived from. Despite some initial frustration due to skewed data, Wireshark was absolutely instrumental in this project.

-Start the long tedious process of replicating the NTLM auth tokens. There are three important tokens in this process that make up the authentication 'handshake.' The client sends the first token with basic information about itself excluding the password, the servers responds with a token with a challenge (a seemingly random 8-byte hex string), and then the client takes obfuscates the password with this challenge and send the third token back with it. This process requires lots of low-level programming, and we will be attempting in a very high-level language, PL/SQL.

-Fail. Yep, after a week of programming and testing, I can replicate the NTLM auth tokens down to the BIT. Yet I still get an 'Unauthorized' response. Bummer, I fully believe that this can work but I am getting held up by either a network firewall that is protecting the Oracle DB and not protecting my desktop, or the Microsoft web service simply refuses to accept requests from a Linux box (where the DB resides) but will from my Windows 7 desktop. I think this needs to revisited but in order to keep this project moving forward I will try switching to Java to for the authentication.

-Success! From PL/SQL I call a stored Java source and pass it my credentials along with the soap message I want to sent the web service. Java handles the authentication by creating an SSL connection and java.net.Authenticator does the rest of the NTLM magic for you. It's too bad really how simple it is in Java.

Note: Some responses from web services are larger then a PL/SQL VARCHAR2 (32k) for this I used a clob. The constructor for a clob is protected in Java because they want you to use it in conjunction with a java.sql.ResultSet. In order to overcome this problem I passed an initialized clob from PL/SQL to Java, wrote in it, then passed it back.