add cronjob to verify user webpages and twitter accounts

Signed-off-by: Bjoern Schiessle <bjoern@schiessle.org>

4 files changed, 250 insertions, 125 deletions

diff --git a/mysql.dmp b/mysql.dmp
index ab3c5cf..3a9cf43 100755
--- a/mysql.dmp
+++ b/mysql.dmp
@@ -41,6 +41,18 @@ CREATE TABLE IF NOT EXISTS `users` (
   KEY `federationId` (`federationId`(191))
 ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
+DROP TABLE IF EXISTS `toVerify`;
+CREATE TABLE IF NOT EXISTS `toVerify` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `userId` int(11) NOT NULL,
+  `storeId` int(11) NOT NULL,
+  `property` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
+  `location` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
+  `tries` int(11) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+
 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
diff --git a/server/lib/UserManager.php b/server/lib/UserManager.php
index 6727199..cd340b3 100644
--- a/server/lib/UserManager.php
+++ b/server/lib/UserManager.php
@@ -70,18 +70,18 @@ LIMIT 50');
 	}
 
 	private function getExactCloudId($cloudId) {
-	    $stmt = $this->db->prepare('SELECT id FROM users WHERE federationId = :id');
-	    $stmt->bindParam(':id', $cloudId);
-	    $stmt->execute();
-	    $data = $stmt->fetch();
+		$stmt = $this->db->prepare('SELECT id FROM users WHERE federationId = :id');
+		$stmt->bindParam(':id', $cloudId);
+		$stmt->execute();
+		$data = $stmt->fetch();
 
-        if (!$data) {
-            return [];
-        }
+		if (!$data) {
+			return [];
+		}
 
-        return $this->getForUserId((int)$data['id']);
+		return $this->getForUserId((int)$data['id']);
 
-    }
+	}
 
 	private function getForUserId($userId) {
 		$stmt = $this->db->prepare('SELECT * FROM users WHERE id = :id');
@@ -172,7 +172,6 @@ LIMIT 50');
 		$stmt->bindParam(':timestamp', $timestamp, \PDO::PARAM_INT);
 		$stmt->execute();
 		$stmt->closeCursor();
-
 		$fields = ['name', 'email', 'address', 'website', 'twitter', 'phone'];
 
 		$stmt = $this->db->prepare('SELECT * FROM store WHERE userId = :userId');
@@ -180,7 +179,6 @@ LIMIT 50');
 		$stmt->execute();
 		$rows = $stmt->fetchAll();
 		$stmt->closeCursor();
-
 		foreach ($rows as $row) {
 			$key = $row['k'];
 			$value = $row['v'];
@@ -197,6 +195,7 @@ LIMIT 50');
 			} else {
 				// Key present check if we need to update
 				if ($data[$key] === $value) {
+					$this->needToVerify($id, $row['id'], $data, $key);
 					continue;
 				}
 				$stmt = $this->db->prepare('UPDATE store SET v = :v, valid = 0 WHERE id = :id');
@@ -227,7 +226,22 @@ LIMIT 50');
 			$storeId = $this->db->lastInsertId();
 			$stmt->closeCursor();
 
-			$this->emailValidator->emailUpdated($data[$field], $storeId);
+			if ($field === 'email') {
+				$this->emailValidator->emailUpdated($data[$field], $storeId);
+			}
+		}
+	}
+
+	private function needToVerify($userId, $storeId, $data, $key) {
+		if (isset($data['verificationStatus'][$key]) && $data['verificationStatus'][$key] === '1') {
+			$tries = 0;
+			$stmt = $this->db->prepare('INSERT INTO toVerify (userId, storeId, property, location, tries) VALUES (:userId, :storeId, :property, :location, :tries)');
+			$stmt->bindParam(':userId', $userId, \PDO::PARAM_INT);
+			$stmt->bindParam(':storeId', $storeId, \PDO::PARAM_INT);
+			$stmt->bindParam(':property', $key);
+			$stmt->bindParam(':location', $data[$key]);
+			$stmt->bindParam(':tries', $tries, \PDO::PARAM_INT);
+			$stmt->execute();
 		}
 	}
 
@@ -244,11 +258,11 @@ LIMIT 50');
 		$cloudId = $body['message']['data']['federationId'];
 
 		try {
-            $verified = $this->verifyRequest($cloudId, $body['message'], $body['signature']);
-        }  catch(\Exception $e) {
-            $response->withStatus(400);
-            return $response;
-        }
+			$verified = $this->verifyRequest($cloudId, $body['message'], $body['signature']);
+		}  catch(\Exception $e) {
+			$response->withStatus(400);
+			return $response;
+		}
 
 		if ($verified) {
 			$result = $this->insertOrUpdate($cloudId, $body['message']['data'], $body['message']['timestamp']);
@@ -263,85 +277,147 @@ LIMIT 50');
 		return $response;
 	}
 
