bool
PersistentGreyList::AddObject(shared_ptr<GreyListTriplet> pTriplet)
//---------------------------------------------------------------------------()
// DESCRIPTION:
// Adds a new greylist triple into the database.
//---------------------------------------------------------------------------()
{
String sSenderAddress = pTriplet->GetSenderAddress().Left(200);
String sRecipientAddress = pTriplet->GetRecipientAddress().Left(200);
// Update the triplet
SQLStatement oStatement;
IPAddressSQLHelper helper;
oStatement.SetTable("hm_greylisting_triplets");
oStatement.AddColumn("glcreatetime", pTriplet->GetCreateTime());
oStatement.AddColumn("glblockendtime", pTriplet->GetBlockEndTime());
oStatement.AddColumn("gldeletetime", pTriplet->GetDeleteTime());
helper.AppendStatement(oStatement, pTriplet->GetIPAddress(), "glipaddress1", "glipaddress2");
oStatement.AddColumn("glsenderaddress", sSenderAddress);
oStatement.AddColumn("glrecipientaddress", sRecipientAddress);
oStatement.AddColumn("glpassedcount", "0");
oStatement.AddColumn("glblockedcount", "0");
oStatement.SetStatementType(SQLStatement::STInsert);
oStatement.SetIdentityColumn("glid");
bool bResult = Application::Instance()->GetDBManager()->Execute(oStatement, 0, DALConnection::DALErrorInSQL);
return bResult;
}
bool
PersistentTCPIPPort::ReadObject(shared_ptr<TCPIPPort> pObject, shared_ptr<DALRecordset> pRS)
{
IPAddressSQLHelper helper;
pObject->SetID (pRS->GetLongValue("portid"));
pObject->SetProtocol((SessionType) pRS->GetLongValue("portprotocol"));
pObject->SetPortNumber(pRS->GetLongValue("portnumber"));
//JDR: added to load set use STARTTLS.
long _portSSL = pRS->GetLongValue("portusessl");
if (_portSSL == 1)
{
// use regular SSL
pObject->SetUseSSL(true);
pObject->SetUseSTARTTLS(false);
}
else if (_portSSL == 2)
{
// use starttls
pObject->SetUseSSL(false);
pObject->SetUseSTARTTLS(true);
}
else
{
// all other options are invalid, set all to false.
pObject->SetUseSSL(false);
pObject->SetUseSTARTTLS(false);
}
//JDR: end mod
pObject->SetAddress(helper.Construct(pRS, "portaddress1", "portaddress2"));
pObject->SetSSLCertificateID(pRS->GetLongValue("portsslcertificateid"));
return true;
}
bool
PersistentSecurityRange::SaveObject(shared_ptr<SecurityRange> pSR, String &result)
{
if (!Validate(pSR, result))
return false;
DateTime rangeExpiresTime = pSR->GetExpiresTime();
if (rangeExpiresTime.GetStatus() != DateTime::valid)
rangeExpiresTime.SetDateTime(2001,01,01,0,0,0);
String name = pSR->GetName();
if (name.GetLength() > 100)
name = name.Mid(0, 100);
IPAddressSQLHelper helper;
SQLStatement oStatement;
oStatement.SetTable("hm_securityranges");
oStatement.AddColumn("rangename", name);
oStatement.AddColumn("rangepriorityid", pSR->GetPriority());
helper.AppendStatement(oStatement, pSR->GetLowerIP(), "rangelowerip1", "rangelowerip2");
helper.AppendStatement(oStatement, pSR->GetUpperIP(), "rangeupperip1", "rangeupperip2");
oStatement.AddColumn("rangeoptions", pSR->GetOptions());
oStatement.AddColumn("rangeexpires", pSR->GetExpires());
oStatement.AddColumn("rangeexpirestime", Time::GetTimeStampFromDateTime(rangeExpiresTime));
if (pSR->GetID() == 0)
{
oStatement.SetStatementType(SQLStatement::STInsert);
oStatement.SetIdentityColumn("rangeid");
}
else
{
oStatement.SetStatementType(SQLStatement::STUpdate);
String sWhere;
sWhere.Format(_T("rangeid = %I64d"), pSR->GetID());
oStatement.SetWhereClause(sWhere);
}
bool bNewObject = pSR->GetID() == 0;
// Save and fetch ID
__int64 iDBID = 0;
bool bRetVal = Application::Instance()->GetDBManager()->Execute(oStatement, bNewObject ? &iDBID : 0);
if (bRetVal && bNewObject)
pSR->SetID((int) iDBID);
if (!bRetVal)
result = "Failed to save. Please see the hMailServer error log for details.";
return bRetVal;
}
shared_ptr<SecurityRange>
PersistentSecurityRange::ReadMatchingIP(const IPAddress &ipaddress)
{
shared_ptr<SecurityRange> empty;
IPAddressSQLHelper helper;
String sSQL;
if (ipaddress.GetType() == IPAddress::IPV4)
{
shared_ptr<SecurityRange> pSR = shared_ptr<SecurityRange>(new SecurityRange());
sSQL.Format(_T("select * from hm_securityranges where %s >= rangelowerip1 and %s <= rangeupperip1 and rangelowerip2 IS NULL and rangeupperip2 IS NULL order by rangepriorityid desc"),
String(helper.GetAddress1String(ipaddress)), String(helper.GetAddress1String(ipaddress)));
if (!ReadObject(pSR, SQLCommand(sSQL)))
return empty;
return pSR;
}
else
{
// Read all IPv6 items.
shared_ptr<SecurityRange> bestMatch;
SQLCommand command(_T("select * from hm_securityranges where rangelowerip2 is not null order by rangepriorityid desc"));
shared_ptr<DALRecordset> recordset = Application::Instance()->GetDBManager()->OpenRecordset(command);
if (!recordset)
return empty;
while (!recordset->IsEOF())
{
shared_ptr<SecurityRange> securityRange = shared_ptr<SecurityRange>(new SecurityRange());
if (ReadObject(securityRange, recordset) == false)
return empty;
if (ipaddress.WithinRange(securityRange->GetLowerIP(), securityRange->GetUpperIP()))
{
// This IP range matches the client. Does it have higher prio than the currently
// matching?
if (!bestMatch || securityRange->GetPriority() > bestMatch->GetPriority())
bestMatch = securityRange;
}
recordset->MoveNext();
}
return bestMatch;
}
}
bool
PersistentTCPIPPort::SaveObject(shared_ptr<TCPIPPort> pObject)
{
SQLStatement oStatement;
oStatement.SetTable("hm_tcpipports");
if (pObject->GetID() == 0)
{
oStatement.SetStatementType(SQLStatement::STInsert);
oStatement.SetIdentityColumn("portid");
}
else
{
oStatement.SetStatementType(SQLStatement::STUpdate);
String sWhere;
sWhere.Format(_T("portid = %I64d"), pObject->GetID());
oStatement.SetWhereClause(sWhere);
}
__int64 iAddress = 0;
IPAddressSQLHelper helper;
oStatement.AddColumn("portprotocol", pObject->GetProtocol());
oStatement.AddColumn("portnumber", pObject->GetPortNumber());
oStatement.AddColumnInt64("portsslcertificateid", pObject->GetSSLCertificateID());
helper.AppendStatement(oStatement, pObject->GetAddress(), "portaddress1", "portaddress2");
//JDR: Added to store the the UseSTARTLS setting.
if (pObject->GetUseSSL() == true)
{
oStatement.AddColumn("portusessl", 1);
}
else if ( pObject->GetUseSTARTTLS() == true)
{
oStatement.AddColumn("portusessl", 2);
}
else
{
oStatement.AddColumn("portusessl", 0);
}
//JDR: end edit
bool bNewObject = pObject->GetID() == 0;
// Save and fetch ID
__int64 iDBID = 0;
bool bRetVal = Application::Instance()->GetDBManager()->Execute(oStatement, bNewObject ? &iDBID : 0);
if (bRetVal && bNewObject)
pObject->SetID((int) iDBID);
return true;
}
bool
PersistentTCPIPPort::SaveObject(std::shared_ptr<TCPIPPort> pObject, String &errorMessage, PersistenceMode mode)
{
if (mode == PersistenceModeNormal)
{
if (pObject->GetSSLCertificateID() == 0 &&
(pObject->GetConnectionSecurity() == CSSSL || pObject->GetConnectionSecurity() == CSSTARTTLSOptional || pObject->GetConnectionSecurity() == CSSTARTTLSRequired))
{
errorMessage = "Certificate must be specified.";
return false;
}
}
SQLStatement oStatement;
oStatement.SetTable("hm_tcpipports");
if (pObject->GetID() == 0)
{
oStatement.SetStatementType(SQLStatement::STInsert);
oStatement.SetIdentityColumn("portid");
}
else
{
oStatement.SetStatementType(SQLStatement::STUpdate);
String sWhere;
sWhere.Format(_T("portid = %I64d"), pObject->GetID());
oStatement.SetWhereClause(sWhere);
}
__int64 iAddress = 0;
IPAddressSQLHelper helper;
oStatement.AddColumn("portprotocol", pObject->GetProtocol());
oStatement.AddColumn("portnumber", pObject->GetPortNumber());
oStatement.AddColumnInt64("portsslcertificateid", pObject->GetSSLCertificateID());
helper.AppendStatement(oStatement, pObject->GetAddress(), "portaddress1", "portaddress2");
oStatement.AddColumn("portconnectionsecurity", pObject->GetConnectionSecurity());
bool bNewObject = pObject->GetID() == 0;
// Save and fetch ID
__int64 iDBID = 0;
bool bRetVal = Application::Instance()->GetDBManager()->Execute(oStatement, bNewObject ? &iDBID : 0);
if (bRetVal && bNewObject)
pObject->SetID((int) iDBID);
return true;
}
bool
PersistentLogonFailure::AddFailure(const IPAddress &ipaddress)
{
SQLStatement statement;
IPAddressSQLHelper helper;
helper.AppendStatement(statement, ipaddress, "ipaddress1", "ipaddress2");
statement.AddColumnCommand("failuretime", SQLStatement::GetCurrentTimestamp());
statement.SetStatementType(SQLStatement::STInsert);
statement.SetTable("hm_logon_failures");
return Application::Instance()->GetDBManager()->Execute(statement);
}
bool
PersistentIncomingRelay::ReadObject(boost::shared_ptr<IncomingRelay> pSR, boost::shared_ptr<DALRecordset> pRS)
{
pSR->SetID(pRS->GetLongValue("relayid"));
IPAddressSQLHelper helper;
pSR->SetLowerIP(helper.Construct(pRS, "relaylowerip1", "relaylowerip2"));
pSR->SetUpperIP(helper.Construct(pRS, "relayupperip1", "relayupperip2"));
pSR->SetName(pRS->GetStringValue("relayname"));
return true;
}
bool
PersistentTCPIPPort::ReadObject(boost::shared_ptr<TCPIPPort> pObject, boost::shared_ptr<DALRecordset> pRS)
{
IPAddressSQLHelper helper;
pObject->SetID (pRS->GetLongValue("portid"));
pObject->SetProtocol((SessionType) pRS->GetLongValue("portprotocol"));
pObject->SetPortNumber(pRS->GetLongValue("portnumber"));
pObject->SetUseSSL(pRS->GetLongValue("portusessl") ? true : false);
pObject->SetAddress(helper.Construct(pRS, "portaddress1", "portaddress2"));
pObject->SetSSLCertificateID(pRS->GetLongValue("portsslcertificateid"));
return true;
}
bool
PersistentTCPIPPort::ReadObject(std::shared_ptr<TCPIPPort> pObject, std::shared_ptr<DALRecordset> pRS)
{
IPAddressSQLHelper helper;
pObject->SetID (pRS->GetLongValue("portid"));
pObject->SetProtocol((SessionType) pRS->GetLongValue("portprotocol"));
pObject->SetPortNumber(pRS->GetLongValue("portnumber"));
pObject->SetConnectionSecurity((ConnectionSecurity) pRS->GetLongValue("portconnectionsecurity"));
pObject->SetAddress(helper.Construct(pRS, "portaddress1", "portaddress2"));
pObject->SetSSLCertificateID(pRS->GetLongValue("portsslcertificateid"));
return true;
}
bool
PersistentLogonFailure::ClearFailuresByIP(const IPAddress &ipaddress)
{
IPAddressSQLHelper helper;
String whereClause;
whereClause.Format(_T("ipaddress1 %s and ipaddress2 %s"),
String(helper.GetAddress1Equals(ipaddress)),
String(helper.GetAddress2Equals(ipaddress)));
SQLStatement statement;
statement.SetStatementType(SQLStatement::STDelete);
statement.SetWhereClause(whereClause);
statement.SetTable("hm_logon_failures");
return Application::Instance()->GetDBManager()->Execute(statement);
}
bool
PersistentSecurityRange::ReadObject(shared_ptr<SecurityRange> pSR, shared_ptr<DALRecordset> pRS)
{
IPAddressSQLHelper helper;
pSR->SetID(pRS->GetLongValue("rangeid"));
pSR->SetPriority(pRS->GetLongValue("rangepriorityid"));
pSR->SetLowerIP(helper.Construct(pRS, "rangelowerip1", "rangelowerip2"));
pSR->SetUpperIP(helper.Construct(pRS, "rangeupperip1", "rangeupperip2"));
pSR->SetOptions(pRS->GetLongValue("rangeoptions"));
pSR->SetName(pRS->GetStringValue("rangename"));
pSR->SetExpires(pRS->GetLongValue("rangeexpires") == 1);
pSR->SetExpiresTime(Time::GetDateFromSystemDate(pRS->GetStringValue("rangeexpirestime")));
return true;
}
shared_ptr<GreyListTriplet>
PersistentGreyList::GetRecord(const String &sSenderAddress, const String &sRecipientAddress, const IPAddress & remoteIP)
//---------------------------------------------------------------------------()
// DESCRIPTION:
// Returns a grey list triple based on sender, recipient and IP address.
//---------------------------------------------------------------------------()
{
shared_ptr<GreyListTriplet> pTriplet;
IPAddressSQLHelper helper;
String sSQL;
sSQL.Format(_T("select * from hm_greylisting_triplets where glipaddress1 %s and glipaddress2 %s and glsenderaddress = @SENDERADDRESS and glrecipientaddress = @RECIPIENTADDRESS"),
String(helper.GetAddress1Equals(remoteIP)),
String(helper.GetAddress2Equals(remoteIP)));
SQLCommand command(sSQL);
command.AddParameter("@SENDERADDRESS", sSenderAddress);
command.AddParameter("@RECIPIENTADDRESS", sRecipientAddress);
shared_ptr<DALRecordset> pRS = Application::Instance()->GetDBManager()->OpenRecordset(command);
if (!pRS || pRS->IsEOF())
{
// Not found
return pTriplet;
}
// Read the record.
pTriplet = shared_ptr<GreyListTriplet>(new GreyListTriplet);
pTriplet->SetID(pRS->GetInt64Value("glid"));
pTriplet->SetCreateTime(pRS->GetStringValue("glcreatetime"));
pTriplet->SetBlockEndTime(pRS->GetStringValue("glblockendtime"));
pTriplet->SetDeleteTime(pRS->GetStringValue("gldeletetime"));
pTriplet->SetIPAddress(IPAddress(pRS->GetInt64Value("glipaddress1"), pRS->GetInt64Value("glipaddress2")));
pTriplet->SetSenderAddress(pRS->GetStringValue("glsenderaddress"));
pTriplet->SetRecipientAddress(pRS->GetStringValue("glrecipientaddress"));
pTriplet->SetPassedCount(pRS->GetLongValue("glpassedcount"));
pTriplet->SetBlockedCount(pRS->GetLongValue("glblockedcount"));
return pTriplet;
}
int
PersistentLogonFailure::GetCurrrentFailureCount(const IPAddress &ipaddress)
{
IPAddressSQLHelper helper;
String sql;
sql.Format(_T("select count(*) as c from hm_logon_failures where ipaddress1 %s and ipaddress2 %s"),
String(helper.GetAddress1Equals(ipaddress)),
String(helper.GetAddress2Equals(ipaddress)));
SQLCommand command(sql);
shared_ptr<DALRecordset> pRS = Application::Instance()->GetDBManager()->OpenRecordset(command);
if (!pRS)
return 0;
long count = pRS->GetLongValue("c");
return count;
}
bool
PersistentIncomingRelay::SaveObject(boost::shared_ptr<IncomingRelay> pSR)
{
SQLStatement oStatement;
oStatement.SetTable("hm_incoming_relays");
oStatement.AddColumn("relayname", pSR->GetName());
IPAddressSQLHelper helper;
helper.AppendStatement(oStatement, pSR->GetLowerIP(), "relaylowerip1", "relaylowerip2");
helper.AppendStatement(oStatement, pSR->GetUpperIP(), "relayupperip1", "relayupperip2");
if (pSR->GetID() == 0)
{
oStatement.SetStatementType(SQLStatement::STInsert);
oStatement.SetIdentityColumn("relayid");
}
else
{
oStatement.SetStatementType(SQLStatement::STUpdate);
String sWhere;
sWhere.Format(_T("relayid = %d"), (unsigned int) pSR->GetID());
oStatement.SetWhereClause(sWhere);
}
bool bNewObject = pSR->GetID() == 0;
// Save and fetch ID
__int64 iDBID = 0;
bool bRetVal = Application::Instance()->GetDBManager()->Execute(oStatement, bNewObject ? &iDBID : 0);
if (bRetVal && bNewObject)
pSR->SetID((int) iDBID);
return bRetVal;
}