Tag Archives: mysql

Update 6/25/09: I’ve updated the script to include a number of suggestions made in the comments. The new script supports multiple files, up to 20 URLs can be created at a time, and a brief note can be attached to each key. If these features sound useful, please check out the new post at:

Protecting multiple downloads using unique URLs.

A client asked me to develop a simple method for protecting a download (or digital product) by generating a unique URL that can be distributed to authorized users via email. The URL would contain a key that would be valid for a certain amount of time and number of downloads. The key will become invalid once the first of those conditions is exceeded. The idea is that distributing the unique URL will limit unauthorized downloads resulting from the sharing of legitimate download links.

In addition, once the key has been validated, the download starts immediately, preventing the visitor from seeing the actual location of the download file. What’s more, the file name of the download in the “Save as” dialogue box isn’t necessarily the same as the file name of the file on the server, making the file itself pretty much undiscoverable.

How it works

There are five main components to this system:

  1. the MySQL database that holds each key, the key creation time, and the number of times the key has been used
  2. the downloadkey.php page that generates the unique keys and corresponding URLs
  3. the download.php page that accepts the key, verifies its validity, and either initiates the download or rejects the key as invalid
  4. a dbconnect.php file that contains the link to the database and which is included into both of the other PHP files
  5. the download .zip file that is to be protected

Place all three PHP scripts and the .zip file into the same directory on your server.

The MySQL database

Using whatever method you’re comfortable with, create a new MySQL database named “download” and add the following table:

CREATE TABLE `downloadkey` (
  `uniqueid` varchar(255) NOT NULL default '',
  `timestamp` varchar(255) NOT NULL default '',
  `downloads` varchar(255) NOT NULL default '0',
  PRIMARY KEY (uniqueid)
);

The downloadkey.php page

This page generates the key, creates a URL containing the key, and writes the key to the database. Never give out the location of this page – this is for only you to access.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>Download Key Generator</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<meta name="author" content="//ardamis.com/" />
<style type="text/css">
#wrapper {
	font: 15px Verdana, Arial, Helvetica, sans-serif;
	margin: 40px 100px 0 100px;
}
.box {
	border: 1px solid #e5e5e5;
	padding: 6px;
	background: #f5f5f5;
}
</style>
</head>

<body>
<div id="wrapper">

<h2>Download Key Generator</h2>

<?php
// A script to generate unique download keys for the purpose of protecting downloadable goods

require ('dbconnect.php');

	if(empty($_SERVER['REQUEST_URI'])) {
    	$_SERVER['REQUEST_URI'] = $_SERVER['SCRIPT_NAME'];
	}

	// Strip off query string so dirname() doesn't get confused
	$url = preg_replace('/\?.*$/', '', $_SERVER['REQUEST_URI']);
	$folderpath = 'http://'.$_SERVER['HTTP_HOST'].'/'.ltrim(dirname($url), '/').'/';


// Generate the unique download key
	$key = uniqid(md5(rand()));
//	echo "key: " . $key . "<br />";
	
// Get the activation time
	$time = date('U');
//	echo "time: " . $time . "<br />";
	
// Generate the link
	echo "<p>Here's a new download link:</p>";
	echo "<p><span class=\"box\">" . $folderpath . "download.php?id=" . $key . "</span></p>";

	
// Write the key and activation time to the database as a new row
	$registerid = mysql_query("INSERT INTO downloadkey (uniqueid,timestamp) VALUES(\"$key\",\"$time\")") or die(mysql_error());
?>

<p>&nbsp;</p>
<p>Each time you refresh this page, a unique download key is generated and saved to a database.  Copy and paste the download link into an email to allow the recipient access to the download.</p>
<p>This key will be valid for a certain amount of time and number of downloads, which can be set in the download.php script.  The key will expire and no longer be usable when the first of these conditions is exceeded.</p>
<p>The download page has been written to force the browser to begin the download immediately.  This will  prevent the recipient of the email from discovering the location of the actual download file.</p>

</div>
</body>
</html>

The download.php page

