A couple of days ago I was adding entries to my ban related tables via phpbb's administration tools. After awhile, I quickly tired of serially adding an entry and then submitting it. I decided to dump the three tables in question and then modify them by hand. Plus that, I wanted to review the MySQL syntax.
Here are a series of create and insert statements for MySQL. I modified some of the entry values with "x", "!", "?" as to not offend some readers.
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Table structure for table `phpbb_banlist`
--
CREATE TABLE `phpbb_banlist` (
`ban_id` mediumint(8) unsigned NOT NULL auto_increment,
`ban_userid` mediumint(8) NOT NULL default '0',
`ban_ip` varchar(8) NOT NULL default '',
`ban_email` varchar(255) default NULL,
PRIMARY KEY (`ban_id`),
KEY `ban_ip_user_id` (`ban_ip`,`ban_userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=97 ;
--
-- Dumping data for table `phpbb_banlist`
--
INSERT INTO `phpbb_banlist` VALUES(84, 0, '', '*@fromru.com');
INSERT INTO `phpbb_banlist` VALUES(5, 0, '', '*@mail.ru');
INSERT INTO `phpbb_banlist` VALUES(6, 0, '', '*@*.ru');
INSERT INTO `phpbb_banlist` VALUES(7, 0, '', '*@go2.pl');
...
CREATE TABLE `phpbb_disallow` (
`disallow_id` mediumint(8) unsigned NOT NULL auto_increment,
`disallow_username` varchar(25) NOT NULL default '',
PRIMARY KEY (`disallow_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=30 ;
INSERT INTO `phpbb_disallow` VALUES(1, '*porn*');
INSERT INTO `phpbb_disallow` VALUES(2, '*sex*');
INSERT INTO `phpbb_disallow` VALUES(3, '*xxxxx*');
INSERT INTO `phpbb_disallow` VALUES(4, '*co!!*');
INSERT INTO `phpbb_disallow` VALUES(5, '*axx*');
INSERT INTO `phpbb_disallow` VALUES(6, '*adult*');
INSERT INTO `phpbb_disallow` VALUES(7, '*viagra*');
INSERT INTO `phpbb_disallow` VALUES(8, '*pill*');
INSERT INTO `phpbb_disallow` VALUES(9, '*drug*');
INSERT INTO `phpbb_disallow` VALUES(10, '*pharm*');
...
-- Table structure for table `phpbb_words`
--
CREATE TABLE `phpbb_words` (
`word_id` mediumint(8) unsigned NOT NULL auto_increment,
`word` char(100) NOT NULL default '',
`replacement` char(100) NOT NULL default '',
PRIMARY KEY (`word_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=27 ;
--
-- Dumping data for table `phpbb_words`
--
INSERT INTO `phpbb_words` VALUES(1, 'xxxx', '***');
INSERT INTO `phpbb_words` VALUES(2, 'sh??', '****');
INSERT INTO `phpbb_words` VALUES(3, 'xxxch', '*****');
INSERT INTO `phpbb_words` VALUES(4, 'fxxke?', '******');
INSERT INTO `phpbb_words` VALUES(5, 'axx', '***');
INSERT INTO `phpbb_words` VALUES(6, '*turd*', '****');
...
This blog covers Unix system administration HOWTO tips for using inline for loops, find command, Unix scripting, configuration, SQL, various Unix-based tools, and command line interface syntax. The Unix OS supports tasks such as running hardware, device drivers, peripherals and third party applications. Share tips/comments. Read the comments. But most importantly: Read Disclaimer - Read Disclaimer.
Subscribe to:
Post Comments (Atom)
2 comments:
I run a few forums (using vBulletin, not phpBB) with a pretty large userbase. I've also grown very weary of the recent influx of automated forum spam-bots.
.ru and .my are the bane of my existence right now. I actually went and black-holed a bunch of class A's with iptables at one point.
Thanks, as always, for the example!
thanks ax0n
Other than yourself, do you have moderators helping out on your forums? I have been going alone. Do you mind listing or emailing me a link to one of your forums.
Yes, those *.ru's were targeting my forum likewise. I've done a lot of blocking, too.
Post a Comment