MODULES:
DOCUMENTATION:
[] - optional
$m.sqlb() - wraps text into these
, which is useful in mysql queries
$m.sqle() - escapes string
$m.sqlq() - wraps text into 'quotes', which is useful in mysql queries
$m.dbres(field,database,where,value[,where2,value2]) - returns db result
$m.dbdel(database,where,value) - deletes entry from database
$m.user(field,id/username,value) - returns user information from users
database
$m.logged(nickname) - returns user id if specified nickname is logged in
general
databasegeneral
databaseCOMMANDS:
!help - no description needed here
!register username password - create an account
!login username password - log in to account
!logout - log out from an account
!profile /nickname/ - profile info about specified nickname or yourself if not specified
!settings password|signature - sets your profile settings
!admin debug|restart|sesdel - administration options (by default level 5+)
REQUIREMENTS:
CREATE TABLE `messages` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`sender` int(10) unsigned NOT NULL,
`receiver` int(10) unsigned NOT NULL,
`message` text COLLATE utf8_latvian_ci NOT NULL,
`time` datetime DEFAULT NULL,
`status` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_latvian_ci;
CREATE TABLE `sessions` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(10) unsigned NOT NULL,
`status` tinyint(3) unsigned NOT NULL,
`address` varchar(50) NOT NULL,
`nickname` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(45) NOT NULL,
`password` varchar(45) NOT NULL,
`registered` datetime NOT NULL,
`active` tinyint(3) unsigned NOT NULL DEFAULT '0',
`group` smallint(10) unsigned NOT NULL DEFAULT '0',
`level` int(10) unsigned NOT NULL DEFAULT '0',
`points` int(10) unsigned NOT NULL,
`signature` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `channels` (
`channel` varchar(50) NOT NULL,
`users` smallint(5) unsigned NOT NULL,
`regulars` smallint(5) unsigned NOT NULL,
`voices` smallint(5) unsigned NOT NULL,
`halfops` smallint(5) unsigned NOT NULL,
`operators` smallint(5) unsigned NOT NULL,
`topic` varchar(255) CHARACTER SET utf8 COLLATE utf8_latvian_ci NOT NULL,
`mode` varchar(20) CHARACTER SET latin1 NOT NULL,
PRIMARY KEY (`channel`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `general` (
`setting` varchar(255) NOT NULL,
`value` varchar(255) NOT NULL,
PRIMARY KEY (`setting`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CHANGELOG:
0.13 -> 0.14 (307 lines)
general
and channels
.SHOUTOUTS:
; - tiny aliases
alias command return $+($chr(33),$1-)
alias bold return $+($chr(2),$1-,$chr(2))
alias uline return $+($chr(31),$1-,$chr(31))
alias quotes return $+($chr(34),$1-,$chr(34))
alias brackets return $+($chr(40),$1-,$chr(41))
alias brackets2 return $+($chr(91),$1-,$chr(93))
alias zero return $iif($1,$1-,0)
alias toggle return $iif($1,0,1)
alias mcomma {
var %a, %b = $regsub($ticks,$1,/\G([+-]?\d+?)(?=(?:\d{3})++(?=\.\d++$|$))/g,\1 $+ $chr(44),%a)
return %a
}
; - mysql related aliases
alias m.sqlb return $+($chr(96),$1-,$chr(96))
alias m.sqle return $mysql_escape_string($1-)
alias m.sqlq return $+($chr(39),$1-,$chr(39))
; - general aliases
alias -l config {
if ($1 = server) return irc.somewhat.org
else if ($1 = nick) return MySQL
else if ($1 = chan) return #mysqlchan
else if ($1 = fullname) return MySQL Bot Core (ver1.52 STABLE)
else if ($1 = network) return Networkname
else if ($1 = identd) return mysql
else if ($1 = email) return pats@neisen.lv
else if ($1 = mysql) {
if ($2 = hostname) return 127.0.0.1
else if ($2 = username) return root
else if ($2 = password) return
else if ($2 = database) return bot
}
}
alias -l m.encrypt return $md5($md5($encode($1-,m)))
alias -l m.amsg { $iif(!$2,return) | msg $1- | notice $1- }
alias -l m.notify {
if ($1 = error) return $bold(Error:)
else if ($1 = syntax) return $bold(Syntax:)
else if ($1 = example) return $bold(Example:)
else if ($1 = success) return $bold(Success:)
else if ($1 = info) return $bold(Information:)
else if ($1 = note) return $bold(Note:)
else if ($1 = login) return Please log in to your account or $command(register) a new one before you can use this command.
else return $bold($1-)
}
alias -l m.group {
if (!$1) || ($1 !isnum) || ($1 < 1) return User
else if ($1 = 1) return Premium
else if ($1 = 2) return VIP
else if ($1 < 5) return Moderator
else if ($1 < 8) return Administrator
else return Main Administrator
}
; - system based aliases
alias -l m.system {
if ($me !ison $config(chan)) join $v2
if ($me != $config(nick)) nick $v2
if (!$mysql_is_valid_connection(%m.sql)) {
mysql_close %m.sql | unset %m.sql
;msg $config(chan) $m.notify(info) Currently system is down... Running in offline mode.
}
$iif(%m.clearall,clearall) | $iif(%m.closemsg,closemsg)
}
alias -l m.sync {
; pinging mysql connection
mysql_ping %m.sql
; general statistic and information updating
m.setting uptime $uptime
m.setting nickname $me
m.setting network $network
;m.setting channels $chan(0)
m.setting lastupdate $ctime
; channel statistic and information updating
var %channels = $chan(0)
while %channels {
var %chan = $chan(%channels)
var %sql = REPLACE INTO `channels` (`channel`,`users`,`regulars`,`voices`,`halfops`,`operators`,`topic`,`mode`) VALUES (?,?,?,?,?,?,?,?)
noop $mysql_query(%m.sql, %sql, %chan, $nick(%chan,0), $nick(%chan,0,r), $nick(%chan,0,v), $nick(%chan,0,h), $nick(%chan,0,o), $m.sqle($chan(%channels).topic), $chan(%channels).mode)
dec %channels
}
; module system syncing
unset %m.commands
unset %m.admin
.signal m.modules
}
alias m.restart { mysql_close %m.sql | exit -rn }
alias m.dbres {
var %sql = SELECT $m.sqlb($1) FROM $m.sqlb($2) WHERE $m.sqlb($3) = ? $iif($6 != $null,AND $m.sqlb($5) = ?)
return $mysql_fetch_single($mysql_query(%m.sql, %sql, $m.sqle($4), $iif($6 != $null,$v1)))
}
alias m.dbdel {
var %sql = DELETE FROM $m.sqlb($1) WHERE $m.sqlb($2) = ?
if ($3) noop $mysql_query(%m.sql, %sql, $m.sqle($3))
}
alias m.user {
var %sql = SELECT $m.sqlb($1) FROM `users` WHERE $iif($2 isnum,id,username) = ?
return $mysql_fetch_single($mysql_query(%m.sql, %sql, $m.sqle($2)))
}
alias m.setting {
if ($2 != $null) {
var %sql = REPLACE INTO `general` (`setting`,`value`) VALUES (?,?)
noop $mysql_query(%m.sql, %sql, $1, $m.sqle($2-))
}
else return $m.dbres(value, general, setting, $1)
}
alias m.logged {
var %sql = SELECT * FROM `sessions` WHERE `nickname` = ?
var %res = $mysql_query(%m.sql, %sql, $m.sqle($1))
noop $mysql_fetch_row(%res, logged) | mysql_free %res
if ($hget(logged, status)) {
var %address = $hget(logged, address), %uid = $hget(logged, uid) | hfree logged
if (%address != $address($1,2)) {
msg $1 $m.notify(info) You've been logged out of the account due address mismatch.
noop $m.dbdel(sessions, nickname, $1) | return $false
}
else return %uid
}
else return $false
}
alias m.modules {
tokenize 124 $1-
if ($3) set %m.admin $addtok(%m.admin,$2,32)
else $iif($2,set %m.commands $addtok(%m.commands,$v1,32))
}
; - raws
raw *:*:{
; successful connection
if ($numeric = 001) {
if ($network == $config(network)) {
if ($me !ison $config(chan)) join $config(chan)
}
}
}
; - events
on *:START:{
; setting defaults
.titlebar $config(fullname) | .fullname $config(fullname)
.identd on $config(identd)
.nick $config(nick) | .anick $+($config(nick),_)
; creating mysql connection
set %m.sql $mysql_connect($config(mysql,hostname), $config(mysql,username), $config(mysql,password))
if (!%m.sql) {
echo -st $m.notify(error) MySQL connection has failed.
echo -st - %mysql_errstr
halt
}
else if ($mysql_select_db(%m.sql, $config(mysql,database))) {
; removing previously active sessions (security reasons)
var %sql = DELETE FROM `sessions` | noop $mysql_query(%m.sql, %sql)
; setting "correct" (better) charset
mysql_set_charset %m.sql utf8
}
; starting connection
server $config(server) -j $config(chan)
; creating timers
.timer 0 120 m.system
.timer 0 $iif(%m.syncdelay isnum,$v1,30) m.sync
}
on *:JOIN:#:{
if ($chan = $config(chan)) {
if ($nick = $me) who $chan
}
}
on *:NICK:{
var %sql = UPDATE `sessions` SET `nickname` = $m.sqlq($m.sqle($newnick)) WHERE `nickname` = $m.sqlq($m.sqle($nick))
noop $mysql_query(%m.sql, %sql)
}
on *:TEXT:*:*:{
if (!%m.sql) halt
var %c = $chan, %n = $nick, %a = $iif(%c,notice,msg) %n
if ($1 == $command(help)) || ($1 == $command(commands)) {
var %uid = $m.logged(%n)
if !%uid {
%a $bold(Commands:) Use $command(register) to create a new account or $command(login) $brackets($uline(in my query)) to log in to your account.
%a $m.notify(note) If you've any problems with your account - you can mail us at $uline($config(email))
}
else {
%a $bold(Commands:) $command(logout) $command(profile) $command(settings) $command(messages) %m.commands $iif($zero($m.user(group,$m.logged(%n))) >= 5,$command(admin) %m.admin)
}
}
else if ($1 == $command(register)) {
if (%c) msg %n $m.notify(error) Sorry, but this command can be used only in query. $iif($3,Please use $uline(different) password now.)
else if ($m.logged(%n)) msg %n $m.notify(info) You're already logged in as $bold($m.user(username, $v1)) $+ .
else {
if (!$3) { msg %n $m.notify(syntax) $command(register) [username] [password] $m.notify(example) $command(register) %n thismahcoolpass }
else if ($2 isnum) { msg %n $m.notify(error) Username cannot be numeric. }
else if ($len($3) < 5) || ($3 isnum) || ($3 == thismahcoolpass) { msg %n $m.notify(error) Sorry, password must be at least 5 symbols long, it cannot be only numeric and it can't be the one in preview. }
else if ($m.dbres(password, users, username, $2) != $null) { msg %n $m.notify(error) Sorry, this username is already registered. }
else {
var %sql = INSERT INTO `users` (`username`,`password`,`registered`,`active`) VALUES (?,?,NOW(),1)
noop $mysql_query(%m.sql, %sql, $m.sqle($strip($2)), $m.sqle($strip($m.encrypt($3))))
msg %n $m.notify(success) You're now registered with username $bold($2) $+ $chr(44) please remember your password and keep it safe.
msg %n $m.notify(info) You can now login using $bold($command(login) $2 $3)
}
}
}
else if ($1 == $command(login)) {
if (%c) msg %n $m.notify(error) Sorry, but this command can be used only in query. $iif($3,Please login ASAP and $uline(change your password) $+ .)
else if ($m.logged(%n)) msg %n $m.notify(info) You're already logged in as $bold($m.user(username, $v1)) $+ .
else {
var %sql = SELECT `password` FROM `users` WHERE `username` = ?
if (!$3) { msg %n $m.notify(syntax) $command(login) [username] [password] $m.notify(example) $command(login) %n thismahcoolpass }
else {
var %password = $m.dbres(password, users, username, $2)
if (%password = $null) msg %n $m.notify(error) Username $quotes($2) is not registered.
else if (%password != $m.encrypt($3)) msg %n $m.notify(error) Incorrect password.
else {
var %uid = $m.user(id,$2)
noop $m.dbdel(sessions, uid, %uid)
var %sql = INSERT INTO `sessions` (`uid`,`status`,`address`,`nickname`) VALUES (?,?,?,?)
noop $mysql_query(%m.sql, %sql, %uid, 1, $address(%n,2), %n)
msg %n $m.notify(info) You're now logged in as $bold($2) $+ .
msg %n $m.notify(note) Your session will expire $uline(only) if your address $brackets($address(%n,2)) changes.
; message module - checking for unread messages
var %sql = SELECT `time` FROM `messages` WHERE `receiver` = ? AND `status` = '0' LIMIT 1
var %res = $mysql_query(%m.sql, %sql, $m.logged(%n))
if ($mysql_fetch_single(%res)) msg %n *** You have new messages, please review them using $bold($command(message) review)
}
}
}
}
else if ($1 == $command(settings)) {
var %uid = $m.logged(%n)
if (!%uid) %a $m.notify(login)
else {
if (!$2) %a $bold(Settings:) password signature
else if ($2 == password) {
if (%c) msg %n $m.notify(error) This command cannot be used in channel.
else if (!$4) msg %n $m.notify(syntax) $command(settings) password $brackets2(current password) $brackets2(new password)
else if ($4 isnum) || ($len($4) < 5) || ($4 == thismahcoolpass) { msg %n $m.notify(error) Sorry, password must be at least 5 symbols long, it cannot be only numeric and it can't be the one in preview. }
else if ($m.dbres(password, users, id, %uid) != $3) { msg %n $m.notify(error) Incorrect current password. }
else {
var %sql = UPDATE users SET `password` = ? WHERE `id` = %uid
noop $mysql_query(%m.sql, %sql, $4)
msg %n $m.notify(info) Your password has been changed successfully.
}
}
else if ($2 == signature) {
if (!$3) || ($len($3) > 100) %a $bold(Signature:) Your personal signature in length of 100 characters.
else {
var %sql = UPDATE `users` SET `signature` = $m.sqlq($m.sqle($strip($3-))) WHERE `id`= $m.sqlq(%uid)
noop $mysql_query(%m.sql, %sql)
%a $m.notify(info) Your signature has been updated.
}
}
}
}
else if ($1 == $command(logout)) {
if (!$m.logged(%n)) %a $m.notify(info) You're not logged in yet.
else {
var %uid = $m.logged(%n)
noop $m.dbdel(sessions, uid, %uid)
%a $m.notify(info) You're no longer logged in as $bold($m.user(username,%uid)) $+ .
}
}
else if ($1 == $command(profile)) {
var %user = $iif($2,$v1,%n), %uid = $m.logged(%user)
%a $brackets2(%user) $bold(Status:) $iif(%uid,Logged in as $m.user(username,$v1) $brackets(ID: %uid) $bold(Registered:) $m.user(registered,%uid) $bold(Group:) $m.group($m.user(group,%uid)),Not logged in yet.)
if %uid {
;%a - $brackets2(Game Profile) $bold(Level:) $zero($m.user(level,%uid)) $bold(Points:) $m.user(points,%uid)
if ($m.user(signature,%uid)) { %a - $brackets2(Signature) $v1 }
if ($m.user(group,$m.logged(%n)) >= 4) {
%a - $brackets2(Admin Info) $bold(Session ID:) $m.dbres(id, sessions, uid, %uid) $bold(Logged Address:) $m.dbres(address, sessions, uid, %uid)
}
}
}
else if ($1 == $command(admin)) {
if (!$m.logged(%n)) %a $m.notify(login)
else if ($zero($m.user(group,$m.logged(%n))) < 5) %a $m.notify(error) Permission denied. Lack of privileges.
else if (!$2) %a $bold(Admin:) debug restart sesdel
else if ($2 == debug) {
var %msql.con = $mysql_is_valid_connection(%m.sql), %msql.ver = $mysql_get_server_info(%m.sql)
%a $bold(MySQL:) $config(mysql,hostname) u: $config(mysql,username) d: $config(mysql,database) $iif(%msql.ver,v: $v1) / status: $iif(%msql.con != 1,not) connected
}
else if ($2 == restart) { %a $m.notify(info) Restart in progress... | .timer 1 1 m.restart }
else if ($2 == sesdel) && ($3 isnum) {
noop $m.dbdel(sessions, id, $3)
%a $m.notify(info) Session with ID $3 has been cleared out.
}
}
else if ($1 == $command(message)) || ($1 == $command(messages)) {
if (!$m.logged(%n)) %a $m.notify(login)
else if ($2 == send) {
if (!$4) %a $m.notify(syntax) $command(message) send [username] [text]
;else if ($3 == %n) %a $m.notify(error) Why?
else if (!$m.user(id,$3)) %a $m.notify(error) Such user as $quotes($3) doesn't exist.
else if ($len($4-) > 120) %a $m.notify(error) Message cannot be longer than 120 symbols.
else {
var %sql = INSERT INTO `messages` (`sender`,`receiver`,`message`,`time`) VALUES (?,?,?,NOW())
noop $mysql_query(%m.sql, %sql, $m.logged(%n), $m.user(id,$3), $m.sqlq($m.sqle($strip($4-))))
%a $m.notify(info) Your message has been successfully sent to $+($3,.)
}
}
else if ($2 == read) || ($2 == view) {
if ($3 !isnum && $3 != unread) %a $m.notify(syntax) $command(message) read [identificator]
else {
var %sql = SELECT * FROM `messages` WHERE `receiver` = ? $iif($3 == unread,AND `status` = '0') ORDER BY `id` ASC $iif($3 != unread,LIMIT $+($calc($3 - 1),$chr(44),1))
var %res = $mysql_query(%m.sql, %sql, $m.logged(%n))
noop $mysql_fetch_row(%res, msg) | mysql_free %res
if ($hget(msg, time)) {
%a $+($bold($m.user(username, $hget(msg, sender))),$chr(58)) $right($left($hget(msg, message),-1),-1)
%a - sent: $hget(msg, time) status: $iif(!$hget(msg, status),un) $+ read -- $command(message) delete $hget(msg, id)
if (!$hget(msg, status)) noop $mysql_query(%m.sql, UPDATE `messages` SET `status` = 1 WHERE `id` = $hget(msg, id))
hfree msg
}
else %a $m.notify(error) $iif($3 == unread,Sorry $+ $chr(44) you have no unread messages.,Inexistant message.)
}
}
else if ($2 == delete) {
if ($3 !isnum) %a $m.notify(syntax) $command(message) delete [identificator]
else {
var %sql = DELETE FROM `messages` WHERE `receiver` = ? AND `id` = ?
var %res = $mysql_query(%m.sql, %sql, $m.logged(%n), $3)
%a $m.notify(info) If existing and yours, message $+($chr(35),$3) removed.
}
}
else if ($2 == review) {
var %sql = SELECT COUNT(*) FROM `messages` WHERE `receiver` = ? AND `status` = '0'
var %count = $zero($mysql_fetch_single($mysql_query(%m.sql, %sql, $m.logged(%n))))
%a You have $iif(%count,$v1,no) unread message $+ $iif(%count > 1,s) $+ $iif(!%count,s) $+ . $iif(%count,View $iif(%count > 1,them,it) using $bold($command(message) view unread))
}
else %a $m.notify(syntax) $command(message) read/send/delete/review - to see your mailbox, use $bold($command(message) review)
}
}
; menu
menu * {
MySQL Bot &Core
.&Perfomance
..$iif(%m.clearall,$style(1)) Automatic Clear&ALL:set %m.clearall $toggle(%m.clearall)
..$iif(%m.closemsg,$style(1)) Automatic Close&MSG:set %m.closemsg $toggle(%m.closemsg)
..&Sync Delay $chr(9) $iif(%m.syncdelay,$v1,30) sec.:set %m.syncdelay $$?="Enter the preferred delay of syncronising between the bot and database in seconds:"
.&Actions
..&Synchronise:m.sync
..Re&start:m.restart
.-
.&Connection Status:echo -at Currently the connection is $bold($iif($mysql_is_valid_connection(%m.sql),valid,invalid))
.&Support:url -na http://www.hawkee.com/profile/6484/
}
@spartan23 If you would properly read the description as it was meant to instead of making trivial, pointless comments, you'd have observed that this requires MySQL attached to your local host. Of which would usually require some sort of locally based server software like ApacheTriad with MySQL enabled, and some configuring of your Windows IIS settings. Noutrious has made this, somewhat, clear in the descriptions through the linked modules.
Great work! Only criticism is the passwords are stored in plain text, you should at least MD5 them and maybe even add salt.
I had to laugh when I saw your post TheImrac. ;) You'd think with all the programmers and engineers at SCE that they would have thought to encrypt all users account information, and personal data before getting hacked for all of what, 80 million users personal data? However, lesson learned by SCE that month was never store anything in plain text.