-    public function delete(Request $request, Response $response) {
-        $body = json_decode($request->getBody(), true);
-
-        if ($body === null || !isset($body['message']) || !isset($body['message']['data']) ||
-            !isset($body['message']['data']['federationId']) || !isset($body['signature']) ||
-            !isset($body['message']['timestamp'])) {
-            $response->withStatus(400);
-            return $response;
-        }
-
-        $cloudId = $body['message']['data']['federationId'];
-
-        try {
-            $verified = $this->verifyRequest($cloudId, $body['message'], $body['signature']);
-        }  catch(\Exception $e) {
-            $response->withStatus(400);
-            return $response;
-        }
-
-
-        if ($verified) {
-            $result = $this->deleteDBRecord($cloudId);
-            if ($result === false) {
-                $response->withStatus(404);
-            }
-        } else {
-            // ERROR OUT
-            $response->withStatus(403);
-        }
-
-        return $response;
-    }
-
-    /**
-     * check signature of incoming request
-     *
-     * @param string $cloudId
-     * @param string $message
-     * @param string $signature
-     * @return bool
-     * @throws \Exception
-     */
-    protected function verifyRequest($cloudId, $message, $signature) {
-        // Get fed id
-        list($user, $host) = $this->splitCloudId($cloudId);
-
-        // Retrieve public key && store
-        $ocsreq = new \GuzzleHttp\Psr7\Request(
-            'GET',
-            'http://'.$host . '/ocs/v2.php/identityproof/key/' . $user,
-            [
-                'OCS-APIREQUEST' => 'true',
-                'Accept' => 'application/json',
-            ]);
-
-        $client = new Client();
-        $ocsresponse = $client->send($ocsreq, ['timeout' => 10]);
-
-        $ocsresponse = json_decode($ocsresponse->getBody(), true);
-
-        if ($ocsresponse === null || !isset($ocsresponse['ocs']) ||
-            !isset($ocsresponse['ocs']['data']) || !isset($ocsresponse['ocs']['data']['public'])) {
-            throw new \BadMethodCallException();
-        }
-
-        $key = $ocsresponse['ocs']['data']['public'];
-
-        // verify message
-        $message = json_encode($message);
-        $signature= base64_decode($signature);
-
-        $res = openssl_verify($message, $signature, $key, OPENSSL_ALGO_SHA512);
-
-        return $res === 1;
-
-    }
-
-
-    /**
+	public function delete(Request $request, Response $response) {
+		$body = json_decode($request->getBody(), true);
+
+		if ($body === null || !isset($body['message']) || !isset($body['message']['data']) ||
+			!isset($body['message']['data']['federationId']) || !isset($body['signature']) ||
+			!isset($body['message']['timestamp'])) {
+			$response->withStatus(400);
+			return $response;
+		}
+
+		$cloudId = $body['message']['data']['federationId'];
+
+		try {
+			$verified = $this->verifyRequest($cloudId, $body['message'], $body['signature']);
+		}  catch(\Exception $e) {
+			$response->withStatus(400);
+			return $response;
+		}
+
+
+		if ($verified) {
+			$result = $this->deleteDBRecord($cloudId);
+			if ($result === false) {
+				$response->withStatus(404);
+			}
+		} else {
+			// ERROR OUT
+			$response->withStatus(403);
+		}
+
+		return $response;
+	}
+
+	public function verify(Request $request, Response $response) {
+		$verificationRequests = $this->getOpenVerificationRequests();
+		foreach ($verificationRequests as $verify) {
+			$success = false;
+			switch ($verify['property']) {
+				case 'twitter':
+					//ToDo try to Verify Twitter account
+					$success = $this->verifyTwitter();
+					break;
+				case 'website':
+					$success = $this->verifyWebpage($verify);
+					break;
+			}
+			if ($success) {
+				// ToDo update verification status
+				$this->removeOpenVerificationRequest($verify);
+			}
+		}
+	}
+
+	/**
+	 * get open verification Requests
+	 *
+	 * @return array
+	 */
+	private function getOpenVerificationRequests() {
+		$stmt = $this->db->prepare('SELECT * FROM toVerify LIMIT 10');
+		$stmt->execute();
+		$result = $stmt->fetchAll();
+		$stmt->closeCursor();
+		return $result;
+	}
+
+	/**
+	 * @param array $data
+	 * @return bool
+	 */
+	private function verifyTwitter($data) {
+		// ToDo get data from verify table (includes $cloudId, $location)
+		// ToDo get proof from twitter user $location
+		// ToDo split $message & $signature
+		// ToDo "verifyRequest" needs to be able to handle the shortened md5 signature from twitter
+		$result = $this->verifyRequest($cloudId, $message, $signature);
+
+		return result;
+
+	}
+
+	/**
+	 * @param array $data
+	 * @return bool
+	 */
+	private function verifyWebpage($data) {
+		// ToDo get data from verify table (includes $cloudId, $location)
+		// ToDo get proof from webpage $location
+		// ToDo split $message & $signature
+		return false;
+		$result = $this->verifyRequest($cloudId, $message, $signature);
+
+		return $result;
+	}
+
+	/**
+	 * check signature of incoming request
+	 *
+	 * @param string $cloudId
+	 * @param string $message
+	 * @param string $signature
+	 * @return bool
+	 * @throws \Exception
+	 */
+	protected function verifyRequest($cloudId, $message, $signature) {
+		// Get fed id
+		list($user, $host) = $this->splitCloudId($cloudId);
+
+		// Retrieve public key && store
+		$ocsreq = new \GuzzleHttp\Psr7\Request(
+			'GET',
+			'http://'.$host . '/ocs/v2.php/identityproof/key/' . $user,
+			[
+				'OCS-APIREQUEST' => 'true',
+				'Accept' => 'application/json',
+			]);
+
+		$client = new Client();
+		$ocsresponse = $client->send($ocsreq, ['timeout' => 10]);
+
+		$ocsresponse = json_decode($ocsresponse->getBody(), true);
+
+		if ($ocsresponse === null || !isset($ocsresponse['ocs']) ||
+			!isset($ocsresponse['ocs']['data']) || !isset($ocsresponse['ocs']['data']['public'])) {
+			throw new \BadMethodCallException();
+		}
+
+		$key = $ocsresponse['ocs']['data']['public'];
+
+		// verify message
+		$message = json_encode($message);
+		$signature= base64_decode($signature);
+
+		$res = openssl_verify($message, $signature, $key, OPENSSL_ALGO_SHA512);
+
+		return $res === 1;
+
+	}
+
+
+	/**
 	 * @param string $cloudId
 	 * @param string[] $data
 	 * @param int $timestamp
@@ -372,32 +448,32 @@ LIMIT 50');
 		return true;
 	}
 
-    /**
-     * Delete all personal data. We keep the basic user entry with the
-     * federated cloud ID in order to propagate the changes
-     *
-     * @param string $cloudId
-     * @return bool
-     */
-    private function deleteDBRecord($cloudId) {
-
-        $stmt = $this->db->prepare('SELECT * FROM users WHERE federationId = :federationId');
-        $stmt->bindParam(':federationId', $cloudId);
-        $stmt->execute();
-        $row = $stmt->fetch();
-        $stmt->closeCursor();
-
-        // If we can't find the user
-        if ($row === false) {
-            return false;
-        }
-
-        // delete user data
-        $stmt = $this->db->prepare('DELETE FROM store WHERE userId = :userId');
-        $stmt->bindParam(':userId', $row['id']);
-        $stmt->execute();
-        $stmt->closeCursor();
-
-        return true;
-    }
+	/**
+	 * Delete all personal data. We keep the basic user entry with the
+	 * federated cloud ID in order to propagate the changes
+	 *
+	 * @param string $cloudId
+	 * @return bool
+	 */
+	private function deleteDBRecord($cloudId) {
+
+		$stmt = $this->db->prepare('SELECT * FROM users WHERE federationId = :federationId');
+		$stmt->bindParam(':federationId', $cloudId);
+		$stmt->execute();
+		$row = $stmt->fetch();
+		$stmt->closeCursor();
+
+		// If we can't find the user
+		if ($row === false) {
+			return false;
+		}
+
+		// delete user data
+		$stmt = $this->db->prepare('DELETE FROM store WHERE userId = :userId');
+		$stmt->bindParam(':userId', $row['id']);
+		$stmt->execute();
+		$stmt->closeCursor();
+
+		return true;
+	}
 }
