db.cpp

/***************************************************************************
 *   Copyright (C) 2004 by Michael Moritz                                  *
 *   mimo@restoel.net                                                      *
 *                                                                         *
 *   This program is free software; you can redistribute it and/or modify  *
 *   it under the terms of the GNU General Public License as published by  *
 *   the Free Software Foundation; either version 2 of the License, or     *
 *   (at your option) any later version.                                   *
 *                                                                         *
 *   This program is distributed in the hope that it will be useful,       *
 *   but WITHOUT ANY WARRANTY; without even the implied warranty of        *
 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         *
 *   GNU General Public License for more details.                          *
 *                                                                         *
 *   You should have received a copy of the GNU General Public License     *
 *   along with this program; if not, write to the                         *
 *   Free Software Foundation, Inc.,                                       *
 *   59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.             *
 ***************************************************************************/

#ifdef HAVE_CONFIG_H
#include <config.h>
#endif

#include "db.h"
#include "defs.h"
//#include <signal.h>
#include <syslog.h>
#include <iostream>
#include <iomanip>
#include "triplet.h"
#include <sstream>
#include <stdexcept>
#include "cfg.h"
#include "tmplwlmod.h"
#include "wldb.h"
#include "wlcacheddb.h"
#include "dbdefs.h"
#include "dbiquote.h"

#define WEAKRVHOSTDBG   false

using namespace std;
// define the wl modules
typedef TmplWlMod<WLDB> DbWlModule;
typedef TmplWlMod<WlCachedDB> CachedDbWlModule;

DB::DB() throw(std::exception)
        : _con(0),_graceful_quit(0)
{
        if(dbi_initialize(0) <= 0)
                throw runtime_error("DB: no dbi drivers installed, or none found");
//      bool verbose = g_getCfg().isVerbose();
//      bool initmode = g_getCfg().isModeInit();
        /*      
        const char* dbdriver = g_getCfg().getDbType().c_str();
        _con = dbi_conn_new(dbdriver);
        if(!_con) {
                stringstream stm;
                stm << "DB: connection/driver " << dbdriver << " could not be opened\nAvailable drivers:\n";
                list_drivers(stm);
                throw runtime_error(stm.str());
        } else {
                if(verbose)
                        syslog(LOG_DEBUG,"connecting to DB, using driver %s",dbdriver);
        }
        const ParamVector& params = g_getCfg().getDbParamVector();
        for(ParamVector::const_iterator itr=params.begin(); itr != params.end();++itr) {
                // note: dbi_conn_set_option 2nd arg is non-const
                const char *key = (*itr).c_str();
                ++itr;
                if(itr != params.end()) {
                        if((*itr).length() < (tempCharBuf-1) ) {
                                const char *value = (*itr).c_str();
                                char szBuf[tempCharBuf];
                                strcpy(szBuf,value);
                                if(verbose) {
                                        if(string(key) == "password") 
                                                syslog(LOG_DEBUG,"setting DB option: %s to: (hidden)",key);
                                        else
                                                syslog(LOG_DEBUG,"setting DB option: %s to: %s",key,szBuf);
                                                
                                }
                                if(dbi_conn_set_option(_con, key, szBuf) != 0)
                                        throw runtime_error( getConError("dbi_conn_set_option "+(*itr)) );
                        }
                }
        }
        if(dbi_conn_connect(_con) != 0)
                throw runtime_error( getConError("dbi_conn_connect") );
        if(verbose)
                syslog(LOG_DEBUG,"connected to DB");
        */      
        open();
        // initialise triplets table
//      if(initmode)
//              createTable(g_getTripletDef());
}
void DB::open() throw (std::exception)
{
        bool initmode = g_getCfg().isModeInit();
        bool verbose = g_getCfg().isVerbose();
        if(_con) 
                throw runtime_error("already connected to db");
        const char* dbdriver = g_getCfg().getDbType().c_str();
        _con = dbi_conn_new(dbdriver);
        if(!_con) {
                stringstream stm;
                stm << "DB: connection/driver " << dbdriver << " could not be opened\nAvailable drivers:\n";
                list_drivers(stm);
                throw runtime_error(stm.str());
        } else {
                if(verbose)
                        syslog(LOG_DEBUG,"connecting to DB, using driver %s",dbdriver);
        }
        const ParamVector& params = g_getCfg().getDbParamVector();
        for(ParamVector::const_iterator itr=params.begin(); itr != params.end();++itr) {
                // note: dbi_conn_set_option 2nd arg is non-const
//              const char *key = (*itr).c_str();
                string strKey = (*itr);
                ++itr;
                if(itr != params.end()) {
                        if(verbose) {
                                string strLog = (strKey=="password") ? "setting DB option: "+strKey+" to: (hidden)"
                                        : "setting DB option: "+strKey+" to: "+(*itr);
                                syslog(LOG_DEBUG,ESC_LOG(strLog).c_str());
                        }
                        string strValue = (*itr);
                        if(dbi_conn_set_option(_con, strKey.c_str(), strValue.c_str()) != 0)
                                throw runtime_error( getConError("dbi_conn_set_option "+(*itr)) );
                }
        }
        if(dbi_conn_connect(_con) != 0)
                throw runtime_error( getConError("dbi_conn_connect") );
        if(verbose)
                syslog(LOG_DEBUG,"connected to DB");
        if(_wlMods.size() == 0)
                init_wlMods();
        // initialise triplets table
        if(initmode)
                createTable(g_getTripletDef());
}

