HTTP Post is the most secure way of fetching information from your own system or from systems where you have access.
InfoCaptor provides direct database adapters so you can simply connect to your database without any proxy or API. This is really a powerful functionality as it allows you to query your own database in real time by just issuing SQL commands.
Very often databases are tied under the IT policies of "no outside access". If your company is under SOX or other government regulation then getting direct connection to SQL is simply impossible.
In such cases, you can follow all the safe methods of getting a read only database user and grant only select privileges to certain schema or set of tables.
But what do you do when your company simply does not allow any outside connections to internal databases?
To help with this situation, you can create a SQL-Bridge interface as illustrated in the figure below
This way you have created another security layer on top of your database. It is totally upto you how you implement the bridge.
We will demonstrate one real example and how we use the above mechanism with our own SAAS application
We have our MockupTiger web application https://www.wireframes.org hosted on a different server than our Dashboard application https://my.infocaptor.com
They both are on different databases and don't talk to each other and do not have any special back door access.
Internally we have opened up readonly access to MySQL databases that houses our blogs (on mediatemple) so it is easy for us to issue direct SQL commands against our wordpress database but for SAAS applications the databases are sealed off from the outside world.
In order to derive real time metrics from our MockupTiger web app we followed the above approach of HTTP posting + Sql bridge.
How to write the SQL bridge
Here is the detailed procedure.
1. Write the sql_bridge.php code (Get the sample file here)
1.1 we define three hard coded variables $login, $pass and $api
1.2 Next, we collect the user submitted POST variables
1.3 We simply validate the POST variable contents to the hard coded values
1.4 We define the database variables for connecting to a MySQL database. You can choose to connect to any database of your choice and implement your own procedure how you connect and talk to it.
Once authenticated, we intepret another post variable ( $_POST["x"] ) content to determine what type of data we need to return
NOTE: We are generating the SQL statements inside our sql_bridge.php file depending on the "x" variable.
In the next step (#2) we call the mysql_result_x with the sql statement and collect the output in the $output variable
Next, we convert the output to JSON and return it to the browser.
Here is how the mysql routine is defined and you can define your own routine for other databases.
Now we place the sql_bridge.php file on our webserver and maybe bury it under a sub-directory.
In this tutorial, we simply placed it in subdirectory named "restricted"
By calling this URL it shows blank page, no information is revealed.
Now we define this URL within infocaptor so it can securely pass the POST variables and display data on the dashboard
How to define connection for HTTP Post within InfoCaptor
Within InfoCaptor - Goto Actions and select "Manage Data Sources/Connections"
The connection definition URL accepts the number of fields (nof) variable for http post
If you don't specify nof value then the default value is 3 POST parameters
For e.g if you specify nof=1 then only one parameter name/value pair can be defined
In our case we need only three post variables User, Password and API
But wait, don't we need one more post variable for the "x"?
You are right but we don't need to define it here and will address that shortly
Let us define our connection for HTTP post
1. Give a appropriate connection handle (my_webapp_metrics)
2. Provide the HTTP URL (http://localhost/restricted/sql_bridge.php)
3. Select output Data type. We know that sql_bridge.php returns JSON data (see Fig 3 , bullet point #3)
4. Parameter name = user and Parameter Value = whoareyou
5. Parameter name = pass and Parameter Value =keepguessing
4. Parameter name = api and Parameter Value = DWWRDD366546sdscsd39239ExesTBSD
Click on "Add new Connection Entry"
Now we can use this connection handle inside our dashboard widgets.
1. Create a new page
Since we know that we are getting only count values we can display them on the icon widgets
2. Drag four icons to the canvas (we just chose the rounded squares)
3. Right click on the first one and select "Data Source"
4. Select the connection handle we just defined (my_webapp_metrics)
5. The parameters section is optional for http_post. But here is an interesting piece. Any new variable you define in the parameter section is automatically sent as a post variable along with the rest of the http post URL and whatever you defined in the connection settings.
So this is where we can define the "x" post variable.
And you will see the number populated on the widget icon
Similarly populate the remaining icons
So each icon widget is representing the count from the sql_bridge output.
In case you are wondering why we did not define the "x" post variable as part of the connection here is a brief explanation.
The connection is suppose to authenticate the request is coming from a known location. So we define the connection using user, password and api variables.
If we had defined the variable "x" in the connection template then we would end with different connections for each different kind of information we wish to display.
The above setup gives us the flexibility of keeping some part of the connection constant and change part of it on the fly.