I’ve been working on an Oracle package for encrypting/decrypting data using AES256. I need the package to be compatible with a separate C# implementation of AES256 I’m also working on. In the .NET AESManaged Class, the default padding mode is PKCS #7, which the Oracle DBMS_CRYPTO package does not support.

So I’ve thrown together an implementation of PKCS7 padding in PL/SQL to use on the Oracle side. Details about the PKCS7 padding are described in RFC 5652.

fn_PKCS7_Pad:

Pads the raw data out to a multiple of the block size. Block size is in bytes.

Note: If the data is already a multiple of the block size then the data is padded with one full block. This is per the RFC.

fn_PKCS7_Trim:

Function trims the padding off of the incoming raw data.

The trim function is currently making some fairly broad assumptions about the data. One assumption is that the data is in fact padded. Also it is trimming off the number of bytes dictated by the last byte without actually checking to see if the previous bytes have the same value and the correct number of pad bytes are present. When I get some time, I’ll go back and add some sanity checks.

Test Code:

Output:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

------------------------------------
IN:         AABBCC
BLOCK SIZE: 8
PADDED:     AABBCC0505050505
TRIMMED:    AABBCC
------------------------------------
------------------------------------
IN:         AABBCCDDEEFF
BLOCK SIZE: 8
PADDED:     AABBCCDDEEFF0202
TRIMMED:    AABBCCDDEEFF
------------------------------------
------------------------------------
IN:         AABBCCDDEEFFAABB
BLOCK SIZE: 8
PADDED:     AABBCCDDEEFFAABB0808080808080808
TRIMMED:    AABBCCDDEEFFAABB
------------------------------------

Full Code in Text File

I stumbled upon a setting in Google Reader today that shows the Favicons for your subscriptions. Apparently the feature has been in there forever. But it’s new to me!

I’ve been working on a C# version of a COM component our applications at work use to integrate with an Avaya predictive dialing system. While implementing the code for the communication piece, I found a quirk about the .NET IPAddress class I had not run into before.

The communication is being done with asynchronous sockets. The Socket class requires an IPEndPoint, which in turn takes an IPAddress and port (integer). Fair enough. For my purposes I just needed to connect using the IP not the host name. So the next logical step in my mind would be to new up an instance of IPAddress with the IP I’m attempting to connect to. The problem I ran into is that the IPAddress constructor doesn’t accept an IP! At least not in the Dot-decimal notation (x.x.x.x). Instead, I would need to know the byte array, int64, or long representation of the address.

Well it turns out you have to call the static Parse() method on the IPAddress class passing in the IP which in turn will return an instance of the class. Parse() takes dotted-quad IPv4 or colon-hexadecimal IPv6. It seems obvious now and only took a minute or so digging around in the MSDN documentation to figure out. Just wasn’t what I was expecting. Apparently I need to brush up on my “converting ip address to byte[]” in my head skills.

This is not intended to be a discussion if 1 actually equals 2. But has to do with a line of sql from the other day.

I had a request come in at work to disable a tab in one of our applications that showed historical data for customer accounts. My initial thought was that we would have to make a change to the application and redeploy. Pain in the arse! When it dawned on me (which possibly was Adam slapping me up the side of the head) that since the application depended on the results of a query in Oracle, I could just force the query to return no rows, effectively showing no history in the application.

So the quick fix was to disable the query by adding to the where clause “and 1=2”. So regardless what the skeptics say, in Oracle land 1 will never equal 2. So the query returns 0 rows no matter what parameters are passed in.

Fantastic! One quick after hour change (b/c of packages supporting “State”, discussion for another time), we had history disabled in the application.

So what does 1=2 actually do?

Now what was really interesting was running an explain on the query and discovering how Oracle deals with 1=2. As with most compilers, Oracle will attempt to simplify code as best it can to optimize performance.

Consider the following example in C#:

1
2
3
4
5
6
7
8
9
10
11

static void Test1()
{
  if (1 == 2)
  {
    Console.WriteLine("True Statement");
  }
  else
  {
    Console.WriteLine("False Statement");
  }
}

The IF statement is pointless since at runtime you will always end up in the ELSE. Checking the code in Reflector after compiling in Release mode shows that the optimizer catches the waste of cpu cycles and space and consolidates:

1
2
3
4

private static void Test1()
{
  Console.WriteLine("False Statement");
}

Now this change made by the compiler makes sense in this situation.

What if 1=2 is crucial?

Consider the following from Oracle:

1
2
3
4

SELECT uo.object_name,
       uo.object_type
  FROM user_objects uo
 WHERE uo.object_type = 'TABLE';

The query will return a list of table(s) owned by the user (assuming the user owns at least one table).

Adding the 1=2 condition:

1
2
3
4
5

SELECT uo.object_name,
       uo.object_type
  FROM user_objects uo
 WHERE uo.object_type = 'TABLE'
       AND 1=2;

The 1=2 will cause the query to always return 0 rows. Now of course the optimizer can’t remove the 1=2 at runtime without changing the intended results. So what does Oracle do?

Taking a look at the explain: “1=2” is translated to “NULL IS NOT NULL”

So the Resulting SQL more or less becomes:

1
2
3
4
5

SELECT uo.object_name,
       uo.object_type
  FROM user_objects uo
 WHERE uo.object_type = 'TABLE'
       AND NULL IS NOT NULL;

Brilliant!

I about fell in the floor laughing when I saw this when making the aforementioned code change. I proceeded to change my where clause to “NULL IS NOT NULL”. And look forward to the occasional laugh when a co-worker stumbles upon the code.

Now I haven’t checked to see how Oracle deals with 1=2 in a IF statement in pl/sql. I would assume it would handle it in a similar fashion as .net does. This situation would actually make a better comparison to how .NET vs Oracle deals with optimizing code.