void DB::close()
{
//      s_db_object = 0;
        for(WlModuleList::const_iterator itr=_wlMods.begin(); itr != _wlMods.end();++itr) 
                delete (*itr);  
        _wlMods.clear();        
        if(_con) {
//              signal (SIGTERM, SIG_DFL);
                syslog(LOG_INFO,"disconnecting from DB");       
                dbi_conn_close(_con);
                _con = 0;
        }
        dbi_shutdown();
}
int DB::handle_signal(int signum) {
        syslog(LOG_DEBUG,"received signal %i",signum);
//      assert (signum == SIGINT);
        this->_graceful_quit = 1;
        this->close();
        return 0;
}
void DB::init_wlMods() {
        bool verbose = g_getCfg().isVerbose();
        bool initmode = g_getCfg().isModeInit();
        try {
                // initialise whitelisting modules      
                const Cfg::WLMap& wlMap = g_getCfg().getWLMap();
                for(Cfg::WLMap::const_iterator itr=wlMap.begin(); itr != wlMap.end(); ++itr) {
                        bool created = false;
                        const std::string& tableName = itr->first;
                        //cout << "tableName: " << tableName << endl;
                        const WLStaticDef& wldef = g_getWlDef(tableName); //throws if not found
                        switch(itr->second.getMode()) {
                                case Cfg::WLEntry::WL_db:
                                        _wlMods.push_back(new DbWlModule (*this, initmode, wldef) );
                                        created = true;
                                        break;
                                case Cfg::WLEntry::WL_dbcached:
                                        _wlMods.push_back(new CachedDbWlModule (*this, initmode, wldef) );
                                        created = true;
                                        break;
                                case Cfg::WLEntry::WL_off:
                                        break;
                                default:
                                        throw runtime_error("wl mode not supported for table: "+tableName+". Check your configuration file.");
                        }
                        if(verbose && created)
                                syslog(LOG_DEBUG,"created wl module: %s, type: %s",
                                           tableName.c_str(),g_getCfg().getWlModeName(itr->second.getMode()).c_str());  
                }
        } catch(runtime_error &e) { //catch exception, dont exit, wl failure is nonfatal, but log error
                syslog(LOG_ERR,"Error while initialising wl module: %s",e.what());
        }
}

