Tuesday, March 18, 2014

Authenticating to Google Fusion Tables from Server

A couple of weeks ago I started a project where I needed to save big amounts of data for later statistical analysis. The first data store I looked at was Google Fusion Tabels (because it was free...). The reason I didn't go for it was that there were quota limitation that didn't allow that much data.

But before I got to the point that I discarded it wrote a working backend on Google app engine that could write to a table. Here is the code. The hard thing here was authenticating to the Fusion tables. 

Working with Google APIs from Google App Engine should be very easy thanks to Google App engines support for service account, unfortunately Fusion tables API does not support this yet. So we need to use the harder way to use Application Accounts. Here is the code for authenticating and making request to Fusion tables. Please ignore bad exception handling.
private Fusiontables fusiontables = null;
   
    public void initiateFusionTablesAPI() {
        Collection scopes =
                Collections.singleton("https://www.googleapis.com/auth/fusiontables");

        String serviceAccountId = "768188911902@developer.gserviceaccount.com";

        String p12FileName = "/privatekey.p12";
        InputStream p12Stream = context.getResourceAsStream(p12FileName);
        PrivateKey serviceAccountPK = null;

        try {
            serviceAccountPK = SecurityUtils.loadPrivateKeyFromKeyStore(
                    SecurityUtils.getPkcs12KeyStore(), p12Stream,
                    "notasecret", "privatekey", "notasecret");
        } catch (GeneralSecurityException e) {
            e.printStackTrace();
        }

        GoogleCredential credential = new GoogleCredential.Builder()
                .setTransport(new NetHttpTransport())
                .setJsonFactory(new JacksonFactory())
                .setServiceAccountId(serviceAccountId)
                .setServiceAccountScopes(scopes)
                .setServiceAccountPrivateKey(serviceAccountPK)
                .build();



        fusiontables = new Fusiontables.Builder(
                new NetHttpTransport(), new JacksonFactory(), credential)
                .setApplicationName("Bysykkel-stats")
                .build();

    }

   
    public void storeInformation(){

    String sql = "INSERT INTO ...";
   
    try {
        Sqlresponse sqlresponse = fusiontables.query().sql(sql).execute();
        if (sqlresponse.getRows().size() != 1) {
            throw new RuntimeException("Error in sql response " + sqlresponse.toPrettyString());
        }
    }  catch (Exception e) {
            e.printStackTrace();

    }

    }

Wednesday, March 12, 2014

Tool to test queries on Google Fusion tables

A couple of weeks ago I started a project where I needed to save big amounts of data for later statistical analysis. The first data store I looked at was Google Fusion Tabels (because it was free...). The reason I didn't go for it was that there were quota limitation that didn't allow that much data.

But another problem I found was that there is no good way to simply test out queries on a table. So I built a tool for it. So for those of you using Google Fusion Tables or thinking about it, go have a look.

The tool is here, https://rasmusson.github.io/fusion-tables-tool
Any ideas can be submitted here, https://github.com/rasmusson/fusion-tables-tool/issues

Saturday, March 8, 2014

A simple way of doing pagination with Angular UI

In the last few days I have been developing a tool to work with Google fusion tables. In this I display results for a search query in a table with pagination. I found a lot of different ways to do this but I finally ended up with this.
{{column}}
{{cell}}
The relevant part in this is row in queryResult.rows.slice(((currentPage-1)*10), ((currentPage)*10)) track by $index. 

I using the currentPage variable from the Angular UI pagination component in combination with the javascript array slice method to split the result up in pages. Simple and efficient.

Here is a plunker with an example

The limitation of this being that you have to load the entire result at once. Pagination is entirely on the client side and not in the query.

Friday, January 17, 2014

Using CDI in external JAR module

I recently had a task to create a REST API in a jar module separate from the main application. The JAR will be a dependency to the main application.
In this REST resource class I which to inject the som session scoped data about the user.

Problem is that CDI does not inspect external JAR files by default. This off cource resulted in the classic NullPointerException.

After some research  found that adding an empty beans.xml to the resources/META-INF/ tells CDI to inspect the classes in the JAR for injection points.

Tuesday, November 5, 2013

Use squence for toggling boolean value

I recently needed to build a postgres function that generated names, the function should return double names half of the times it was called. The function were to be run in a loop in a main function so the first idea was to use a boolean variable in the main function that was sent as a parameter to the name function. The was boolean was toggled after every loop iteration.

A better solution that I came up with is to use a sequence. These are stateful and can be access in the entire scheme.

I create a sequence that counts from 0 to 1 and then starts over. I then cast the result to char and then to boolean.

CREATE SEQUENCE double_first_name_toggle MINVALUE 0 MAXVALUE 1 CYCLE;
SELECT nextval('double_first_name_toggle')::char::boolean into double_first_name;