diff --git a/server/cronjob.php b/server/replicationcron.php
index 626034d..5b1555c 100755
--- a/server/cronjob.php
+++ b/server/replicationcron.php
@@ -17,4 +17,3 @@ $app = new \Slim\App($container);
 
 $app->map(['GET'], '/import', 'Replication:import');
 $app->run();
-
diff --git a/server/verifycron.php b/server/verifycron.php
new file mode 100644
index 0000000..ad4dc48
--- /dev/null
+++ b/server/verifycron.php
@@ -0,0 +1,38 @@
+<?php
+/**
+ * @copyright Copyright (c) 2017 Bjoern Schiessle <bjoern@schiessle.org>
+ *
+ * @license GNU AGPL version 3 or any later version
+ *
+ * This program is free software: you can redistribute it and/or modify
+ * it under the terms of the GNU Affero General Public License as
+ * published by the Free Software Foundation, either version 3 of the
+ * License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU Affero General Public License for more details.
+ *
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program.  If not, see <http://www.gnu.org/licenses/>.
+ *
+ */
+
+require __DIR__ . '/vendor/autoload.php';
+
+if (PHP_SAPI !== 'cli') {
+    return;
+}
+
+$env = \Slim\Http\Environment::mock(['REQUEST_URI' => '/verify']);
+
+$settings = require __DIR__ . '/src/config.php';
+$settings['environment'] = $env;
+$container = new \Slim\Container($settings);
+require __DIR__ . '/src/dependencies.php';
+
+$app = new \Slim\App($container);
+
+$app->map(['GET'], '/verify', 'UserManager:verify');
+$app->run();