The URL generated by downloadkey.php points to this page. It contains the key validation script and then forces the browser to begin the download if it finds the key is valid. 

<?php
// Set the maximum number of downloads (actually, the number of page loads)
$maxdownloads = "2";
// Set the key's viable duration in seconds (86400 seconds = 24 hours)
$maxtime = "86400";

require ('dbconnect.php');

	if(get_magic_quotes_gpc()) {
        $id = stripslashes($_GET['id']);
	}else{
		$id = $_GET['id'];
	}

	// Get the key, timestamp, and number of downloads from the database
	$query = sprintf("SELECT * FROM downloadkey WHERE uniqueid= '%s'",
	mysql_real_escape_string($id, $link));
	$result = mysql_query($query) or die(mysql_error());
	$row = mysql_fetch_array($result);
	if (!$row) { 
		echo "The download key you are using is invalid.";
	}else{
		$timecheck = date('U') - $row['timestamp'];
		
		if ($timecheck >= $maxtime) {
			echo "This key has expired (exceeded time allotted).<br />";
		}else{
			$downloads = $row['downloads'];
			$downloads += 1;
			if ($downloads > $maxdownloads) {
				echo "This key has expired (exceeded allowed downloads).<br />";
			}else{
				$sql = sprintf("UPDATE downloadkey SET downloads = '".$downloads."' WHERE uniqueid= '%s'",
	mysql_real_escape_string($id, $link));
				$incrementdownloads = mysql_query($sql) or die(mysql_error());
				
// Debug		echo "Key validated.";

// Force the browser to start the download automatically

/*
	Variables: 
		$file = real name of actual download file on the server
		$filename = new name of local download file - this is what the visitor's file will actually be called when he/she saves it
*/

   ob_start();
   $mm_type="application/octet-stream";
   $file = "actual_download.zip";
   $filename = "bogus_download_name.zip";
 
   header("Cache-Control: public, must-revalidate");
   header("Pragma: no-cache");
   header("Content-Type: " . $mm_type);
   header("Content-Length: " .(string)(filesize($file)) );
   header('Content-Disposition: attachment; filename="'.$filename.'"');
   header("Content-Transfer-Encoding: binary\n");
 
   ob_end_clean();
   readfile($file);

			}
		}
	}
?>

The dbconnect.php script (database connection)

This is the PHP include referenced by both scripts that contains the database link.

<?php
// Connect to database "download" using: dbname , username , password 
    $link = mysql_connect('localhost', 'root', '') or die("Could not connect: " . mysql_error());
    mysql_select_db("download") or die(mysql_error());
?>

This file will almost certainly require some editing. You will need to specify a host name for your MySQL server and a MySQL username and password in that file at mysql_connect('localhost', 'root', '') so that you can connect to the database you’ve set up. It’s extremely unlikely that your production MySQL database will be installed on localhost with a user “root” and no password.

That’s all there is to it. Whenever you want to give someone access to the download, visit the downloadkey.php page. It will generate a unique key code, save it to a database, and print out a URL that you can copy and paste into an email or whatever. The page at that URL checks to see if the key code is legit, then checks to see if the code is less than X hours old, then checks to see if it has been used less than X times. The visitor will get a descriptive message for the first unmet condition and the script will terminate. If all three conditions are met, the download starts automatically.

Security Note:
As the commenter Bruno Ng points out, submitting information via the form fields when using this script sends your database connection info in plain text, and that’s a bad thing.

Therefore, the form field method should only be used for testing local databases.

If you need to test production databases, your database connection info should be hard coded in the PHP script (which isn’t hard to do – look around lines 72-75).

A simple page for testing and troubleshooting a connection to a MySQL database. The PHP script will test the server address, username and password. If the database field is left empty, it will return a list of available databases. Testing a specific database is optional, but if a database name is supplied, it will return a list of the tables in that database (if any exist).

