Initially, for this project we were requested to make a web interface for our client to access their SQL database. Our client requested having a few important features for the interface. First requirement is to be able to see raw data on the webpage. Second is to be able to export the raw data to an excel file. Third is to be able to make graphs with the raw data provided.
This article records my initial brainstorming on how the project might have been implemented. The two areas I focused the most was on the front end GUI and the back end security. Though some might seem naive, but it was still in the early planning phase of our project.
For the implementation of my prototype web UI, I found a great jQuery library called Select2. It expands the multiple option select into a more powerful and intuitive solution as seen below.
The Select2 library offered a power search function for multiple select options, but also customizability for filtering search options. This was the essential core in my initial plans to build our front end UI. This search and select function was going to be the bread and butter for majority of the UI, where we can optimize the user interface to dynamically add filter selections for the end user to pick a high and low value. It provides a clear and cut interface for the user to understand their options.
The UI of the front end was a great deal of my focuses was on during early planning, as we are serving clients who does not want to fiddle around with cumbersome UI and overloaded with a variety of options when initially looking at the UI. But it is still possible to add hidden options on to the UI for users who wants to have more control through the interface.
The second important thing was building a secure connection between the application and the database. From my CS340 experience, we didn’t do a very good job securing a connection between our databse to our application. There was no authentication, all the connection files were in raw text. Though it was ok for a class project, but it would a disaster for a project that is serving a real client with real consequences.
An authentication method I brainstormed was to requrie two authentications, one for the web UI and the other for the database. First step would be to require a user to authenticate with a username and password for accessing the webpage. Then implement a second authentication for securing the database connection via a passcode.
The reason for using a passcode is to use that code to encrypt the file that stores the raw connection parameters. The user has to enter a passcode to decrypt the file and then the application will be able to authenticate between the database. This ensures that even if the application’s source code was exposed, the connection file will be encrypted and unaccessable. Another other way would be instead of using a static passcode, we could use a one time password generator that generates the passcode to decrypt the file. Though I am not too sure if it is possible to implement, but I think it would be an interesting topic for me to continue to look into and create a working sample of.
In the end, these project ideas may only be realized through a small project of my own instead of building one for our client, due to our changing approach to the project. But it does make me think about the variety of issues building an application that is facing towards a hostile web, but innocent users.
