v0.5.1 secret files

2 files changed, 78 insertions, 56 deletions

diff --git a/install/index.php b/install/index.php
index d40959f..519e3c5 100644
--- a/install/index.php
+++ b/install/index.php
@@ -22,72 +22,73 @@ require('../conf.php');
 if (isset($_GET['create_tables'])) {
 	try {
 		$_pdo->query("CREATE TABLE IF NOT EXISTS `".Constants::db_prefix."assignment` (
-			  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
-			  `offerId` smallint(5) unsigned NOT NULL,
-			  `title` tinytext NOT NULL,
-			  `description` text NOT NULL,
-			  `hours` float NOT NULL,
-			  `price_per_hour` float NOT NULL,
-			  `VAT_percentage` float NOT NULL,
-			  PRIMARY KEY (`id`),
-			  UNIQUE KEY `offerId - title` (`offerId`,`title`(255)),
-			  KEY `offerId` (`offerId`)
+				`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
+				`offerId` smallint(5) unsigned NOT NULL,
+				`title` tinytext NOT NULL,
+				`description` text NOT NULL,
+				`hours` float NOT NULL,
+				`price_per_hour` float NOT NULL,
+				`VAT_percentage` float NOT NULL,
+				PRIMARY KEY (`id`),
+				UNIQUE KEY `offerId - title` (`offerId`,`title`(255)),
+				KEY `offerId` (`offerId`)
 			) ENGINE=InnoDB  DEFAULT CHARSET=latin1");
 
 		$_pdo->query("CREATE TABLE IF NOT EXISTS `".Constants::db_prefix."client` (
-			  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
-			  `name` tinytext NOT NULL,
-			  PRIMARY KEY (`id`),
-			  UNIQUE KEY `name` (`name`(255))
+				`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
+				`name` tinytext NOT NULL,
+				PRIMARY KEY (`id`),
+				UNIQUE KEY `name` (`name`(255))
 			) ENGINE=InnoDB  DEFAULT CHARSET=latin1");
 
 		$_pdo->query("CREATE TABLE IF NOT EXISTS `".Constants::db_prefix."contact` (
-			  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
-			  `clientId` smallint(5) unsigned NOT NULL,
-			  `name` tinytext NOT NULL,
-			  `email` varchar(680) NOT NULL,
-			  `address` tinytext NOT NULL,
-			  `address_2` tinytext,
-			  `postal_code` varchar(7) NOT NULL,
-			  `city` tinytext NOT NULL,
-			  `country` tinytext NOT NULL,
-			  `language` varchar(3) NOT NULL DEFAULT 'en',
-			  PRIMARY KEY (`id`),
-			  UNIQUE KEY `clientId-name` (`clientId`,`name`(255)),
-			  KEY `clientId` (`clientId`)
+				`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
+				`clientId` smallint(5) unsigned NOT NULL,
+				`name` tinytext NOT NULL,
+				`email` varchar(680) NOT NULL,
+				`address` tinytext NOT NULL,
+				`address_2` tinytext,
+				`postal_code` varchar(7) NOT NULL,
+				`city` tinytext NOT NULL,
+				`country` tinytext NOT NULL,
+				`language` varchar(3) NOT NULL DEFAULT 'en',
+				PRIMARY KEY (`id`),
+				UNIQUE KEY `clientId-name` (`clientId`,`name`(255)),
+				KEY `clientId` (`clientId`)
 			) ENGINE=InnoDB  DEFAULT CHARSET=latin1");
 
 		$_pdo->query("CREATE TABLE IF NOT EXISTS `".Constants::db_prefix."discount` (
-			  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
-			  `offerId` smallint(5) unsigned NOT NULL,
-			  `title` tinytext NOT NULL,
-			  `description` text NOT NULL,
-			  `value` float unsigned NOT NULL,
+				`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
+				`offerId` smallint(5) unsigned NOT NULL,
+				`title` tinytext NOT NULL,
+				`description` text NOT NULL,
+				`value` float unsigned NOT NULL,
 				`VAT_percentage` float NOT NULL,
 				PRIMARY KEY (`id`)
 			) ENGINE=InnoDB DEFAULT CHARSET=latin1;");
 
 		$_pdo->query("CREATE TABLE IF NOT EXISTS `".Constants::db_prefix."file` (
-			  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
-			  `filename` varchar(100) NOT NULL,
-			  PRIMARY KEY (`id`),
-			  UNIQUE KEY `filename` (`filename`)
+				`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
+				`filename` varchar(100) NOT NULL,
+				`secret_key` varchar(63) DEFAULT NULL,
+				PRIMARY KEY (`id`),
+				UNIQUE KEY `filename` (`filename`)
 			) ENGINE=InnoDB  DEFAULT CHARSET=latin1");
 
 		$_pdo->query("CREATE TABLE IF NOT EXISTS `".Constants::db_prefix."offer` (
-			  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
-			  `contactId` smallint(5) unsigned NOT NULL,
-			  `start_date` date NOT NULL,
-			  `end_date` date NOT NULL,
-			  `invoice_date` date NOT NULL,
-			  `accepted` tinyint(1) unsigned NOT NULL DEFAULT '0',
+				`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
+				`contactId` smallint(5) unsigned NOT NULL,
+				`start_date` date NOT NULL,
+				`end_date` date NOT NULL,
+				`invoice_date` date NOT NULL,
+				`accepted` tinyint(1) unsigned NOT NULL DEFAULT '0',
 				`invoice_fileId` smallint(5) unsigned DEFAULT NULL,
 				`payment_key` varchar(63) DEFAULT NULL,
-			  PRIMARY KEY (`id`),
-			  UNIQUE KEY `invoice_fileId` (`invoice_fileId`),
-			  KEY `contactId` (`contactId`),
-			  KEY `contactId_2` (`contactId`),
-			  KEY `invoice_fileId_2` (`invoice_fileId`)
+				PRIMARY KEY (`id`),
+				UNIQUE KEY `invoice_fileId` (`invoice_fileId`),
+				KEY `contactId` (`contactId`),
+				KEY `contactId_2` (`contactId`),
+				KEY `invoice_fileId_2` (`invoice_fileId`)
 			) ENGINE=InnoDB  DEFAULT CHARSET=latin1");
 
 		$_pdo->query("CREATE TABLE IF NOT EXISTS `payment` (
@@ -100,24 +101,24 @@ if (isset($_GET['create_tables'])) {
 			) ENGINE=InnoDB DEFAULT CHARSET=latin1;");
 
 		$_pdo->query("CREATE TABLE IF NOT EXISTS `".Constants::db_prefix."user` (
-			  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
+				`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 				`username` varchar(24) NOT NULL,
 				`password` varchar(255) NOT NULL,
 				PRIMARY KEY (`id`)
 			) ENGINE=InnoDB DEFAULT CHARSET=latin1;");
 
 		$_pdo->query("ALTER TABLE `".Constants::db_prefix."assignment`
-			  ADD CONSTRAINT `assignment_ibfk_1` FOREIGN KEY (`offerId`) REFERENCES `".Constants::db_prefix."offer` (`id`)");
+				ADD CONSTRAINT `assignment_ibfk_1` FOREIGN KEY (`offerId`) REFERENCES `".Constants::db_prefix."offer` (`id`)");
 
-		$_pdo->query("ALTER TABLE `".Constants::db_prefix."contact`
-			  ADD CONSTRAINT `contact_ibfk_1` FOREIGN KEY (`clientId`) REFERENCES `".Constants::db_prefix."client` (`id`)");
+		$_pdo->query("ALTER 	LE `".Constants::db_prefix."contact`
+				ADD CONSTRAINT `contact_ibfk_1` FOREIGN KEY (`clientId`) REFERENCES `".Constants::db_prefix."client` (`id`)");
 
-		$_pdo->query("ALTER TABLE `".Constants::db_prefix."discount`
-			  ADD CONSTRAINT `discount_ibfk_1` FOREIGN KEY (`offerId`) REFERENCES `".Constants::db_prefix."offer` (`id`);");
+		$_pdo->query("ALTER 	LE `".Constants::db_prefix."discount`
+				ADD CONSTRAINT `discount_ibfk_1` FOREIGN KEY (`offerId`) REFERENCES `".Constants::db_prefix."offer` (`id`);");
 
-		$_pdo->query("ALTER TABLE `".Constants::db_prefix."offer`
-			  ADD CONSTRAINT `offer_ibfk_1` FOREIGN KEY (`invoice_fileId`) REFERENCES `".Constants::db_prefix."file` (`id`),
-			  ADD CONSTRAINT `offer_ibfk_2` FOREIGN KEY (`contactId`) REFERENCES `".Constants::db_prefix."contact` (`id`)");
+		$_pdo->query("ALTER 	LE `".Constants::db_prefix."offer`
+				ADD CONSTRAINT `offer_ibfk_1` FOREIGN KEY (`invoice_fileId`) REFERENCES `".Constants::db_prefix."file` (`id`),
+				ADD CONSTRAINT `offer_ibfk_2` FOREIGN KEY (`contactId`) REFERENCES `".Constants::db_prefix."contact` (`id`)");
 
 		$_pdo->query("CREATE UNIQUE INDEX `payment_uniq_1` ON `".Constants::db_prefix."payment` (`offerId`);");
 		$_pdo->query("ALTER TABLE `payment`
diff --git a/install/upgrade.php b/install/upgrade.php
index 8ead230..db0c913 100644
--- a/install/upgrade.php
+++ b/install/upgrade.php
@@ -31,6 +31,8 @@ function lower_version($that, $new) {
 	for ($i = 0; $i < count($new); $i++) {
 		if ($new[$i] > $that[$i]) {
 			return true;
+		} elseif ($new[$i] < $that[$i]) {
+			return false;
 		}
 	}
 	return false;
@@ -111,15 +113,34 @@ if (isset($_GET['upgrade'])) {
 		try {
 			$_pdo->query("ALTER TABLE `".Constants::db_prefix."offer`
 				ADD `payment_key` VARCHAR(63) DEFAULT NULL;");
+
 			$_pdo->query("ALTER TABLE `".Constants::db_prefix."payment`
 				ADD `braintree_id` VARCHAR(36) DEFAULT NULL,
 				ADD `braintree_status` VARCHAR (63) NULL DEFAULT NULL;");
+
 			$_pdo->query("CREATE UNIQUE INDEX `payment_uniq_1` ON `".Constants::db_prefix."payment` (`offerId`);");
 		} catch (PDOException $e) {
 			echo "Altering the database structure failed with a PDOException ({$e->getCode()}): {$e->getMessage()}<br/>" . $e->getTraceAsString();
 		}
 	}
 
+	if (lower_version($_GET['upgrade'], '0.5.1')) {
+		try {
+			$_pdo->query("ALTER TABLE `".Constants::db_prefix."file`
+				ADD `secret_key` VARCHAR(63) DEFAULT NULL;");
+
+			$files = $_pdo->query("SELECT `id` FROM `".Constants::db_prefix."file` WHERE `secret_key` IS NULL;");
+			$stmt = $_pdo->prepare("UPDATE `".Constants::db_prefix."file` SET `secret_key`=? WHERE `id`=?");
+			foreach ($files->fetchAll(PDO::FETCH_ASSOC) as $file) {
+				$key = preg_replace('/[^\w]+/', '',
+					base64_encode(openssl_random_pseudo_bytes(45)));
+				$stmt->execute([$key, $file['id']]);
+			}
+		} catch (PDOException $e) {
+			echo "Altering the database structure failed with a PDOException ({$e->getCode()}): {$e->getMessage()}<br/>" . $e->getTraceAsString();
+		}
+	}
+
 	echo "<br/>All done.";
 }
 ?>