Due to problems with the Syntax Highlighter plugin producing invalid code when copying and pasting, here is a link to the code in plain text. The code below is just for reference.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>MySQL Connection Test</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<style type="text/css">
#wrapper {
	width: 600px;
	margin: 20px auto 0;
	font: 1.2em Verdana, Arial, sans-serif;
}
input {
	font-size: 1em;
}
#submit {
	padding: 4px 8px;
}
</style>
</head>

<body>

<div id="wrapper">

<?php
	$action = htmlspecialchars($_GET['action'], ENT_QUOTES);
?>

<?php if (!$action) { ?>

	<h1>MySQL connection test</h1>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>?action=test" id="mail" method="post">

	<table cellpadding="2">
		<tr>
			<td>Hostname</td>
			<td><input type="text" name="hostname" id="hostname" value="" size="30" tabindex="1" /></td>
			<td>(usually "localhost")</td>
		</tr>
		<tr>
			<td>Username</td>
			<td><input type="text" name="username" id="username" value="" size="30" tabindex="2" /></td>
			<td></td>
		</tr>
		<tr>
			<td>Password</td>
			<td><input type="text" name="password" id="password" value="" size="30" tabindex="3" /></td>
			<td></td>
		</tr>
		<tr>
			<td>Database</td>
			<td><input type="text" name="database" id="database" value="" size="30" tabindex="4" /></td>
			<td>(optional)</td>
		</tr>
		<tr>
			<td></td>
			<td><input type="submit" id="submit" value="Test Connection" tabindex="5" /></td>
			<td></td>
		</tr>
	</table>

</form>

<?php } ?>

<?php if ($action == "test") {

// The variables have not been adequately sanitized to protect against SQL Injection attacks: http://us3.php.net/mysql_real_escape_string

	$hostname = trim($_POST['hostname']);
	$username = trim($_POST['username']);
	$password = trim($_POST['password']);
	$database = trim($_POST['database']);

	$link = mysql_connect("$hostname", "$username", "$password");
		if (!$link) {
			echo "<p>Could not connect to the server '" . $hostname . "'</p>\n";
        	echo mysql_error();
		}else{
			echo "<p>Successfully connected to the server '" . $hostname . "'</p>\n";
//			printf("MySQL client info: %s\n", mysql_get_client_info());
//			printf("MySQL host info: %s\n", mysql_get_host_info());
//			printf("MySQL server version: %s\n", mysql_get_server_info());
//			printf("MySQL protocol version: %s\n", mysql_get_proto_info());
		}
	if ($link && !$database) {
		echo "<p>No database name was given. Available databases:</p>\n";
		$db_list = mysql_list_dbs($link);
		echo "<pre>\n";
		while ($row = mysql_fetch_array($db_list)) {
     		echo $row['Database'] . "\n";
		}
		echo "</pre>\n";
	}
	if ($database) {
    $dbcheck = mysql_select_db("$database");
		if (!$dbcheck) {
        	echo mysql_error();
		}else{
			echo "<p>Successfully connected to the database '" . $database . "'</p>\n";
			// Check tables
			$sql = "SHOW TABLES FROM `$database`";
			$result = mysql_query($sql);
			if (mysql_num_rows($result) > 0) {
				echo "<p>Available tables:</p>\n";
				echo "<pre>\n";
				while ($row = mysql_fetch_row($result)) {
					echo "{$row[0]}\n";
				}
				echo "</pre>\n";
			} else {
				echo "<p>The database '" . $database . "' contains no tables.</p>\n";
				echo mysql_error();
			}
		}
	}
}
?>

</div><!-- end #wrapper -->
</body>
</html>

I’ve been developing a PHP/MySQL web application that will be accessed by multiple users. These users will be both viewing and editing records in the database. Obviously, any situation in which multiple users may be performing operations on the same record puts the integrity of the data at risk.

In the case of my application, there is a very real possibility (a certainty, actually) that two or more people will open the same record at the same time, make changes, and attempt to save these changes. This common concurrent execution problem is known as the “lost update”.

User A opens record “A”.
User B opens record “A”.
User A saves changes to record “A”.
User B saves changes to record “A”.

In this example, User A’s changes are lost – replaced by User B’s changes.

