update
From c8b627002c58899bf88286e140aeb2264aa83117 Mon Sep 17 00:00:00 2001
From: Sherif Ramadan <google...@php.net>
Date: Sun, 9 Sep 2012 14:07:01 -0400
Subject: [PATCH] Updated backend scripts for user notes voting/flagging.
---
entry/user-notes-flag.php | 142 +++++++++++++++++++++++++++++++++++++++++++++
entry/user-notes-vote.php | 105 +++++++++++++++++++++++++++++++++
fetch/user-notes.php | 11 +++-
3 files changed, 256 insertions(+), 2 deletions(-)
create mode 100644 entry/user-notes-flag.php
create mode 100644 entry/user-notes-vote.php
diff --git a/entry/user-notes-flag.php b/entry/user-notes-flag.php
new file mode 100644
index 0000000..e7c2373
--- /dev/null
+++ b/entry/user-notes-flag.php
@@ -0,0 +1,142 @@
+<?php
+/*
+ This script acts as the backend communication API for the user notes flag
feature.
+ Requests come in here from the mirrors to update the database with new flags.
+ master.php.net should respond with either an empty HTTP response body or a
non empty body -- currently "1" -- on
+ success/failure repsectively. An email is then sent out to
php-no...@lists.php.net to notify moderators.
+*/
+
+
+// Check database for IP limits and note id
+function db_flag_note() {
+ $mailto = 'php-no...@lists.php.net';
+ $failto = 'google...@php.net'; // add other failto emails here as needed.
+
+ $db = "phpmasterdb";
+ $username = "nobody";
+ $password = "";
+ $host = "localhost";
+
+ if (!($dbh = @mysql_connect($host, $username, $password))) {
+ return false;
+ }
+
+ if (!(@mysql_select_db($db, $dbh))) {
+ return false;
+ }
+
+
+ if ($ip = filter_input(INPUT_POST, "ip", FILTER_VALIDATE_IP,
FILTER_FLAG_IPV4 | FILTER_FLAG_NO_PRIV_RANGE | FILTER_FLAG_NO_RES_RANGE)) {
+ $ip = mysql_real_escape_string(sprintf("%u",ip2long($ip)));
+ }
+
+ $hostip =
mysql_real_escape_string(sprintf("%u",ip2long($_SERVER['REMOTE_ADDR'])));
+ $id = mysql_real_escape_string((int)$_POST['noteid']);
+ // Check that the note id exists
+ $note_query = "SELECT COUNT(*) AS num, id, sect FROM note WHERE id = '$id'";
+ // Limit 1 flag per note, per client ip, per day
+ $client_query = "SELECT COUNT(*) AS num FROM flags WHERE ip = '$ip' AND ts
>= (NOW() - INTERVAL 1 DAY) AND note_id = '$id'";
+ // Limit 5 flag per note, per host ip, per minute
+ $host_query = "SELECT COUNT(*) AS num FROM flags WHERE hostip = '$hostip'
AND ts >= (NOW() - INTERVAL 1 MINUTE) AND note_id = '$id'";
+
+ if (!($result_note = @mysql_query($note_query))) {
+ return false;
+ }
+ $rows_note = @mysql_fetch_assoc($result_note);
+ if (empty($rows_note) || $rows_note['num'] == 0 || $rows_note['sect'] !==
$_POST['sect']) {
+ return false;
+ }
+ $sect = $rows_note['sect'];
+ $note_id = $rows_note['id'];
+
+
+ if (!($result_client = @mysql_query($client_query))) {
+ return false;
+ }
+ $rows_client = @mysql_fetch_assoc($result_client);
+ if (empty($rows_client) || $rows_client['num'] >= 1) {
+ return false;
+ }
+
+ if (!($result_host = @mysql_query($host_query))) {
+ return false;
+ }
+ $rows_host = @mysql_fetch_assoc($result_host);
+ if (empty($rows_host) || $rows_host['num'] >= 5) {
+ return false;
+ }
+
+ $flag = mysql_real_escape_string($_POST['flag']);
+ $email = mysql_real_escape_string($_POST['email']);
+ $info = mysql_real_escape_string($_POST['info']);
+ $ts = mysql_real_escape_string(date("Y-m-d H:i:s"));
+ $insert_query = "INSERT INTO flags(note_id,ip,hostip,ts,flag,email,info)
VALUES('$id','$ip','$hostip','$ts','$flag','$email','$info')";
+ if (!($result = @mysql_query($insert_query))) {
+ mail ($failto,
+ 'failed manual note flag query',
+ "Query failed: $insert_query\nError: ".mysql_error(),
+ 'From: php-webmas...@lists.php.net',
+ '-fnore...@php.net'
+ );
+ return false;
+ }
+
+ $flag_id = mysql_insert_id();
+ if (!($user = filter_input(INPUT_POST, "email", FILTER_VALIDATE_EMAIL))) {
+ $user = filter_input(INPUT_POST, "email", FILTER_SANITIZE_STRIPPED,
FILTER_FLAG_STRIP_HIGH);
+ $user = str_replace(array("\r", "\n"), "", $user);
+ }
+ $msg = <<<EMAIL_MSG
+A user has flagged a note! Please review it and take any necessary action.
+
+--------------------------------
+Note ID: $note_id
+Flag ID: $flag_id
+Section: $sect
+Email: $user
+Client IP: {$_POST['ip']}
+Host IP: {$_SERVER['REMOTE_ADDR']}
+Reason: {$_POST['flag']}
+Comment: {$_POST['info']}
+--------------------------------
+EMAIL_MSG;
+ mail($mailto,"note $note_id flagged in section '$sect'",$msg,"From:
$user\r\nMessage-ID: <note-$note...@php.net>", "-fnore...@php.net");
+
+ return true;
+}
+
+// Initialize Global vars
+$flags = array(
+ "Spam",
+ "Offensive or abusive language",
+ "This should be a bug report",
+ "Note on wrong/unrelated page",
+ "Invalid or harmful code",
+ "Duplicate note",
+ "This is my note and I want it deleted",
+ "Other",
+);
+
+// Validate the request
+if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
+ header("HTTP/1.0 400", true);
+ exit();
+}
+if (empty($_POST['noteid']) || empty($_POST['sect']) || empty($_POST['flag'])
|| !isset($_POST['email']) ||
+ !isset($_POST['info']) || !isset($_POST['ip'])) {
+ header("HTTP/1.0 400", true);
+ exit();
+}
+if (!in_array($_POST['flag'],$flags, true)) {
+ header("HTTP/1.0 400", true);
+ exit();
+}
+
+// Check master DB for hostip and clientip limits
+if (!db_flag_note()) {
+ header("HTTP/1.0 400", true);
+ exit();
+}
+
+header("HTTP/1.0 200",true);
+echo 1;
diff --git a/entry/user-notes-vote.php b/entry/user-notes-vote.php
new file mode 100644
index 0000000..3ec68b9
--- /dev/null
+++ b/entry/user-notes-vote.php
@@ -0,0 +1,105 @@
+<?php
+/*
+ This script acts as the backend communication API for the user notes vote
feature.
+ Requests come in here from the mirrors to update the database with new votes.
+ master.php.net should respond with either a 400 HTTP status code and empty
body or 200 HTTP status with new updated
+ vote count in the body. This will let the mirror know whether the request
succeeded or failed.
+*/
+
+
+// Check database for IP limits
+function db_vote_note() {
+ $db = "phpmasterdb";
+ $username = "nobody";
+ $password = "";
+ $host = "localhost";
+
+ if (!($dbh = @mysql_connect($host, $username, $password))) {
+ return false;
+ }
+
+ if (!(@mysql_select_db($db, $dbh))) {
+ return false;
+ }
+
+ $ip = mysql_real_escape_string(sprintf("%u",ip2long($_POST['ip'])));
+ $hostip =
mysql_real_escape_string(sprintf("%u",ip2long($_SERVER['REMOTE_ADDR'])));
+ $id = mysql_real_escape_string($_POST['noteid']);
+ // Check that the note id exists
+ $note_query = "SELECT COUNT(*) AS num, sect FROM note WHERE id = '$id'";
+ // Limit 1 vote per note, per client ip, per day
+ $client_query = "SELECT COUNT(*) AS num FROM votes WHERE ip = '$ip' AND ts
>= (NOW() - INTERVAL 1 DAY) AND note_id = '$id'";
+ // Limit 5 votes per note, per host ip, per minute
+ $host_query = "SELECT COUNT(*) AS num FROM votes WHERE hostip = '$hostip'
AND ts >= (NOW() - INTERVAL 1 MINUTE) AND note_id = '$id'";
+
+ if (!($result_note = @mysql_query($note_query))) {
+ return false;
+ }
+ $rows_note = @mysql_fetch_assoc($result_note);
+ if (empty($rows_note) || $rows_note['num'] == 0) {
+ return false;
+ }
+ // Check that the note is in the right section
+ if ($rows_note['sect'] !== $_POST['sect']) {
+ return false;
+ }
+
+ if (!($result_client = @mysql_query($client_query))) {
+ return false;
+ }
+ $rows_client = @mysql_fetch_assoc($result_client);
+ if (empty($rows_client) || $rows_client['num'] >= 100) {
+ return false;
+ }
+
+ if (!($result_host = @mysql_query($host_query))) {
+ return false;
+ }
+ $rows_host = @mysql_fetch_assoc($result_host);
+ if (empty($rows_host) || $rows_host['num'] >= 5) {
+ return false;
+ }
+
+ if ($_POST['vote'] === 'up') {
+ $vote = 1;
+ }
+ else {
+ $vote = 0;
+ }
+ $ts = mysql_real_escape_string(date("Y-m-d H:i:s"));
+ $insert_query = "INSERT INTO votes(note_id,ip,hostip,ts,vote)
VALUES('$id','$ip','$hostip','$ts','$vote')";
+ if (!($result = @mysql_query($insert_query))) {
+ return false;
+ }
+
+ if (!($getvotes = @mysql_query("SELECT SUM(votes.vote) AS up,
(COUNT(votes.vote) - SUM(votes.vote)) AS down FROM votes WHERE votes.note_id =
{$id}"))) {
+ return false;
+ }
+ $votes = mysql_fetch_assoc($getvotes);
+
+ return $votes['up'] - $votes['down'];
+}
+
+// Validate the request
+if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
+ header("HTTP/1.0 400", true);
+ exit();
+}
+if (empty($_POST['noteid']) || empty($_POST['sect']) || empty($_POST['vote'])
|| !isset($_POST['ip'])) {
+ header("HTTP/1.0 400", true);
+ exit();
+}
+if ($_POST['vote'] !== 'up' && $_POST['vote'] !== 'down') {
+ header("HTTP/1.0 400", true);
+ exit();
+}
+
+// Check master DB for hostip and clientip limits and other validations
+if (($vote_update = db_vote_note()) === false) {
+ header("HTTP/1.0 400", true);
+ exit();
+}
+
+// If everything passes the response should be the new updated vote count with
HTTP 200 OK status code
+header("HTTP/1.0 200",true);
+echo $vote_update;
diff --git a/fetch/user-notes.php b/fetch/user-notes.php
index e941cfe..8a13ea4 100644
--- a/fetch/user-notes.php
+++ b/fetch/user-notes.php
@@ -31,6 +31,13 @@ while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {
} else {
$user = '';
}
- echo "$row[id]|$row[sect]|$row[rate]|$row[ts]|$user|",
- base64_encode(gzcompress($row['note'],3)),"\n";
+ // We'll need to gather an array for the votes separately here
+ // @TODO: Find a better way to do this later
+ $getvotes = mysql_query("SELECT SUM(votes.vote) AS up, (COUNT(votes.vote)
- SUM(votes.vote)) AS down FROM votes WHERE votes.note_id = {$row['id']}");
+ $votes = mysql_fetch_assoc($getvotes);
+ if (empty($votes['up'])) $votes['up'] = 0;
+ if (empty($votes['down'])) $votes['down'] = 0;
+ // Output here
+ echo
"{$row['id']}|{$row['sect']}|{$row['rate']}|{$row['ts']}|{$row['user']}|",
+
base64_encode(gzcompress($row['note'],3)),"|{$votes['up']}|{$votes['down']}\n";
}
--
1.7.5.4