Every time the SELECT nextval('double_first_name_toggle')::char::boolean into double_first_name; is called it the result is the opposite from last time.

This is now used in the name function and no parameter needs to be sent in.



Friday, August 23, 2013

Creating your own CA with OpenSSL and CA.pl

I have recently had an issue where I need to create test certificates for use in unit test.
The files I’m signing needs to be signed by a certificate that is signed by a CA with specific public key.

For this purpose I figure the easiest way to accomplish this is to

Create a CA of my own
Create a certificate for the unit tests
Sign the certificate with using the CA

I will show how I did this using OpenSSL and the CA.pl script that comes with the OpenSSL installation.

Create new CA
CA.pl -newca

CA certificate filename (or enter to create)

Making CA certificate ...
Loading 'screen' into random state - done
Generating a 1024 bit RSA private key
................++++++
.............++++++
writing new private key to './demoCA/private/cakey.pem'
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:test-ca
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
Using configuration from C:\OpenSSL-Win32\bin\openssl.cfg
Loading 'screen' into random state - done
Enter pass phrase for ./demoCA/private/cakey.pem:
Check that the request matches the signature
Signature ok
Certificate Details:
        Serial Number:
            ff:65:cb:b3:87:ab:ce:4b
        Validity
            Not Before: Aug 23 08:40:46 2013 GMT
            Not After : Aug 22 08:40:46 2016 GMT
        Subject:
            countryName               = AU
            stateOrProvinceName       = Some-State
            organizationName          = Internet Widgits Pty Ltd
            commonName                = test-ca
        X509v3 extensions:
            X509v3 Subject Key Identifier:
                EA:DE:A5:01:11:76:9A:22:15:33:7E:72:6A:A8:FC:AD:3E:8E:23:9E
            X509v3 Authority Key Identifier:
                keyid:EA:DE:A5:01:11:76:9A:22:15:33:7E:72:6A:A8:FC:AD:3E:8E:23:9E

            X509v3 Basic Constraints:
                CA:TRUE
Certificate is to be certified until Aug 22 08:40:46 2016 GMT (1095 days)

Write out database with 1 new entries

Data Base Updated

I use all the default options except for the common name and PEM pass phrase.


Creating the test cert
CA.pl -newreq
Loading 'screen' into random state - done
Generating a 1024 bit RSA private key
..............................................................++++++
...........................................++++++
writing new private key to 'newkey.pem'
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:test-cert
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
Request is in newreq.pem, private key is in newkey.pem

I use all the default options except for the common name and PEM pass phrase.

Sign the test cert with the CA
CA.pl -sign

Using configuration from C:\OpenSSL-Win32\bin\openssl.cfg
Loading 'screen' into random state - done
Enter pass phrase for ./demoCA/private/cakey.pem:
Check that the request matches the signature
Signature ok
Certificate Details:
        Serial Number:
            ff:65:cb:b3:87:ab:ce:4c
        Validity
            Not Before: Aug 23 08:47:32 2013 GMT
            Not After : Aug 23 08:47:32 2014 GMT
        Subject:
            countryName               = AU
            stateOrProvinceName       = Some-State
            organizationName          = Internet Widgits Pty Ltd
            commonName                = test-cert
        X509v3 extensions:
            X509v3 Basic Constraints:
                CA:FALSE
            Netscape Comment:
                OpenSSL Generated Certificate
            X509v3 Subject Key Identifier:
                99:7F:AD:BE:2F:3C:C5:F4:65:8A:A0:2D:6C:07:23:88:48:25:E5:39
            X509v3 Authority Key Identifier:
                keyid:EA:DE:A5:01:11:76:9A:22:15:33:7E:72:6A:A8:FC:AD:3E:8E:23:9E

Certificate is to be certified until Aug 23 08:47:32 2014 GMT (365 days)
Sign the certificate? [y/n]:y


1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated
Signed certificate is in newcert.pem

Now the newcert.pem is signed with the CA

PS. If you use the same common name for the CA and cert you will get this error.
failed to update database
TXT_DB error number 2

Wednesday, June 12, 2013

Genereate .p7b file from multiple .cer files

A p7b file can contain many CA certificates. This can for example be the case when creating a bundle of CA certificates. As was the case for me.

In a project I'm working on we want to bundle all the trused CA certificates in one file. The file for this purpose was p7b.

We used openssl to do it.

The first thing that needs to be done is to convert the .cer files to PEM.

openssl x509 -in myCA1.cer -inform DER -outform pem -out myCA1.pem
openssl x509 -in myCA2.cer -inform DER -outform pem -out myCA2.pem

And then we bundle the PEM files

openssl crl2pkcs7 -nocrl -certfile myCA1.pem -certfile myCA1.pem -outform PEM -out bundle.p7b

To add another PEM fil just add another -certfile argument.