I started to look for a method of preventing this sort of data loss. At first, I wanted to lock the record using a sort of check-in/check-out system. User A would check-out record “A”, and then have exclusive write access to that record until it was checked back in as part of the saving process. There were a number of problems with this method, foremost that User A may decide to not make any changes and so not save the record, which would leave the record in a checked-out state until further administrative action was taken to unlock it.

For awhile, I tried to come up with some ingenious way around this, which usually boiled down to somehow automatically unlocking the record after a period of time. But this is not a satisfactory solution. For one thing, a user may have a legitimate reason to keep the record checked-out for longer periods. For another, User B shouldn’t have to wait for a time-out event to occur if User A is no longer in the record.

So what I eventually came up with is a method of checking whether a record has been changed since it was accessed by the user each time a save is initiated. My particular way of doing this involves comparing timestamps, but other ways exist.

Here’s how I’m implementing my solution to the lost update concurrency issue:

User A creates record “A” and saves it at 9:00 AM. A “last-saved timestamp” of 9:00 AM is generated and saved to the record.

User A opens record “A” at 10:00 AM. An “opened timestamp” of 10:00 AM is generated and written to a hidden (or readonly) input field on the HTML page.
User B opens record “A” at 10:00 AM. An “opened timestamp” of 10:00 AM is generated and written to a hidden (or readonly) input field on the HTML page.

At 10:30 AM, User A attempts to save the record. The “last-saved timestamp” is retrieved from the record. The “opened timestamp” of 10:00 AM is compared to the “last-saved timestamp” of 9:00 AM. Because the record has not been changed since it was opened, the record is saved. A new “last-saved timestamp” of 10:30 AM is generated and saved to the record.

At 11:00 AM, User B attempts to save the record. The “last-saved timestamp” is retrieved from the record. The “opened timestamp” of 10:00 AM is compared to the “last-saved timestamp” of 10:30 AM (User A’s timestamp). Because the record has been changed since it was opened by User B, User B is not allowed to save the record.

User B will have to re-open record “A”, consider the effect that User A’s changes may have, and then make any desired changes.

Unless I’m missing something, this assures that the data from the earlier save will not be overwritten by the later save. To keep things consistent, I’m using PHP to generate all of my timestamps from the server clock, as JavaScript time is based on the user’s system time and is therefore wholly unreliable.

The main drawback, that I see, is extra work for User B, who has to now review the record as saved by User A before deciding what changes to make. But this is going to be necessary anyway, as changes made between when User B opened and attempted to save the record may influence User B’s update.

The strange thing is that I haven’t seen this offered as a solution on any of the pages I found while Googling for solutions to the access control, lost update and other concurrency-related data loss problems. Lots of people acknowledge the problem and potential for data loss, but few offer solutions on the application level – preferring to rely on a database engine’s ability to lock rows.

In this post, I’ll illustrate how to use the IP-to-Country database available from http://ip-to-country.webhosting.info/ to identify the real-world geographic location of visitors to a web page (geolocate) based on their IP addresses. Once you know where a visitor is physically located, you can do all sorts of nifty things, such as send them location-aware content (think language, currency, etc.).

The IP-to-Country Database

There are a number of databases that associate IP address ranges with countries. I’ll be using the one at http://ip-to-country.webhosting.info/. It is available as a CSV file, so the first step will be getting the contents migrated into a MySQL database.

The MySQL Part

First, create a MySQL database. I like phpMyAdmin, but use whatever method you are comfortable with. Name the database ip2country.

Once the database has been created, we need to create a table. In phpMyAdmin, click on the SQL tab and enter the following lines:

CREATE TABLE `ip2country` (
  `ipFrom` int(15) NOT NULL default '0',
  `ipTo` int(15) NOT NULL default '0',
  `country2` char(2) NOT NULL default '',
  `country3` char(3) NOT NULL default '',
  `country` varchar(25) NOT NULL default ''
);

This creates a table, also called ip2country, and five fields (ipFrom, ipTo, country2, country3, and country) to hold the data from the CSV.

