Senthadev

{ Think. Ask Why. Do it. }


Tokenizing column value to rows in SQL

While I was working on SQL query optimization, I came across the following sql “where” clause.

Let me explain the query first. Query was fetching all the developers who are connected to the provided project ID. And the end-user can invoke the query by providing a single project ID or list of project ID's which are separated by 'semicolon'.

For example,
Search value : “proj001;proj201;proj301;proj302”

(of couse this value is escaped before sending it to database (Oracle) to execute).

select *
from developers d
where (instr(';' || :SEARCH || ';', ';' || d.projectID || ';')) > 0;

-- And the :SEARCH will contain "proj001;proj201;proj301;proj302"

How could I tune this query?

Well, if I could convert the search value ( semicolon separated project ID's) into a virtual table of rows then I could perform a join statement and fetch all the records.

I could do it in two ways.


Tokenizing the string value using substr and instr functions.

select *
from developers d ,
   ( select 
        substr(search_text, 
            decode(level, 1, 1, instr(search_text, ';', 1, level-1) +1), 
            instr(search_text, ';', 1, level) - decode(level, 1, 1, instr(search_text, ';', 1, level-1) +1) ) proj_id
      from (select :SEARCH || ';' search_text from dual)
      connect by level <= length(search_text) - length(replace(search_text, ';'))) s
 where d.proj_id = s.proj_id;


Tokenizing the string using regEx.

select *
from developers d ,
   ( select 
        regexp_substr(search_text, '[^;]+', 1, level) proj_id
     from (select :SEARCH || ';' search_text from dual)
      connect by level <= length(search_text) - length(replace(search_text, ';'))) s
 where d.proj_id = s.proj_id;


discussion..

Using Guava in Weblogic and NoSuchMethodError

Recently I was working on a prototype, servlet based web application, using Google Guava https://code.google.com/p/guava-libraries/ cache library. And used weblogic 12c to host and test the application. During the application context initialization, it stoped and displayed following error.


Caused by: java.lang.NoSuchMethodError: com.google.common.util.concurrent.MoreExecutors.sameThreadExecutor()
Lcom/google/common/util/concurrent/ListeningExecutorService


Why now? It ran without error in JBoss 7. Did I accidently changed any code, libraries, or maven build. So I checked the code again and I couldn't find any issues. Why it was failing? after some googling I figured that weblogic was loading a different version of the same class. To figure which library/class was loaded, I used below code:


System.out.println(MoreExecutors.class.getProtectionDomain().getCodeSource().ge‌​tLocation());
//And it printed different library name which was part of weblogic feature jar file.
//

Ok, so even when I packed the latest library in war(lib folder), weblogic was loading its own class since it was part of the system library.
How to override it for my application? After reading the documentation about the class loading order of weblogic, I created the weblogic.xml to use the latest package as shown below:

  
<?xml version="1.0" encoding="UTF-8"?>

<wls:weblogic-web-app
xmlns:wls="http://xmlns.oracle.com/weblogic/weblogic-web-app"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/ejb-jar_3_0.xsd
http://xmlns.oracle.com/weblogic/weblogic-web-app
http://xmlns.oracle.com/weblogic/weblogic-web-app/1.4/weblogic-web-app.xsd">

    <wls:weblogic-version>12.1.1</wls:weblogic-version>
    <wls:context-root>/</wls:context-root>
    <wls:container-descriptor>
         <wls:prefer-application-packages>
             <wls:package-name>com.google.common.*</wls:package-name>
         </wls:prefer-application-packages>
     </wls:container-descriptor>
</wls:weblogic-web-app>

  

And updated the maven to include the weblogic.xml in the build and deployed again.
Yes, it ran without error by loading the latest library from the webapp.

Since the weblogic threw the error I was able to find that it was loading an old class instead of packaged jar class. What happens when it loads an old class which doesn't throw error? it becomes a serious issue by executing old logic.


discussion..

Windows Environment SSO and Java

In windows based enterprise environment, most applications are integrated using single sign-on(SSO) functionality. What is single sign-on,
According to wikipedia:

Single sign-on (SSO) is a property of access control of multiple related, but independent software systems. With this property a user logs in once and gains access to all systems without being prompted to log in again at each of them.

Simply, single sign-on allows enterprise users to access different range of applications using their same domain credentials.

Recently I was facing a scenario where I need to access some resources programmatically using Java. Its a HTTP based resource, its protected based on windows authentication and I was using Oracle Java 6. According to following guide http://docs.oracle.com/javase/6/docs/technotes/guides/net/http-auth.html, built in http handler handles the http authentication.

I ran into problem, where I was running the developer environment using my domain account (senthadev\dev) and I have to use a different domain account(senthavde\qatest) to access the resource. It wasn't possible since java always used my developer domain account to authenticate and my account does not have access. How to solve this?

While searching for an solution, I ran into some suggestions such as to override the default library, bytecode patching etc..

Then I found an interesting project called Waffle https://github.com/dblock/waffle

Its a 100% java library which uses Windows SSPI to provide SSO funcationality. I used it to impersonate the java threads as a senthadev\qatest user and then access the http resource.

Here is the maven code to include the waffle library:

  
    <dependency>
      <groupId>com.github.dblock.waffle</groupId>
      <artifactId>waffle-jna</artifactId>
      <version>1.5</version>
    </dependency>
  

And the sample java code:


package com.senthadev.winlogon;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.URL;

import com.sun.jna.platform.win32.Advapi32Util;
import com.sun.jna.platform.win32.Win32Exception;
import com.sun.jna.platform.win32.WinBase;

import waffle.windows.auth.IWindowsAuthProvider;
import waffle.windows.auth.IWindowsIdentity;
import waffle.windows.auth.IWindowsImpersonationContext;
import waffle.windows.auth.impl.WindowsAuthProviderImpl;

public class WinLoginAuth {

    public static void main(String [] args){

        String username = "qatest";
        String password = "change_me";
        String domain = "senthadev";

        int logonType = WinBase.LOGON32_LOGON_INTERACTIVE;
        int logonProvider = WinBase.LOGON32_PROVIDER_DEFAULT;

        IWindowsAuthProvider authProvider = new WindowsAuthProviderImpl();
        IWindowsIdentity userIdentity = null;
        IWindowsImpersonationContext impoContext = null;

        try{
            userIdentity = authProvider.logonDomainUserEx(username, domain, password, logonType, logonProvider);
            System.out.println("Welcome :" + userIdentity.getFqn());
            impoContext = userIdentity.impersonate();
            System.out.println("Current thread is running as :" + Advapi32Util.getUserName());

            //Access the resource behind SSO
            URL url = new URL("https://win.senthadev.com/protected/report");
            HttpURLConnection conn = (HttpURLConnection) url.openConnection();
            conn.connect();
            //do the job as senthadev\qates
            conn.disconnect();
        }
        catch(Win32Exception winEx){
            System.out.println("Oops!! " + winEx.getMessage());
        }
        catch(Exception ex){
            System.out.println("Main Oops!! " + ex.getMessage());
        }
        finally{
            if (impoContext != null)
                impoContext.revertToSelf();

            if (userIdentity != null)
                userIdentity.dispose();
            authProvider = null;
        }

    }
}


discussion..

Generating CSR using Java and BouncyCastle API

updated on : 2013-10-22
source code in github : https://github.com/senthadev/programs/blob/master/GenerateCSR.java

This note is about generating CSR (Certificate Signing Request using PKCS#10 specification) programmatically
based on Java version “1.6.0_51” and BouncyCastle API “1.49”.

What is CSR?

According to wiki,
In public key infrastructure (PKI) systems, a certificate signing request (also CSR or certification request) is a message sent from an applicant to a certificate authority (CA) in order to apply for a digital identity certificate. The most common format for CSRs is the PKCS#10 specification.

Lets look at a scenario,

Applicant, Olav, wants to send a message to another applicant called Petter through internet. Now, Petter has to be confident that the message received from Olav is genuine and vice versa. One way to achieve this is by using the PKI. So, both of them will generate a key pair, private key and public key. Public keys are shared openly and the shared message is encrypted using their respective private keys. How will both of them share their public keys? using a third party called Certification Authority (CA) who is responsible to authenticate the users by signing their public keys. To get sign, Olav has to submit his public key to CA and this process is called CSR.

Lets get to the coding:

First, get the BouncyCastly library. I use maven to download them.

  
    <dependency>
      <groupId>org.bouncycastle</groupId>
      <artifactId>bcprov-jdk15on</artifactId>
      <version>1.49</version>
    </dependency>
    <dependency>
      <groupId>org.bouncycastle</groupId>
      <artifactId>bcpkix-jdk15on</artifactId>
      <version>1.49</version>
    </dependency>
  

There are 3 ways to load the BouncyCastle security provider. They are

  1. Static approach by updating the JRE_DIR/lib/security/java.security file.
    Append : security.provider.1=org.bouncycastle.jce.provider.BouncyCastleProvider

  2. Dynamically when the program initialized: (I use this approach)
    Provider bc = new org.bouncycastle.jce.provider.BouncyCastleProvider();
    Security.insertProviderAt(bc, 1);

  3. Directly passing the provider object to the method calls.

Generate a keyPair, using RSA algorithm, which consists of Public key and a Private key. These keys are 2048 bit in size.


KeyPairGenerator gen = KeyPairGenerator.getInstance("RSA");
gen.initialize(2048);
KeyPair pair = gen.generateKeyPair();
PrivateKey privateKey = pair.getPrivate();
PublicKey publicKey = pair.getPublic();

Create a “Distinguished Name” for the certificate. Generally its contains information about the company.


//import javax.security.auth.x500.X500Principal
X500Principal subject = new X500Principal ("C=NO, ST=Trondheim, L=Trondheim, O=Senthadev, OU=Innovation, CN=www.senthadev.com, EMAILADDRESS=senthadev@gmail.com");

Create a ContentSigner object using helper object org.bouncycastle.operator.jcajce.JcaContentSignerBuilder
Signer object is created using privateKey and “SHA1withRSA” algorithm.


//import org.bouncycastle.operator.ContentSigner
ContentSigner signGen = new JcaContentSignerBuilder("SHA1withRSA").build(privateKey);

Now we can create the CSR.


//import org.bouncycastle.pkcs.PKCS10CertificationRequest;
//import org.bouncycastle.pkcs.PKCS10CertificationRequestBuilder;
//import org.bouncycastle.pkcs.jcajce.JcaPKCS10CertificationRequestBuilder;

PKCS10CertificationRequestBuilder builder = new JcaPKCS10CertificationRequestBuilder(subject, publicKey);
PKCS10CertificationRequest csr = builder.build(signGen);

If you want to print/write to a file in openssl PEM format,


//import org.bouncycastle.openssl.PEMWriter;

OutputStreamWriter output = new OutputStreamWriter(System.out);
PEMWriter pem = new PEMWriter(output);
pem.writeObject(request);
pem.close();

Thats it.


discussion..

Oracle Sql Date Queries

How to create a date from string value?

select to_date('2013-06-27', 'yyyy-mm-dd') from dual;

How to get the first day of the month from a random date?

select trunc(sysdate, 'mon') from dual;
select trunc(to_date('2013-06-27', 'yyyy-mm-dd'), 'mon') from dual;

this will return 2013-06-01


How to get all the dates, in a particular month from a random date, into a table of rows?

select (trunc(sysdate, 'mon') + (level-1)) all_dates
from dual
connect by level <= to_number(to_char(LAST_DAY(sysdate), 'dd'));

For example, if the sysdate returns 2013-06-27, then result would be 30 records starting from 2013-06-01..2013-06-30


How to get start and end dates of all the weeks, starting from monday, of a particular month for a random date?

select 
    weeks.cdate monday,
    weeks.cdate + 6 sunday
from (
    select trunc(trunc(sysdate, 'mon') + ((level-1)*7), 'Day') + 1 as cdate
    from dual
    connect by level <= to_number(to_char(last_day(sysdate), 'W'))
) weeks
where to_char(weeks.cdate, 'MM') = to_char(sysdate, 'MM')

For example, if the sysdate returns 2013-06-17, then the result:

mondaysunday
2013-06-032013-06-03
2013-06-102013-06-16
2013-06-172013-06-23
2013-06-242013-06-30

trunc(sysdate, 'Day') will return the 'sunday' of that particular week. In order to get monday, we are adding 1 to the received date.

references:
For available date formats : http://psoug.org/reference/date_func.html


discussion..

Dynamic Languages Discussions done 12 years ago

I was watching the following video on youtube, where Paul Graham was one of the panel member.
His talks and writings carries meaningful messages. I really like Paul Graham's thinking and ideas, so I have noted it down below. Its an old video, according to youtube description it was made on 2001-05-10. The way he sees the future is fantastic.

Computer doesn't care about the languages, it only need the instructions to execute.
Its us who needs languages.

Different between best languages and other languages are that best languages are created to be used by them to solve their own problems.
Other languages are created to be used by others. Simply designed for marketing.
Hacker's don't like to be told what to do. They like to mess around and figure things out.
Best languages are the one which helps to write throw-away programs. Because programs are born in throw-away programs.
(This is absolutely true when I used Python and later Go.)

Clients are on loose so focus on server based software. How true his predictions are. Since its server, you can use any languages to program.
Choose a language which helps to write changeable software. When doing an update, you must know where it might break when things goes bad.
Knowing where it will break is very important. Using closure to keep states.

Focus on profilers since it helps you to speed up your programs.

An interesting point. Rather then picking a language which was designed by 5 people comity, pick a language from a pool of 5 different languages where created by 5 different people. Since each one of them will take very high risk and break boundaries to create it and use it to solve their problems.

(Rest of the speakers are also superb. )

Video reference:
http://www.youtube.com/watch?v=agw-wlHGi0E


discussion..

Finding forgotten documents in gmail

Our mail boxes are filled with hidden gems. It contains memories which are connected to certain messages.
I started to notice the forgotten gems while I was searching for old mails. The path I travelled, the hurdles I jumped,
my thoughts and my ideas; its very interesting to go through these forgotten gems. I remembered an wise one said, “never forget the way you came”. This is where I realized that my gmail had more than 10000 mails going back to start. Its all the way from time when gmail was introduced and I got an invite from a friend to join.

Lets say, I want to find all the images (jpg and png) I received, which are more than 1KB in size, before 2008.

So I used the following search string to achieve it :

filename:jpg OR filename:png size:1000 before:2008/01/01 has:attachment

And got my result :)

Ohh, and I want to get all the pdf documents which I received between 2005-2006

filename:pdf size:0 before:2006/01/01 after:2005/01/01 has:attachment

This link https://support.google.com/mail/answer/7190 will help you to create
your on search critiria.

Good luck on hidden gem hunting.

(I guess free service provider is also doing the same to learn my behaviours and narrow the ads which are directly targeting me)


discussion..

Moving from Wordpress

EDIT: Now Disqus is enabled for my notes.
I was going though my old notes, found this quote which I've learned sometime ago.

If you see something so beautiful, remember that there will be always an ugly site not visible, but its there!!

“Someone's garbage is another one's treasure!!” , how can I fit this theory into a startup

Hello Guys,

Sometimes back, I was using wordpress to host my blog.
But not satisfied with it. Have to pay more to include a simple javascript snippet. And it felt bulky. Now I have started to use
github/jekyll. And I love it. Power of simple.

Will be moving my old notes soon. And have to include disqus to handle comments/feedbacks for my notes.


discussion..

Ciphering with TripleDES in outer CBC mode using 2 keys

Recently, I was working on a project to implement the 3GPP TS 23.048 specification. It's a specification which deals with security
mechanisms for the (U)SIM application toolkit. Under a particular section (KIC and KID), I have to implement the Triple DES in outer CBC mode with 2 keys. Well, I initially implemented using python/Crypto library. Later I implemented it using Go.

Triple DES is a block cipher/encryption algorithm. In other words, applying DES algorithm 3 times on each block.
Generally we have to use 3 keys, but current sim cards supports only 2 keys (or maybe the sim card which I used was following 2 keys standard).
Enough with theory, lets get into action.

package main

import (
    "log"
    "fmt"
    "crypto/des"
    "crypto/cipher"
    "encoding/hex"
)

func main(){
    kic := "7962D9ECE03D1ACD4C76089DCE131543" //its a 16 bytes key (values in hex)
    initialVector := "0000000000000000" //8 bytes
    plainText := "112233445566778811223344556677881122334455667788" // length must be multiply of 8

    //convert the kic to 3 keys (k1, k2, k3 where k1 != k2 and k1 == k3)
    kic = fmt.Sprintf("%s%s", kic, kic[0:16])

    key, _ := hex.DecodeString(kic);
    iv, _ :=  hex.DecodeString(initialVector)
    src, _ := hex.DecodeString(plainText)

    dest := make([]byte, len(src))
    tdes, err := des.NewTripleDESCipher(key); if err != nil{
        log.Fatal(err)
    }
    encrypt := cipher.NewCBCEncrypter(tdes, iv)
    encrypt.CryptBlocks(dest, src)

    log.Println("Plain  value :", plainText)
    log.Println("Cipher value :", hex.EncodeToString(dest))
}

Thats it, using standard Go library we can implement the entire 3GPP TS 23.048 specification.


discussion..

Decoding binary encoded URL request in Django App

How to decode a GET request which contains encoded (%xx quoted hex format) binary data.
For example, following is a GET request:

http://mosms.senthadev.com/sendsms/?udh=%02q%00&ud=%00%0B%0A%B0%00%01%00%00%00%00%12%00%01

This was one of the hurdle that I faced while implementing a Django web application.
In this request, parameters udh and ud contains encoded binary data.

Therefore, to retrieve the values, I used below code. But the return values were in ascii.

ud = request.GET.get('ud')
udh = request.GET.get('udh')
print('%r' % ud) # u'\x02q\x00'
print('%r' % udh) # u'\x00\x0b\n\ufffd\x00\x01\x00\x00\x00\x00\x12\x00\x01'

For example,
I was expecting 027100 for ud, but I got u'\x02q\x00'.
And this caused an error when I tried to convert the value back to binary using binascii.b2a_hex(ud).
Django threw the following error:

UnicodeEncodeError: 'charmap' codec can't encode character u'\ufffd' in position 3: character maps to

This thrown for Django 1.4

How to solve this in a very limited time frame?
And, I used following code:

 
import urllib, binascii
from django.http import HttpResponse

def submit():
    #collecting the GET params into dict
    data = dict(item.rsplit('=') for item in request.META['QUERY_STRING'].rsplit('&'))
    udh = str(binascii.b2a_hex(urllib.unquote(data['udh'])))
    ud = str(binascii.b2a_hex(urllib.unquote(data['ud'])))

    #now I have the ud and udh as proper hex string to be converted to binary data.
    #udh : 027100
    #ud : 000b0ab0000100000000120001

discussion..