Botnow /

SqliteViews

by default, Botnow's sqlite database is scattered across different tables, making it difficult to search for similarities in different columns.

this can be resolved by creating "views" which are basically virtual tables that are the output of some other statement

for example, with these views

CREATE VIEW bncirc AS SELECT * FROM irc INNER JOIN bnc ON irc.id = bnc.ircid;
CREATE VIEW shellirc AS SELECT * FROM irc INNER JOIN shell ON irc.id = shell.ircid;

if you wanted to find all shell accounts registered via gmail who are in the 182.1.0.0/16 subnet, instead of having to use messy nested statements, it would be super simple

SELECT username,email,ctcpversion FROM shellirc WHERE email LIKE "%@gmail.com" AND ip LIKE "182.1.%";

username  email                                 ctcpversion                                                                  
--------  ------------------------------------  -----------------------------------------------------------------------------
jrmu      ihaslotsofshellaccounts923@gmail.com  AndroIRC - Android IRC Client (5.2 - Build 6830152) - http://www.androirc.com                                                            
jrsusmu   redacted123@gmail.com                 AndroIRC - Android IRC Client (5.2 - Build 6830152) - http://www.androirc.com

as we can see, jrmu is suspicious and might be a clone!