The next step is to get the contents of the CSV file into the MySQL database, and there are two ways to do this.

If you are using MySQL version 5.0 or greater, the fastest way is to use the LOAD DATA INFILE statement. The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. (For details, visit http://dev.mysql.com/doc/refman/5.0/en/load-data.html.) On my computer, it takes less than half a second to get all 79,000+ rows into the MySQL database. To use this method, click on the SQL tab and enter the following lines, changing the path to “ip-to-country.csv” to the actual path to the file on your local computer.

LOAD DATA INFILE 'D:/PATH/TO/FILE/ip-to-country.csv' INTO TABLE ip2country
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY """"
LINES TERMINATED BY "\n";

The other way to populate the MySQL database is by running a PHP script that writes the records from the CSV one line at a time. To use this method, upload the CSV to a directory on your web site, then create a PHP file with the following lines, editing the script to use your database address, username, and password:

<?php

	// Adjust for the database address, username, password 
	$link = mysql_connect('localhost', 'root', '');
	if (!$link) {
		die('Could not connect: ' . mysql_error());
	}

	$db = mysql_select_db("ip2country") or die(mysql_error());

	// Set the variable $row to zero to begin counting entries
	$row = 0;

	// The ip-to-country.csv must be in the same directory as this php file
	$handle = fopen("ip-to-country.csv", "r");

	// Required to prevent timeout
	set_time_limit(300);

	// While rows exist, write each into the database
	while ($data = fgetcsv($handle, 1000, ",")) {
		$query = "INSERT INTO ip2country(`ipFrom`, `ipTo`, `country2`, `country3`, `country`) VALUES('".$data[0]."', '".$data[1]."', '".$data[2]."', '".$data[3]."', '". addslashes($data[4]) ."')";
		$result = mysql_query($query) or die("Invalid query: " . mysql_error().__LINE__.__FILE__);
		$row++;
	}

	// Close the database connection
	fclose ($handle);

	// Print a confirmation
	echo "All done! " . $row . " rows added to database.";
	
?>

Upload the PHP script to the same directory as the CSV file and then browse to it. It will migrate the contents of the CSV file into the MySQL database and then give a little confirmation of how many rows were added when it is done.

If your script times out, you may need to either increase the value of set_time_limit(300); or move that part of the script inside the while loop.

The PHP Part

Now that the database is in place, it’s time to do something with it. The following PHP script will get the visitor’s IP address and print out the corresponding country.

<?php

	// Figure out the visitor's IP address
	$ip = $_SERVER['REMOTE_ADDR'];

	// Establish a database connection (adjust address, username, and password)
	$dbh = mysql_connect("localhost", "root", "") or die("Could not connect: " . mysql_error());
	mysql_select_db("ip2country");

	// Create a query string
	$country_query = "SELECT country2, country FROM ip2country WHERE ipFrom<=INET_ATON('" . $ip . "') AND ipTo>=INET_ATON('" . $ip . "')";
		  
	// Execute the query
	$country_exec = mysql_query($country_query);

	// Fetch the record set into an array
	$ccode_array = mysql_fetch_array($country_exec);

	// Close the database connection
	mysql_close($dbh);

	// Get the country code from the array and save it as a variable
	$country_code = $ccode_array['country2'];

	// Get the country name from the array and save it as a variable
	$country_name = $ccode_array['country'];
	
	// If the database contains a match, print out the country name and country code, otherwise print the IP address
	if ($country_code != "") { 
	echo '<p>The IP-to-Country database contains a match for your ip address: ' . $ip . '</p>';
	echo '<p>You are located in ' . $country_name . ', and the country code is ' . $country_code . '</p>';
	}else{
	echo '<p>Sorry. The IP-to-Country database does not contain a match for your ip address: ' . $ip . '</p>';
	}

?>

Summary

That’s it. You now have a way to determine each visitor’s physical location. Use geolocation carefully, and always provide a fall-back in the event the database does not contain a given IP. IP addresses are constantly being assigned and revoked, so keeping your database up-to-date is critical.