Web Database Access from Desktop Applications
By Michael J. Ross2008-05-06
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".
Tutorial Pages:
» Web Database Access from Desktop Applications
» Sample Database
» Database Access Script
» Desktop Application Access
» Database Updating
» Conclusion
