Database Connection

Beta version only

This is currently only enabled in the beta. This is still under development. Any issues or questions, please contact us for further support.

GOAL: Make it easy to connect to a database and download all or a subset of the data. Make it easy to update the data and query the database. Connect to a variety of database types, and do so in a secure manner.

Currently, only external databases are supported. The code currently supports MySQL or Postgres databases.

The design is that DataGraph connects to a php script on your web server, and that script connects to the database. Often the web server and the database server are running on the same machine, and for security reasons the database server does not allow any direct connections. Or the web server and the database server are different machines, and the database does not allow connections outside of a local network. This is the same type of design as is used for WordPress and other web site applications. The difference is that instead of accessing the data through a web browser, DataGraph sends the requests and parses the output from the web server.

To connect to a server, you need to upload the php script. DataGraph creates and uploads this for you. You need a script for every database you want to connect to, but that allows you to connect to any tables in that database. For setting up the connection, you need a database user name and password, and that will be embedded in the php script that is uploaded. Once you have added the php script, this allows DataGraph a way to query the database and see what tables exist.

Once you have set up the connection, you can connect from other machines without needing to share the database user and password. There are two types of connections.

1 – Admin user – Has access to all of the tables in the data base and can add/remove users and set access.
2 – Standard user – Only sees tables that the admin user has enabled

To make and upload the php script and to administer users select DataGraph > Set up Database Link.

This window allows you also to log requests that are made to the server. This is both so that you can better understand what information is sent to the user and to help figure out any connection issue that arises. The PDO interface is an abstraction layer so that php can access a variety of databases, but there are small variations in SQL syntax between databases, and it is possible that the php script needs to be tweaked. To help debug that, DataGraph has a logger that allows you to resubmit queries and see the response that came back.

For both admin users and standard users, the connection depends on a shared secret. This is different from a password, in the sense that it is not sent with the queries.  

When a DataGraph file makes a connection to a database, it does so with a special group.

This group needs three pieces of information.

1. The address of the php script on your server. This is either through a http or https connection.
2. The user name. A blank name is the admin user, otherwise it is the name of the user that was set up by that admin user.
3. The shared secret. This is 128 bit identifier that is generated when you set up the link or add a user.

Every request that DataGraph makes to the server is done as follows:

1. It creates a key value argument list, something like user=david, table=mytable, command = getcontent, columns = a,b,c
2. This argument list is made into a string and appended with the user name and shared secret string.
3. Compute a 256 bit check sum from this string (uses sha256)
4. The argument along with the checksum is sent to the server script.

The server script (php script) gets this request. Based on the user argument it knows the shared secret. The shared secret for the admin user is embedded inside the php script, and the shared secret for a non-admin user is saved in a table that the connection script has created in your database. The argument list is the appended to the user name and shared secret and the server app creates a 256 bit checksum of that string and compares it to the checksum that you sent with the request. If the checksum doesn’t match the request is rejected. If the request is accepted the response is sent back as an xml file and DataGraph parses that.

Since the php script is readily readable, you can see exactly how the argument is validated and what SQL statements are submitted. Care has been taken to avoid SQL injection attacks. Requests without the shared secret are mathematically extremely hard since a brute force approach would require going through all of the UUID identifiers.