Web Database Access from Desktop Applications
There may be growing interest in moving computer applications from the desktop to the Web. But desktop applications are still a huge market, whose demise has been prematurely reported by the media in the past, and will most likely be reported in the future. In addition, for many domain areas, a Web browser — even for an AJAX-powered site — will be insufficient for many application needs. Microsoft is just one of several companies experiencing an assortment of technical problems with Web-based applications and software as a service (SaaS). These problems include server outages and other technical glitches.
With desktop application usage and development still going strong, every Web developer, at some time or another in their career, may encounter a problem that has long plagued desktop applications in the Internet age: If the database contains information needed by the desktop application, but the database is hosted in a Web account that does not allow remote procedure calls (RPCs) — and most of them do not allow them — then how can the desktop application communicate with the database?
There are countless situations in which this need arises. For instance, you may have created a proprietary desktop application for customers who pay for a license to use it for a limited amount of time. You probably also want to allow prospects to try your program for free, during a trial period, so they can decide if it is worth investing in a license. So how can you set up both your application and your database so that the former can query the latter to obtain the user’s expiration date?
In this tutorial, we will explore a straightforward solution that I devised, and that you can use in your own systems. We will be using MySQL, but the principles apply to any other online RDBMS. The database access script and desktop application both utilize PHP, but this technique works for any desktop language that can access Web pages.
Sample Database
Most programming ideas are best taught through example, and this one is no exception. To demonstrate this technique, we will use a sample database, a desktop application, and a PHP script to connect the two together.
Continuing the aforesaid scenario, let’s assume that when a prospect downloads your program, your Web site requires them to provide their name and e-mail address. The following SQL code will work for creating a simple but sufficient MySQL database, user, and table for storing the user data:
DROP DATABASE IF EXISTS product_database;
CREATE DATABASE product_database;
GRANT ALL ON product_database.* TO user@localhost IDENTIFIED BY 'password';
USE product_database;
DROP TABLE IF EXISTS product_registration;
CREATE TABLE product_registration (
registration_number INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
,first_name VARCHAR(20) NOT NULL
,last_name VARCHAR(20) NOT NULL
,email_address VARCHAR(20) NOT NULL UNIQUE
,expiration_date DATE
);
INSERT INTO product_registration VALUES
( 1, 'John', 'Jones', 'john@example.com', '2008-05-01' )
,( 2, 'Jane', 'Smith', 'jane@example.com', '2008-05-30' )
;
To readers unfamiliar with SQL, a brief description of the code should suffice: Firstly, we create the database itself. Secondly, we create a new user with the name “user†and the password “password†(neither value of which you should use in any production database, for security reasons), with all privileges on the database. Thirdly, we create a new table for the product registration data. Lastly, we populate that new table with two sample records.
Database Access Script
The essence of this technique is a combination of GET calls and Web page content — encrypted wherever necessary (more on that later). More specifically, the database content is made available to the desktop application in the form of a Web page, which is called by the desktop application in a URL that passes its parameters to the superglobal array $_GET.
In our example, we need a PHP script that, when given a valid registration number, returns the expiration date for that particular product registration. The following script is short, but suffices to demonstrate the methodology. For purposes of brevity, I have not included the code for executing the query to the database, contained in the function SQL_query(), which is defined in the library file SQL_functions.php. PHP programmers typically have their own preferred code for accessing databases — whether hand-coded or using a framework such as PEAR — and you can make use of your favorite approach.
The function returns an array comprising the query status as a Boolean value, and the data value if the query was successful. Note that the MySQL code would be the logical place to escape any characters in the registration number that a hacker might use for SQL injection or any other sort of attack.
Here is a script, which we will name product_expiration_date.php:
require_once 'SQL_functions.php';
if ( ! isset( $_GET[ 'registration_number' ] ) ) {
exit( 'Error: required parameter registration_number is missing' );
}
list( $query_okay, $expiration_date ) = SQL_query( 'SELECT expiration_date
FROM product_registration WHERE registration_number = '
. $_GET[ 'registration_number' ] );
if ( ! $query_okay ) {
exit( 'Error: invalid registration_number passed' );
}
echo $expiration_date;
?>
In this script, we first confirm that a registration number has been passed in, and, if not, terminate the script with an error message. We then query the database for the expiration date corresponding to the registration number. Should the query fail — presumably because the registration number was not found — we output an error message. The application that calls this script naturally should check for the presence of the term “Error†in the output that it receives.
Assuming that a valid registration number was passed in correctly, then the expiration date is output.
If the above PHP script is located in the root directory of your Web site (at http://www.example.com/), then we would access it via the URL http://www.example.com/product_expiration_date.php. Using the sample records that we populated our table with, the URL for the customer John would be http://www.example.com/product_expiration_date.php?registration_number=1. This returns a Web page consisting of the expiration date “2008-05-01″.
Desktop Application Access
Even though PHP is rarely used for desktop applications, for the purposes of consistency, we will consider a test application written in PHP. Here is a function for checking whether or not the user’s installation of your program has expired:
function validate_program_license( $registration_number ) {
$expiration_date = file_get_contents(
"http://www.example.com/product_expiration_date.php?
registration_number=$registration_number" );
if ( substr( $expiration_date, 0, 5 ) == 'Error' ) {
exit( 'Error: problem accessing database' );
}
if ( strtotime( $expiration_date ) < date() ) {
exit( 'Error: program license has expired' );
}
}
Presumably, you would call this function in your PHP desktop application to confirm that the user’s license is still valid. For instance:
if ( strtotime( validate_program_license( $registration_number ) ) < date() ) {
exit( 'Error: program license has expired' );
}
In a production environment, the registration number could be stored in a hidden license file created when the user downloads and installs your application, or perhaps in the Windows Registry.
Database Updating
Up to this point, we have seen how to read data from a remote database. But what if you need to modify data within that database? The same technique can be used, as illustrated in the expanded version of the PHP script below:
require_once 'SQL_functions.php';
if ( ! isset( $_GET[ 'registration_number' ] ) ) {
exit( 'Error: required parameter registration_number is missing' );
}
if ( isset( $_GET[ 'expiration_date' ] ) ) {
$query = SQL_query( 'UPDATE product_registration
SET expiration_date = "' . $_GET[ 'expiration_date' ]
. '" WHERE registration_number = ' . $_GET[ 'registration_number' ] );
if ( ! $query_okay ) {
echo 'Expiration date updated';
}
else {
exit( 'Error: invalid registration_number passed' );
}
}
else {
list( $query_okay, $expiration_date ) = SQL_query( 'SELECT expiration_date
FROM product_registration WHERE registration_number = '
. $_GET[ 'registration_number' ] );
if ( ! $query_okay ) {
exit( 'Error: invalid registration_number passed' );
}
echo $expiration_date;
}
?>
For example, if you want to add another month to Jane’s license, the following URL would work: http://www.example.com/product_expiration_date.php?
registration_number=1&expiration_date=2008-06-30
Conclusion
This tutorial demonstrates how your desktop applications can read from and update server-side databases that are otherwise inaccessible. Yet there is more that you can do along these lines: For instance, we did not address the issue of security. Obtaining the expiration date of a registration record is of little value to the malicious user. In contrast, if such a user were to somehow detect the URL that your desktop application is calling in order to update the program’s expiration date, then he could set his own registration record to have an expiration date far off in the future, and thus effectively avoid having to pay to renew his license for your program. For any such sensitive data access or modification, you should encrypt the values being passed back and forth between your desktop application and the PHP scripts on your server, so hackers cannot exploit your system.
As noted earlier, this general technique is not limited to MySQL and PHP. It can be used for any server-side database, any Web scripting language that can access such a database, and any programming language that can be used on the desktop and can get the contents of Web pages — using either native functions or making use of a Web access library.
