Just an update I've pushed the new commits to github and fixed a
couple of issues in the backend scripts (patch attached here). The
javascript for the user notes stuff was moved to a separate file,
it'll be better for caching in the UA this way, and removes a lot of
the inline code from the PHP. I also fixed some of the CSS issues with
the ?beta=1 version of the site. Although the JS is bit quirky there
atm. Working on that, but that's really due to the way the CSS is done
there. Still needs tweaking (but it's not like we advertise that part
as working now anyway).
https://github.com/php/web-php/pull/7
If anyone finds any other issues I missed please let me know.
Thanks :)
From ec9e2b8ba7ab5ceacdc8bc482a5c998baaa3a7da Mon Sep 17 00:00:00 2001
From: Sherif Ramadan <google...@php.net>
Date: Tue, 11 Sep 2012 21:36:04 -0400
Subject: [PATCH] Updated backend scripts and SQL schema for new user notes
features.
---
entry/user-notes-flag.php | 145 +++++++++++++++++++++++++++++++++++++++++++++
entry/user-notes-vote.php | 105 ++++++++++++++++++++++++++++++++
fetch/user-notes.php | 11 +++-
note.sql | 30 +++++++++
4 files changed, 289 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..28ba00d
--- /dev/null
+++ b/entry/user-notes-flag.php
@@ -0,0 +1,145 @@
+<?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);
+ if (!strlen($user)) $user = "Anonymous";
+ }
+ $msg = <<<EMAIL_MSG
+A user has flagged a note! Please review it and take any necessary action.
+
+http://www.php.net/{$sect}#{$note_id}
+
+--------------------------------
+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";
}
diff --git a/note.sql b/note.sql
index bf2fc7d..c7b12d8 100644
--- a/note.sql
+++ b/note.sql
@@ -10,6 +10,8 @@
master.php.net/entry/user-note.php
master.php.net/fetch/user-notes.php
master.php.net/manage/user-notes.php
+ master.php.net/entry/user-notes-vote.php
+ master.php.net/entry/user-notes-flag.php
*/
CREATE TABLE IF NOT EXISTS note (
@@ -32,3 +34,31 @@ CREATE TABLE IF NOT EXISTS alerts (
sect VARCHAR(80) not NULL default '',
updated TIMESTAMP(14) NOT NULL
) TYPE=MyISAM;
+
+-- New votes table added by Sherif
+CREATE TABLE IF NOT EXISTS `votes` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `note_id` mediumint(9) NOT NULL,
+ `ip` bigint(20) unsigned NOT NULL DEFAULT '0',
+ `hostip` bigint(20) unsigned NOT NULL DEFAULT '0',
+ `ts` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+ `vote` tinyint(1) unsigned NOT NULL DEFAULT '0',
+ PRIMARY KEY (`id`),
+ KEY `note_id` (`note_id`,`ip`,`vote`),
+ KEY `hostip` (`hostip`)
+) TYPE=MyISAM AUTO_INCREMENT=1;
+
+-- New flags table added by Sherif
+CREATE TABLE IF NOT EXISTS `flags` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `note_id` mediumint(9) NOT NULL,
+ `ip` bigint(20) unsigned NOT NULL DEFAULT '0',
+ `hostip` bigint(20) unsigned NOT NULL DEFAULT '0',
+ `ts` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+ `flag` varchar(80) NOT NULL DEFAULT '',
+ `email` varchar(120) NOT NULL DEFAULT '',
+ `info` text,
+ PRIMARY KEY (`id`),
+ KEY `note_id` (`note_id`,`ip`,`flag`),
+ KEY `hostip` (`hostip`)
+) TYPE=MyISAM AUTO_INCREMENT=1;
--
1.7.5.4
