Pimp my PAW

I'm in the PAW Workbench, I open a view and I click on the "Edit MDX" button. I get a packed MDX block, with no indentation whatsoever, pretty hard to read. If only there was an option to format that MDX block to something much easier to read.....

 **RING** **RING** Oh, I'm sorry, I must interrupt this blogpost, someone is at the door.

Please hold on.

- I'm sorry, who are you?

- Yo dawg! I'm Xzibit!

- Wait! What? What are you doing on this blog?

- Yo dawg I herd you like APIs so we put an API in yo API so you can MDX while you MDX.

- Is this a joke? This does not make any sense!

- It will make sense, buckle up yo.

This is going to be the setup:

chromium <---mitmproxy---> PAW server

1. chromium opens the MDX Editor, that triggers a POST /api/v1/MDX call to the PAW server
2. mitmproxy catches the json response from the PAW server
3. a python script modifies that json data on-the-fly according to our needs
4. mitmproxy hands over the modified data to chromium
5. finally chromium displays the reformatted MDX expression

The whole process is completely transparent to the client and the server. We are inserting ourselves in the middle of an SSL encrypted connection, it will require installing a fake Certificate Authority to keep both client and server happy. And I don't like clicking around, so we will prepare that setup from the commandline.

First, we install the mdx-beautify.py script that mitmproxy will be using to process the PAW server responses.

mkdir ~/.mitmproxy && cat > ~/.mitmproxy/mdx-beautify.py << EOF
from sql_formatter.core import format_sql
from mitmproxy import http
import json

def response(flow: http.HTTPFlow) -> None:
    #intercept MDX API responses
    if flow.request.pretty_url.endswith("/api/v1/MDX"):
        content = json.loads(flow.response.content)
        mdx_pretty = format_sql(content['Mdx'])
        # inject the expression back in the flow
        content['Mdx'] = mdx_pretty
        flow.response.content = bytes(json.dumps(content),"UTF-8")
EOF
#

Before installing the mitmproxy/mitmproxy docker image, we need to extend that docker image with the python package "sql formatter". sql formatter is not ideal to format MDX, but that will be good enough for the sake of that proof of concept:

docker build --tag mitmproxy-sqlfmt - << EOF
FROM mitmproxy/mitmproxy
RUN pip install sql_formatter
EOF
#

Now run that new build with the mdx-beautify.py script and a localhost web interface on port 8081 to display what goes through the proxy:

docker run --rm -it -p 8080:8080 -p 127.0.0.1:8081:8081 -v ~/.mitmproxy:/home/mitmproxy/.mitmproxy mitmproxy-sqlfmt mitmweb -s ~/.mitmproxy/mdx-beautify.py --web-host 0.0.0.0

On its first run, the container generates all the necessary certificates in ~/.mitmproxy .

With a browser, navigate to http://127.0.0.1:8081 this is mitmweb interface that allows you to see all the exchanges between the client and PAW server. We will not use that browser to access the PAW server, this is only to watch what's happening in the background.

As it is suggested on that screenshot, we will now configure a chromium client to use that proxy.

But first, a word of warning, installing fake CAs is messy, and it creates a huge security vulnerability in your browser, anybody in possession of the CA keys could read the traffic from your browser. So, we are going to sandbox chromium with firejail in order to keep that setup isolated in a dedicated directory ~/jail and that will be much easier to clean up afterwards.

We initialise the certificates database in the jail with certutil from mozilla-nss-tools and populate it with the newly generated mitmproxy certificate. That will spare us from messing with the browser settings at chrome://settings/certificates

mkdir -p ~/jail/.pki/nssdb && certutil -d sql:$HOME/jail/.pki/nssdb -A -t "CT,c,c" -n mitmproxy -i ~/.mitmproxy/mitmproxy-ca-cert.pem 

We launch the sandboxed chromium in firejail with the preconfigured proxy address

firejail --env=https_proxy=127.0.0.1:8080 --private=~/jail chromium

With this jailed chromium, navigate to the URL where your PAW server is located, log in, open the workbench and open some cube view, as you normally would.

Now, you see on the screenshot below, on the bottom left, clicking on the "Edit MDX" button resulted in a POST /api/v1/MDX call to the PAW server. On the right, in the response from the server, the value for the key "Mdx" is correctly showing "\n" and space indentations. And finally, the "Edit MDX" window is showing the MDX expression almost formatted to our liking.

- OK! But this is definitely not something that I would deploy to users.

- Right yo! This setup is only intended for educational purposes, and it is about as useful as print "hello world!". But It gives you a different approach to dig in the PAW API, and a way to inject it with python code through the mitmproxy scripting API.

- Thanks Xzibit!