std::string DB::getConError(const std::string& pre) const
{
        if(_con != 0) {
                const char *errBuff;
                dbi_conn_error(_con,&errBuff);
                return "DB: "+pre+": "+string(errBuff)+" ";
        } else {
                return "DB: "+pre+": no connection to db";
        }
}
void DB::list_drivers(ostream &os)
{
        dbi_driver drv = 0;
        while((drv=dbi_driver_list(drv)) != 0) {
                os << " " << dbi_driver_get_name(drv);
        }
}
const std::string DB::trimLastByte(std::string str)
{
        string::size_type lastdotpos = str.rfind('.');
        if(lastdotpos != string::npos) {
                str.erase(lastdotpos,string::npos);
                str += '.';
        }
        return str;
}
//TODO optimise this sql query 
//              nogo: REPLACE because =DELETE+INSERT
//              maybe: http://dev.mysql.com/doc/mysql/en/HANDLER.html
DB::RecordStatus DB::update(const Triplet& triplet) throw(std::exception)
{
// unlikely to happen
        if(!_con)
                throw runtime_error(getConError("update"));
        if(dbi_conn_ping(_con) != 1) {
                _con = 0;
                syslog(LOG_INFO,"lost db connection, reconnecting");
                this->open();
        }
        dbi_driver dbidrv = dbi_conn_get_driver(_con);
        DBIQuote sender(dbidrv, triplet.getSender().substr(0,MAX_LEN_SENDER));
        DBIQuote recipient(dbidrv, triplet.getRecipient().substr(0,MAX_LEN_RECIPIENT));
        DBIQuote client(dbidrv, triplet.getClientAddress().substr(0,MAX_LEN_CLIENT));
        string clientLoggable = client();
        string clientNumeric = triplet.getClientAddressNumeric();
        bool weak = g_getCfg().isModeWeak();
        bool reverse = g_getCfg().isModeReverse();
        bool verbose = g_getCfg().isVerbose();
        RecordStatus recStatus = unknown;
        time_t ltime;
        dbi_result result;
        
        if(clientNumeric.size() != IPADDR_NUM)
                throw runtime_error("Invalid IP Address: "+triplet.getClientAddress()
                        + " parsed: "+triplet.getClientAddressNumericStr());
        // check triplet against wl modules
        if(!_wlMods.empty()) {
                Triplet quotedTriplet(client(),sender(),recipient(),clientNumeric); 
                for(WlModuleList::const_iterator itr=_wlMods.begin(); itr != _wlMods.end();++itr) 
                        if( (*itr)->check(quotedTriplet) )
                                return registered;
        }
        //
        unsigned clientIpBytes = clientNumeric.size();
        stringstream sqlSelect;
        stringstream sqlWhere;
        //construct the sql select string
        sqlSelect << "SELECT count,uts FROM "TABLE_TRIPLETS" WHERE ( ";
        sqlWhere << ATTR_SENDER"=" << sender() << " AND "ATTR_RECIPIENT"=" << recipient();
//      string sqlOrder;
        if(reverse) { //weak greylisting, reverse name lookups
                //bDoReplace = true; //use replace instead of INSERT for similiar entries
                string revClient = getWeakHost(triplet.getResolved());
                if(revClient.size() > 0) {
                        transform(revClient.begin(),revClient.end(),revClient.begin(), ToLower());
                        client = DBIQuote(dbidrv,revClient);
                        clientLoggable = client();
                        sqlWhere << " AND "ATTR_CLIENT"=" << client();
                } else {
                        // check for name resolution error 
                        // and fallback to IP based weak g-listing
                        if(verbose) {
                                if(triplet.getResolveError().size() > 0) {
                                        //log the name resolution error
                                        syslog(LOG_DEBUG,"name resolve error: %s, %s",triplet.getResolveError().c_str(),client());
                                } else {
                                        //log my name conversion's error
                                        syslog(LOG_INFO,"host name conversion failed: %s",client());
                                }
                        }
                        reverse = false;
                        weak = true;
                        client.setNULL();
                }
        }
        if(weak) { // weak greylisting
                clientIpBytes = g_getCfg().getWeakBytes();
                if( (clientIpBytes < 1) || (clientIpBytes > clientNumeric.size()) )
                        throw runtime_error("Invalid client IP bytes (weakbytes in config): "
                                +numToStr(clientIpBytes)+" (1-"+numToStr(clientNumeric.size())+"): "+triplet.getClientAddress()
                                + " parsed: "+triplet.getClientAddressNumericStr());
        }
        if(!reverse)
                sqlWhere << " AND " << triplet.getClientAddressNumericStrSQL(" AND ","ip",64,"=",2,clientIpBytes);      
        sqlSelect << sqlWhere.str() << " )"; 
        
        //cout << clientIpBytes << " " << sqlSelect.str() << endl;
        //exit(0);
        //const char *pcz = sqlSelect.str().c_str();
        
        if(verbose)
                syslog(LOG_DEBUG,ESC_LOG(sqlSelect.str()).c_str());
//                      syslog(LOG_DEBUG,strEscape(sqlSelect.str(),'%','%',sqlSelect.str().size()).c_str());
        result = DBI_CON_QUERYF(_con, ESC_LOG(sqlSelect.str()).c_str());
        if(!result)
                throw runtime_error(getConError("update select: ")+sqlSelect.str()+" " );
//              throw runtime_error(getConError("update select: ")+sqlSelect.str()+" " );

        time(&ltime); //get ultime (unix timestamp) in ltime 
        unsigned long lNow = ltime;
        int rows = dbi_result_get_numrows(result);
        if( rows == 0) { // new triplet
                dbi_result_free(result);
                syslog(LOG_INFO,"new: %s -> %s, %s",sender(),recipient(),clientLoggable.c_str());
                //since we dont name the fields anymore this has to be in sync with the dbdefs.cpp definition
                stringstream sqlInsert;
                /*
                if(bDoReplace) //NOTE Postgresql doesnt have REPLACE!!
                        sqlInsert << "REPLACE";
                else
                        sqlInsert << "INSERT"; 
                sqlInsert << " INTO "TABLE_TRIPLETS" VALUES (" << client() << "," << sender() << ',' << recipient() << ',';
                */
                sqlInsert << "INSERT INTO "TABLE_TRIPLETS" VALUES (" << client() << "," << sender() << ',' << recipient() << ',';
                sqlInsert << triplet.getClientAddressNumericStr(',') << ",0," << lNow << ")";
                if(verbose)
                        syslog(LOG_DEBUG,ESC_LOG(sqlInsert.str()).c_str());
                result = DBI_CON_QUERYF( _con, ESC_LOG(sqlInsert.str()).c_str() );
                if(!result)
                        throw runtime_error(getConError("update insert: ")+sqlInsert.str() );
                recStatus = unknown;
        } else { 
                dbi_result_next_row(result); 
                unsigned long count = dbi_result_get_ulong_idx(result,1);
                unsigned long recTimeStamp = dbi_result_get_ulong_idx(result,2);
                unsigned long lTimeDiff = 0;
                
                if(recTimeStamp < lNow)  // not in future
                        lTimeDiff = lNow - recTimeStamp;
                if( (count > 0) || (lTimeDiff > g_getCfg().getTimeout()) ) {
                        syslog(LOG_INFO,"ok: %s -> %s, %s (%lu, %lu secs)",sender(),recipient(),clientLoggable.c_str(),
                                count,lTimeDiff);
                        dbi_result_free(result);
                        recTimeStamp = lNow;
                        stringstream sqlUpdate;
                        sqlUpdate << "UPDATE "TABLE_TRIPLETS" SET count=count+1,uts=" << lNow;
                        sqlUpdate << " WHERE ( " << sqlWhere.str() << " )";
                        if(verbose)
                                syslog(LOG_DEBUG,ESC_LOG(sqlUpdate.str()).c_str());
                        result = DBI_CON_QUERYF(_con,ESC_LOG(sqlUpdate.str()).c_str());
                        if(!result)
                                throw runtime_error(getConError("update update: ")+sqlUpdate.str());
                        recStatus = registered;
                } else { // waiting triplet
                        syslog(LOG_INFO,"wait: %s -> %s, %s (%lu, %lu secs)",sender(),recipient(),clientLoggable.c_str(),
                                   count,lTimeDiff);
                        recStatus = waiting;
                }
        }
        if(result)
                dbi_result_free(result);
        return recStatus; 
}
dbi_result DB::executeSQL(const std::string& sql) throw(std::exception)
{
        if(!_con)
                throw runtime_error(getConError("executeSQL"));
        dbi_result result = DBI_CON_QUERYF(_con, ESC_LOG(sql).c_str() );
        if(!result) {
                syslog(LOG_INFO,"SQL error: %s",getConError("executeSQL").c_str());
//              throw runtime_error( getConError("executeSQL") );
        }
        return result;
}
void DB::createTable(const WLStaticDef& staticDef) throw(std::exception)
{       
        if(!_con)
                throw runtime_error(getConError("createTable: no db connection"));
        bool verbose = g_getCfg().isVerbose();
        stringstream sqlTestTable;
        sqlTestTable << staticDef._testTable << " WHERE 1=0"; //more portable than " LIMIT 1";
        if(verbose)
                syslog(LOG_DEBUG,sqlTestTable.str().c_str());
        dbi_result result = DBI_CON_QUERYF(_con, sqlTestTable.str().c_str() );
        if(!result) {
                syslog(LOG_INFO,"testing table failed: %s : %s, creating table",
                        staticDef._tableName,getConError("").c_str());
                unsigned iCT = 0;
                while( staticDef._createTable[iCT] ) {
                        stringstream sqlCreateTable;
                        sqlCreateTable << staticDef._createTable[iCT];
                        if(staticDef._primaryKey)
                                sqlCreateTable << " PRIMARY KEY (" << staticDef._primaryKey << " )";
                        sqlCreateTable << ")";
                        if(verbose)
                                syslog(LOG_DEBUG,ESC_LOG(sqlCreateTable.str()).c_str());
                        result = DBI_CON_QUERYF(_con, sqlCreateTable.str().c_str());
                        if(!result && (g_getCfg().getDbType() == "sqlite") ) {
                                //table created, sqlite driver currently returns empty result nevertheless
                                result = DBI_CON_QUERYF(_con, sqlTestTable.str().c_str() );
                        }
                        if(!result) {
                                        throw runtime_error( getConError("create table: ")+staticDef._tableName
                                                + string( verbose?" SQL: "+sqlCreateTable.str() : "") );
                        } else {
                                dbi_result_free(result);
                                result = 0;
                        }
                        iCT++;
                }
                syslog(LOG_INFO,"table %s created", staticDef._tableName );
        }
        if(result)
                dbi_result_free(result);
}
std::string DB::getSqlCondition(const WLCompares& compares,const Triplet& triplet) const
{
        string sql;
        int j = 0;
        while(j < MAX_WL_COMPARES) {
                if(compares[j]._compareWith) {
                        if(compares[j]._compareBinder)
                                sql += std::string(" ") + compares[j]._compareBinder + " ";
                        sql += compares[j]._compareWith;
                        sql += compares[j]._compareOp;
                        sql += triplet.getMember(compares[j]._tripletMember);
                        j++;
                } else
                        break;
        }
        return sql;
}
bool DB::exactMatch(const std::string& logprefix,const WLStaticDef& staticDef,
        const Triplet& triplet) throw(std::exception)
{
        bool verbose = g_getCfg().isVerbose();
        string sqlWlSelect = staticDef._searchTable;
        sqlWlSelect += " WHERE " + getSqlCondition(staticDef._compares,triplet);
        sqlWlSelect += " LIMIT 1";
        if(verbose)
                syslog(LOG_DEBUG,ESC_LOG(sqlWlSelect).c_str());
        dbi_result result = DBI_CON_QUERYF(_con, ESC_LOG(sqlWlSelect).c_str());
        if(!result)
                throw runtime_error(getConError("exactMatch")+staticDef._tableName );
        int rows = dbi_result_get_numrows(result);
        if( rows == 0) {
                dbi_result_free(result);
                return false;
        } else {
                dbi_result_next_row(result); 
                const char *comment = dbi_result_get_string_idx(result,1);
                syslog(LOG_INFO,"%s %s: %s -> %s, %s: %s",
                        logprefix.c_str(),staticDef._tableName,triplet.getSender().c_str(),
                        triplet.getRecipient().c_str(),triplet.getClientAddress().c_str(),comment);
                dbi_result_free(result);
                return true;
        }
}
const std::string DB::numToStr(unsigned u) const
{
        stringstream stm;
        stm << u;
        return stm.str();
}
std::string DB::getWeakHost(const std::string& host) const throw(std::exception)
{
        if(WEAKRVHOSTDBG)       cout << host << " len:" << host.size() <<endl;
        if(host.size() == 0) // not resolved
                return string(host);
        string::size_type pos = host.find('.');
        if(pos == string::npos) // no dot in name
                return string(host);
        if(WEAKRVHOSTDBG)       cout << host.substr(pos) << " pos:" << pos << endl;
        if(WEAKRVHOSTDBG)       cout << host.substr(pos+1,string::npos) << endl;
        return host.substr(pos+1,string::npos);
}

---