"Fossies" - the Fresh Open Source Software Archive

Member "hermes-1.9/src/Database.cpp" (29 Jun 2014, 11015 Bytes) of package /linux/privat/hermes-1.9.tar.gz:


As a special service "Fossies" has tried to format the requested source page into HTML format using (guessed) C and C++ source code syntax highlighting (style: standard) with prefixed line numbers and code folding option. Alternatively you can here view or download the uninterpreted source code file. For more information about "Database.cpp" see the Fossies "Dox" file reference documentation.

    1 /**
    2  * hermes antispam proxy
    3  * Copyright (C) 2006, 2007 Juan José Gutiérrez de Quevedo <juanjo@gutierrezdequevedo.com>
    4  *
    5  * This program is free software; you can redistribute it and/or modify
    6  * it under the terms of the GNU General Public License as published by
    7  * the Free Software Foundation; version 2 of the License
    8  *
    9  * This program is distributed in the hope that it will be useful,
   10  * but WITHOUT ANY WARRANTY; without even the implied warranty of
   11  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   12  * GNU General Public License for more details.
   13  *
   14  * You should have received a copy of the GNU General Public License along
   15  * with this program; if not, write to the Free Software Foundation, Inc.,
   16  * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
   17  *
   18  * @author Juan José Gutiérrez de Quevedo <juanjo@gutierrezdequevedo.com>
   19  */
   20 #include "Database.h"
   21 
   22 #include <unistd.h>
   23 extern LOGGER_CLASS hermes_log;
   24 
   25 void Database::setDatabaseFile(string p_dbfile)
   26 {
   27   dbfile=p_dbfile;
   28 }
   29 
   30 /**
   31  *
   32  * this function executes a query and checks for error
   33  * it doesn't return any value, as it is not needed(the queries don't return data)
   34  *
   35  */
   36 void Database::doQuery(string p_sql)
   37 {
   38   int retval;
   39   bool was_busy=false;
   40 
   41   do
   42   {
   43     retval=sqlite3_exec(dbh,p_sql.c_str(),NULL,NULL,NULL);
   44     if(SQLITE_OK!=retval&&SQLITE_BUSY!=retval)
   45       throw SQLException("SQL: "+p_sql+" sqlite3_errmsg: "+sqlite3_errmsg(dbh),__FILE__,__LINE__);
   46     if(SQLITE_BUSY==retval)
   47     {
   48       sleep(1+rand()%2);
   49       LERR("doquery() sql failed with busy state, retrying");
   50       was_busy=true;
   51     }
   52   }
   53   while(SQLITE_BUSY==retval);
   54   if(was_busy)
   55     LERR("doquery() executed correctly after failing initially");
   56 }
   57 
   58 string Database::cleanString(string s)
   59 {
   60   string result="";
   61 
   62   for(unsigned int i=0;i<s.length();i++)
   63     if(s[i]>31&&s[i]<127)
   64       switch(s[i])
   65       {
   66         case ' ':
   67         case '<':
   68         case '>':
   69         case '(':
   70         case ')':
   71         case '[':
   72         case ']':
   73         case '\\':
   74         case ',':
   75         case ';':
   76         case ':':
   77         case '"':
   78         case '%':
   79         case '\'':
   80           break;
   81         default:
   82           result+=s[i];
   83       }
   84 
   85   return result;
   86 }
   87 
   88 bool Database::greylisted(string ip,string from,string to,int initial_expiry,int initial_blacklist,int whitelist_expiry)
   89 {
   90   char **result;
   91   int nrow=0;
   92   int ncolumn=0;
   93   bool retval=true;
   94   bool was_busy=false;
   95   int sqlite_retval;
   96   int now=time(NULL);
   97   string strnow=Utils::inttostr(now);
   98   string sql="SELECT id,blocked_until FROM greylist WHERE ip=\""+ip+"\" AND emailfrom=\""+from+"\" AND emailto=\""+to+"\" AND "+strnow+" < expires LIMIT 1;";
   99 
  100   do
  101   {
  102     sqlite_retval=sqlite3_get_table(dbh,sql.c_str(),&result,&nrow,&ncolumn,NULL);
  103     if(sqlite_retval!=SQLITE_OK&&sqlite_retval!=SQLITE_BUSY)
  104     {
  105       if(NULL!=result)
  106         sqlite3_free_table(result);
  107       throw SQLException("SQL: "+sql+" sqlite3_errmsg: "+sqlite3_errmsg(dbh),__FILE__,__LINE__);
  108     }
  109     if(SQLITE_BUSY==sqlite_retval)
  110     {
  111       sleep(1+rand()%2);
  112       LERR("greylisted() sql failed with busy state, retrying");
  113       was_busy=true;
  114     }
  115   }
  116   while(sqlite_retval==SQLITE_BUSY);
  117   if(was_busy)
  118     LERR("greylisted() executed correctly after failing initially");
  119 
  120   sql="";
  121 
  122   if(nrow>0)
  123   {
  124     string id=result[2];
  125     //we have seen this triplet before
  126     if(now<atol(result[3]))
  127     {
  128       sql="UPDATE greylist SET blocked=blocked+1 WHERE id="+id+";";
  129       doQuery(sql.c_str());
  130       retval=true;
  131     }
  132     else
  133     {
  134       string expires=Utils::inttostr(now+(60*60*24*whitelist_expiry));
  135 
  136       sql="UPDATE greylist SET expires="+expires+",passed=passed+1 WHERE id="+id+";";
  137       doQuery(sql.c_str());
  138       retval=false;
  139     }
  140   }
  141   else
  142   {
  143     string blocked_until=Utils::inttostr(now+(60*initial_blacklist));
  144     string expires=Utils::inttostr(now+(60*initial_expiry));
  145     //new triplet, greylist and add new row
  146     retval=true;
  147     sql="INSERT INTO greylist(id,ip,emailfrom,emailto,created,blocked_until,expires,passed,blocked)"
  148       "VALUES(NULL,\""+ip+"\",\""+from+"\",\""+to+"\","+strnow+","+blocked_until+","+expires+",0,1);";
  149     doQuery(sql.c_str());
  150   }
  151   if(NULL!=result)
  152     sqlite3_free_table(result);
  153   return retval;
  154 }
  155 
  156 Database::Database():dbh(NULL)
  157 {}
  158 
  159 Database::~Database()
  160 {
  161   close();
  162 }
  163 
  164 void Database::close()
  165 {
  166   if(NULL!=dbh)
  167     sqlite3_close(dbh);
  168 }
  169 
  170 void Database::_open()
  171 {
  172   if(sqlite3_open(dbfile.c_str(),&dbh))
  173   {
  174     dbh=NULL;
  175     throw Exception(_("Error creating/opening db ")+dbfile,__FILE__,__LINE__);
  176   }
  177 }
  178 
  179 void Database::open()
  180 {
  181   // if dbfile is new, initialize first
  182   if(!Utils::file_exists(dbfile))
  183     init();
  184 
  185   _open();
  186 }
  187 
  188 void Database::init()
  189 {
  190   _open();
  191   doQuery("CREATE TABLE whitelisted_ips(ip VARCHAR);");
  192   doQuery("CREATE TABLE whitelisted_tos(email VARCHAR);");
  193   doQuery("CREATE TABLE whitelisted_domains(domain VARCHAR);");
  194   doQuery("CREATE TABLE whitelisted_hostnames(hostname VARCHAR);");
  195   doQuery("CREATE TABLE blacklisted_tos(email VARCHAR);");
  196   doQuery("CREATE TABLE blacklisted_todomains(domain VARCHAR);");
  197   doQuery("CREATE TABLE blacklisted_ips(ip VARCHAR);");
  198   doQuery("CREATE TABLE blacklisted_froms(email VARCHAR);");
  199   doQuery("CREATE TABLE allowed_domains_per_ip(domain VARCHAR,ip VARCHAR);");
  200   doQuery("CREATE TABLE greylist(id INTEGER PRIMARY KEY,ip VARCHAR,emailfrom VARCHAR,emailto VARCHAR,created INTEGER,blocked_until INTEGER,expires INTEGER,passed INTEGER,blocked INTEGER);");
  201 
  202   //whitelist localhost
  203   doQuery("INSERT INTO whitelisted_ips(ip) VALUES(\"127.0.0.1\");");
  204 
  205   close();
  206 }
  207 
  208 int Database::countRows(string p_sql)
  209 {
  210   char **result;
  211   int nrow=0;
  212   int ncolumn=0;
  213   int sqlite_retval;
  214   bool was_busy=false;
  215 
  216   do
  217   {
  218     sqlite_retval=sqlite3_get_table(dbh,p_sql.c_str(),&result,&nrow,&ncolumn,NULL);
  219     if(SQLITE_OK!=sqlite_retval&&SQLITE_BUSY!=sqlite_retval)
  220     {
  221       if(NULL!=result)
  222         sqlite3_free_table(result);
  223       throw SQLException("SQL: "+p_sql+" sqlite3_errmsg: "+sqlite3_errmsg(dbh),__FILE__,__LINE__);
  224     }
  225     if(SQLITE_BUSY==sqlite_retval)
  226     {
  227       sleep(1+rand()%2);
  228       LERR("countRows() sql failed with busy state, retrying");
  229       was_busy=true;
  230     }
  231   }
  232   while(SQLITE_BUSY==sqlite_retval);
  233   if(was_busy)
  234     LERR("countRows() executed correctly after failing initially");
  235 
  236   if(NULL!=result)
  237     sqlite3_free_table(result);
  238 
  239   if(ncolumn)
  240     return (nrow/ncolumn);
  241   else
  242     return nrow;
  243 }
  244 
  245 bool Database::whitelistedIP(string p_ip)
  246 {
  247   string sql="";
  248 
  249   sql="SELECT ip FROM whitelisted_ips WHERE ip=\""+p_ip+"\" LIMIT 1;";
  250 
  251   if(countRows(sql)>0)
  252     return true;
  253   else
  254     return false;
  255 }
  256 
  257 bool Database::whitelistedTO(string p_email)
  258 {
  259   string sql="";
  260 
  261   sql="SELECT email FROM whitelisted_tos WHERE email=\""+p_email+"\" LIMIT 1;";
  262 
  263   if(countRows(sql)>0)
  264     return true;
  265   else
  266     return false;
  267 }
  268 
  269 bool Database::whitelistedDomain(string p_domain)
  270 {
  271   string sql="";
  272 
  273   sql="SELECT domain FROM whitelisted_domains WHERE domain=\""+p_domain+"\" LIMIT 1;";
  274 
  275   if(countRows(sql)>0)
  276     return true;
  277   else
  278     return false;
  279 }
  280 
  281 bool Database::blacklistedTO(string p_email)
  282 {
  283   string sql="";
  284 
  285   sql="SELECT email FROM blacklisted_tos WHERE email=\""+p_email+"\" LIMIT 1;";
  286 
  287   if(countRows(sql)>0)
  288     return true;
  289   else
  290     return false;
  291 }
  292 
  293 bool Database::blacklistedToDomain(string p_domain)
  294 {
  295   string sql="";
  296 
  297   sql="SELECT domain FROM blacklisted_todomains WHERE domain=\""+p_domain+"\" LIMIT 1;";
  298 
  299   if(countRows(sql)>0)
  300     return true;
  301   else
  302     return false;
  303 }
  304 
  305 bool Database::blacklistedIP(string p_ip)
  306 {
  307   string sql="";
  308 
  309   sql="SELECT ip FROM blacklisted_ips WHERE ip=\""+p_ip+"\" LIMIT 1;";
  310 
  311   if(countRows(sql)>0)
  312     return true;
  313   else
  314     return false;
  315 }
  316 
  317 bool Database::blacklistedFROM(string p_email)
  318 {
  319   string sql="";
  320 
  321   sql="SELECT email FROM blacklisted_froms WHERE email=\""+p_email+"\" LIMIT 1;";
  322 
  323   if(countRows(sql)>0)
  324     return true;
  325   else
  326     return false;
  327 }
  328 
  329 bool Database::whitelistedHostname(string p_hostname)
  330 {
  331   string sql="";
  332 
  333   sql="SELECT hostname FROM whitelisted_hostnames WHERE hostname=SUBSTR(\""+p_hostname+"\",-LENGTH(hostname),LENGTH(hostname)) LIMIT 1;";
  334 
  335   if(countRows(sql)>0)
  336     return true;
  337   else
  338     return false;
  339 }
  340 
  341 bool Database::allowedDomainPerIP(string p_domain,string p_ip)
  342 {
  343   string sql="",sql_domain="";
  344 
  345   sql="SELECT ip FROM allowed_domains_per_ip WHERE domain=\""+p_domain+"\" AND ip=\""+p_ip+"\" LIMIT 1;";
  346   sql_domain="SELECT ip FROM allowed_domains_per_ip WHERE domain=\""+p_domain+"\" LIMIT 1;";
  347 
  348   if(countRows(sql_domain)>0&&0==countRows(sql))
  349     return false;
  350   else
  351     return true;
  352 }
  353 
  354 /**
  355  * this function returns an integer value from a sql
  356  * it is useful to calculate things with sql
  357  *
  358  * i.e.: SELECT SUM(intfield) FROM table
  359  *
  360  * @param p_sql SQL query to perform
  361  *
  362  * @return the first value of the first column, rest of data is ignored
  363  */
  364 unsigned long Database::getIntValue(string& p_sql)
  365 {
  366   char **result;
  367   int nrow=0;
  368   int ncolumn=0;
  369   int sqlite_retval;
  370   bool was_busy=false;
  371   unsigned long value;
  372 
  373   do
  374   {
  375     sqlite_retval=sqlite3_get_table(dbh,p_sql.c_str(),&result,&nrow,&ncolumn,NULL);
  376     if(SQLITE_OK!=sqlite_retval&&SQLITE_BUSY!=sqlite_retval)
  377     {
  378       if(NULL!=result)
  379         sqlite3_free_table(result);
  380       throw SQLException("SQL: "+p_sql+" sqlite3_errmsg: "+sqlite3_errmsg(dbh),__FILE__,__LINE__);
  381     }
  382     if(SQLITE_BUSY==sqlite_retval)
  383     {
  384       sleep(1+rand()%2);
  385       LERR("getIntValue() sql failed with busy state, retrying");
  386       was_busy=true;
  387     }
  388   }
  389   while(SQLITE_BUSY==sqlite_retval);
  390   if(was_busy)
  391     LERR("getIntValue() executed correctly after failing initially");
  392 
  393   if(NULL==result)
  394     throw SQLException("SQL: "+p_sql+" didn't return any data, SQL query may be wrong",__FILE__,__LINE__);
  395   if('\0'==result[ncolumn])
  396     value=0; //why sqlite doesn't return 0 when there are no rows?
  397   else
  398     value=strtoul(result[ncolumn],NULL,10);
  399   sqlite3_free_table(result);
  400 
  401   return value;
  402 }
  403 
  404 /**
  405  * clean the spam database and return the number of spam messages
  406  *
  407  * @return number of spam messages deleted
  408  */
  409 unsigned long Database::cleanDB()
  410 {
  411   unsigned long spamcount=0; //shut compiler up
  412   string sql;
  413 
  414   try
  415   {
  416     //block database until we have finished cleaning it
  417     doQuery("BEGIN EXCLUSIVE TRANSACTION");
  418 
  419     //now count how many blocked emails we have to submit to stats
  420     //we do it always because if we don't submit stats it stills appears on the logs
  421     sql="SELECT SUM(blocked) FROM greylist WHERE expires<strftime('%s','now') AND passed=0;";
  422     spamcount=getIntValue(sql);
  423     LINF("We have processed " + Utils::ulongtostr(spamcount) + " spam emails in the last 4 hours");
  424 
  425     //at last, delete them from the database
  426     doQuery("DELETE FROM greylist WHERE expires<strftime('%s','now');");
  427 
  428     //and close the transaction
  429     doQuery("COMMIT TRANSACTION");
  430   }
  431   catch(Exception &e)
  432   {
  433     LERR(e);
  434     doQuery("ROLLBACK TRANSACTION");
  435   }
  436 
  437   return spamcount;
  438 }