[aur-dev] [PATCH 1/2] Add "Bans" table to database schema
The "Bans" table creates a DB structure for the ability to ban IP addresses. It takes an IP address converted by ip2long(). It can easily be extended for other features such as time limits (for temporary bans). The table will eventually be able to be populated directly through the web interface by Trusted Users and Developers. Signed-off-by: canyonknight <canyonknight@gmail.com> --- UPGRADING | 11 +++++++++++ support/schema/aur-schema.sql | 9 +++++++++ 2 files changed, 20 insertions(+) diff --git a/UPGRADING b/UPGRADING index 9c05467..7d13c43 100644 --- a/UPGRADING +++ b/UPGRADING @@ -11,6 +11,17 @@ ALTER TABLE Users ADD COLUMN LastLoginIPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0; ---- +2. Add a new "Bans" table: + +---- +CREATE TABLE Bans ( + ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + IPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0, + PRIMARY KEY (ID), + UNIQUE (IPAddress) +) ENGINE = InnoDB; +---- + From 2.0.0 to 2.1.0 ------------------- diff --git a/support/schema/aur-schema.sql b/support/schema/aur-schema.sql index fab40d6..2d268fa 100644 --- a/support/schema/aur-schema.sql +++ b/support/schema/aur-schema.sql @@ -213,3 +213,12 @@ CREATE TABLE IF NOT EXISTS TU_Votes ( FOREIGN KEY (VoteID) REFERENCES TU_VoteInfo(ID) ON DELETE CASCADE, FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE ) ENGINE = InnoDB; + +-- Malicious user banning +-- +CREATE TABLE Bans ( + ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + IPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0, + PRIMARY KEY (ID), + UNIQUE (IPAddress) +) ENGINE = InnoDB; -- 1.8.2
Adds a new is_ipbanned() function to determine whether the user attempting to login or register for an account has their IP address listed in the "Bans" table. Signed-off-by: canyonknight <canyonknight@gmail.com> --- web/lib/acctfuncs.inc.php | 35 ++++++++++++++++++++++++++++++++++- 1 file changed, 34 insertions(+), 1 deletion(-) diff --git a/web/lib/acctfuncs.inc.php b/web/lib/acctfuncs.inc.php index 21cc6c2..aa4c70b 100644 --- a/web/lib/acctfuncs.inc.php +++ b/web/lib/acctfuncs.inc.php @@ -93,6 +93,15 @@ function process_account_form($UTYPE,$TYPE,$A,$U="",$T="",$S="",$E="", # error check and process request for a new/modified account global $SUPPORTED_LANGS, $AUR_LOCATION; + $error = ''; + + if (is_ipbanned()) { + $error = __('Account registration has been disabled ' . + 'for your IP address, probably due ' . + 'to sustained spam attacks. Sorry for the ' . + 'inconvenience.'); + } + $dbh = DB::connect(); if(isset($_COOKIE['AURSID'])) { @@ -102,7 +111,6 @@ function process_account_form($UTYPE,$TYPE,$A,$U="",$T="",$S="",$E="", $editor_user = null; } - $error = ""; if (empty($E) || empty($U)) { $error = __("Missing a required field."); } @@ -400,6 +408,13 @@ function try_login() { $userID = null; if ( isset($_REQUEST['user']) || isset($_REQUEST['passwd']) ) { + if (is_ipbanned()) { + $login_error = __('The login form is currently disabled ' . + 'for your IP address, probably due ' . + 'to sustained spam attacks. Sorry for the ' . + 'inconvenience.'); + return array('SID' => '', 'error' => $login_error); + } $dbh = DB::connect(); $userID = valid_user($_REQUEST['user']); @@ -480,6 +495,24 @@ function try_login() { } /** + * Determine if the user is using a banned IP address + * + * @return bool True if IP address is banned, otherwise false + */ +function is_ipbanned() { + $dbh = DB::connect(); + + $q = "SELECT * FROM Bans WHERE IPAddress = " . $dbh->quote(ip2long($_SERVER['REMOTE_ADDR'])); + $result = $dbh->query($q); + + if ($result->fetchColumn()) { + return true; + } else { + return false; + } +} + +/** * Validate a username against a collection of rules * * The username must be longer or equal to USERNAME_MIN_LEN. It must be shorter -- 1.8.2
On Tue, Mar 19, 2013 at 7:17 PM, canyonknight <canyonknight@gmail.com> wrote:
The "Bans" table creates a DB structure for the ability to ban IP addresses. It takes an IP address converted by ip2long(). It can easily be extended for other features such as time limits (for temporary bans).
The table will eventually be able to be populated directly through the web interface by Trusted Users and Developers.
Signed-off-by: canyonknight <canyonknight@gmail.com> --- UPGRADING | 11 +++++++++++ support/schema/aur-schema.sql | 9 +++++++++ 2 files changed, 20 insertions(+)
diff --git a/UPGRADING b/UPGRADING index 9c05467..7d13c43 100644 --- a/UPGRADING +++ b/UPGRADING @@ -11,6 +11,17 @@ ALTER TABLE Users ADD COLUMN LastLoginIPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0; ----
+2. Add a new "Bans" table: + +---- +CREATE TABLE Bans ( + ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + IPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0, + PRIMARY KEY (ID), + UNIQUE (IPAddress) +) ENGINE = InnoDB; +---- + From 2.0.0 to 2.1.0 -------------------
diff --git a/support/schema/aur-schema.sql b/support/schema/aur-schema.sql index fab40d6..2d268fa 100644 --- a/support/schema/aur-schema.sql +++ b/support/schema/aur-schema.sql @@ -213,3 +213,12 @@ CREATE TABLE IF NOT EXISTS TU_Votes ( FOREIGN KEY (VoteID) REFERENCES TU_VoteInfo(ID) ON DELETE CASCADE, FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE ) ENGINE = InnoDB; + +-- Malicious user banning +-- +CREATE TABLE Bans ( + ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + IPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0, + PRIMARY KEY (ID), + UNIQUE (IPAddress) +) ENGINE = InnoDB;
Why bother with an ID column at all here, since your IPAddress column is unique, an integer, and could be it's own primary key? Finally, I would highly recommend adding a created timestamp in lieu of an ID; it still gives insertion order but also relative time spacing of said insertions, and would allow gradual aging out of old entries if that was ever desired in the future. -Dan
On Tue, Mar 19, 2013 at 8:21 PM, Dan McGee <dpmcgee@gmail.com> wrote:
On Tue, Mar 19, 2013 at 7:17 PM, canyonknight <canyonknight@gmail.com> wrote:
The "Bans" table creates a DB structure for the ability to ban IP addresses. It takes an IP address converted by ip2long(). It can easily be extended for other features such as time limits (for temporary bans).
The table will eventually be able to be populated directly through the web interface by Trusted Users and Developers.
Signed-off-by: canyonknight <canyonknight@gmail.com> --- UPGRADING | 11 +++++++++++ support/schema/aur-schema.sql | 9 +++++++++ 2 files changed, 20 insertions(+)
diff --git a/UPGRADING b/UPGRADING index 9c05467..7d13c43 100644 --- a/UPGRADING +++ b/UPGRADING @@ -11,6 +11,17 @@ ALTER TABLE Users ADD COLUMN LastLoginIPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0; ----
+2. Add a new "Bans" table: + +---- +CREATE TABLE Bans ( + ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + IPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0, + PRIMARY KEY (ID), + UNIQUE (IPAddress) +) ENGINE = InnoDB; +---- + From 2.0.0 to 2.1.0 -------------------
diff --git a/support/schema/aur-schema.sql b/support/schema/aur-schema.sql index fab40d6..2d268fa 100644 --- a/support/schema/aur-schema.sql +++ b/support/schema/aur-schema.sql @@ -213,3 +213,12 @@ CREATE TABLE IF NOT EXISTS TU_Votes ( FOREIGN KEY (VoteID) REFERENCES TU_VoteInfo(ID) ON DELETE CASCADE, FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE ) ENGINE = InnoDB; + +-- Malicious user banning +-- +CREATE TABLE Bans ( + ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + IPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0, + PRIMARY KEY (ID), + UNIQUE (IPAddress) +) ENGINE = InnoDB;
Why bother with an ID column at all here, since your IPAddress column is unique, an integer, and could be it's own primary key?
I had done it for insertion order purposes, but your timestamp idea is better.
Finally, I would highly recommend adding a created timestamp in lieu of an ID; it still gives insertion order but also relative time spacing of said insertions, and would allow gradual aging out of old entries if that was ever desired in the future.
-Dan
From: canyonknight <canyonknight@gmail.com> The "Bans" table creates a DB structure for the ability to ban IP addresses. It takes an IP address converted by ip2long(). The table will eventually be able to be populated directly through the web interface by Trusted Users and Developers. Signed-off-by: canyonknight <canyonknight@gmail.com> Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de> --- UPGRADING | 10 ++++++++++ support/schema/aur-schema.sql | 8 ++++++++ 2 files changed, 18 insertions(+) diff --git a/UPGRADING b/UPGRADING index 9c05467..a04471f 100644 --- a/UPGRADING +++ b/UPGRADING @@ -11,6 +11,16 @@ ALTER TABLE Users ADD COLUMN LastLoginIPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0; ---- +2. Add a new "Bans" table: + +---- +CREATE TABLE Bans ( + IPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0, + BanTS TIMESTAMP NOT NULL, + PRIMARY KEY (IPAddress) +) ENGINE = InnoDB; +---- + From 2.0.0 to 2.1.0 ------------------- diff --git a/support/schema/aur-schema.sql b/support/schema/aur-schema.sql index fab40d6..0d04f12 100644 --- a/support/schema/aur-schema.sql +++ b/support/schema/aur-schema.sql @@ -213,3 +213,11 @@ CREATE TABLE IF NOT EXISTS TU_Votes ( FOREIGN KEY (VoteID) REFERENCES TU_VoteInfo(ID) ON DELETE CASCADE, FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE ) ENGINE = InnoDB; + +-- Malicious user banning +-- +CREATE TABLE Bans ( + IPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0, + BanTS TIMESTAMP NOT NULL, + PRIMARY KEY (IPAddress) +) ENGINE = InnoDB; -- 1.8.2.411.g65a544e
participants (3)
-
canyonknight
-
Dan McGee
-
Lukas Fleischer