/// <summary>
/// Get the ban date and ban end date for an ip address
/// </summary>
/// <param name="ipAddress">IP address</param>
/// <param name="banDates">Ban dates, default if not found</param>
/// <param name="transaction">Transaction</param>
/// <returns>Ban date. Key and/or value will ber null if not banned or not in the database</returns>
public bool TryGetBanDates(string ipAddress, out KeyValuePair <DateTime?, DateTime?> banDates, object transaction = null)
{
if (IPAddress.TryParse(ipAddress, out IPAddress ipAddressObj))
{
byte[] ipBytes = ipAddressObj.GetAddressBytes();
SqliteDBTransaction tran = transaction as SqliteDBTransaction;
using (SqliteDataReader reader = ExecuteReader("SELECT BanDate, BanEndDate FROM IPAddresses WHERE IPAddress = @Param0", tran?.DBConnection, tran?.DBTransaction, ipBytes))
{
if (reader.Read())
{
DateTime?banDate = null;
DateTime?banEndDate = null;
object val = reader.GetValue(0);
object val2 = reader.GetValue(1);
if (val != null && val != DBNull.Value)
{
banDate = ((long)val).ToDateTimeUnixMilliseconds();
}
if (val2 != null && val2 != DBNull.Value)
{
banEndDate = ((long)val2).ToDateTimeUnixMilliseconds();
}
banDates = new KeyValuePair <DateTime?, DateTime?>(banDate, banEndDate);
return(true);
}
}
}
banDates = new KeyValuePair <DateTime?, DateTime?>(null, null);
return(false);
}
/// <summary>
/// Get all ip addresses
/// </summary>
/// <param name="failLoginCutOff">Fail login cut off, only return entries with last failed login before this timestamp, null to not query this</param>
/// <param name="banCutOff">Ban cut off date, only return entries with ban end date less than or equal to this, null to not query this</param>
/// <param name="transaction">Transaction</param>
/// <returns>IP addresses that match the query</returns>
public IEnumerable <IPAddressEntry> EnumerateIPAddresses(DateTime?failLoginCutOff = null, DateTime?banCutOff = null, object transaction = null)
{
long?failLoginCutOffUnix = null;
long?banCutOffUnix = null;
if (failLoginCutOff != null)
{
failLoginCutOffUnix = failLoginCutOff.Value.ToUnixMillisecondsLong();
}
if (banCutOff != null)
{
banCutOffUnix = banCutOff.Value.ToUnixMillisecondsLong();
}
SqliteDBTransaction tran = transaction as SqliteDBTransaction;
using (SqliteDataReader reader = ExecuteReader(@"SELECT IPAddressText, LastFailedLogin, FailedLoginCount, BanDate, State, BanEndDate, UserName, Source
FROM IPAddresses
WHERE (@Param0 IS NULL AND @Param1 IS NULL) OR (@Param0 IS NOT NULL AND State = 3 AND LastFailedLogin <= @Param0) OR (@Param1 IS NOT NULL AND State IN (0, 1) AND BanEndDate <= @Param1)
ORDER BY IPAddress",
tran?.DBConnection, tran?.DBTransaction, failLoginCutOffUnix, banCutOffUnix))
{
while (reader.Read())
{
yield return(ParseIPAddressEntry(reader));
}
}
}
/// <summary>
/// Set banned ip addresses. If the ip address is not in the database, it will be added,
/// otherwise it will be updated with the ban date if the existing ban date is expired.
/// </summary>
/// <param name="ipAddresses">IP addresses, ban date and ban end dates to set as banned</param>
/// <param name="now">Current date/time</param>
/// <param name="transaction">Transaction</param>
/// <returns>Count of newly banned ip addresses</returns>
public int SetBannedIPAddresses(IEnumerable <Tuple <string, DateTime, DateTime> > ipAddresses, DateTime now, object transaction = null)
{
int count = 0;
SqliteDBTransaction tran = transaction as SqliteDBTransaction;
bool commit = (tran is null);
tran = (tran ?? BeginTransaction() as SqliteDBTransaction);
try
{
foreach (Tuple <string, DateTime, DateTime> ipAddress in ipAddresses)
{
if (IPAddress.TryParse(ipAddress.Item1, out IPAddress ipAddressObj))
{
count += SetBanDateInternal(ipAddressObj, ipAddress.Item2, ipAddress.Item3, now, tran.DBConnection, tran.DBTransaction);
}
}
}
catch
{
if (commit)
{
commit = false;
RollbackTransaction(tran);
}
throw;
}
finally
{
if (commit)
{
CommitTransaction(tran);
}
}
return(count);
}
/// <summary>
/// Delete an ip address from the database
/// </summary>
/// <param name="ipAddress">IP address to delete</param>
/// <param name="transaction">Transaction</param>
/// <returns>True if deleted, false if not exists</returns>
public bool DeleteIPAddress(string ipAddress, object transaction = null)
{
if (IPAddress.TryParse(ipAddress, out IPAddress ipAddressObj))
{
SqliteDBTransaction ipDBTransaction = transaction as SqliteDBTransaction;
byte[] ipBytes = ipAddressObj.GetAddressBytes();
return(ExecuteNonQuery("DELETE FROM IPAddresses WHERE IPAddress = @Param0", ipDBTransaction?.DBConnection, ipDBTransaction?.DBTransaction, ipBytes) != 0);
}
return(false);
}
/// <summary>
/// Set ban date for an ip address. If the ip address exists, the ban date will be set only if the existing ban date is expired.
/// </summary>
/// <param name="ipAddress">IP address</param>
/// <param name="banStartDate">Ban start date</param>
/// <param name="banEndDate">Ban end date</param>
/// <param name="now">Current date/time</param>
/// <param name="state">State</param>
/// <param name="transaction">Transaction</param>
/// <returns>True if ban date set, false if it was already set or ip address is not in the database</returns>
public bool SetBanDates(string ipAddress, DateTime banStartDate, DateTime banEndDate, DateTime now, object transaction = null)
{
if (IPAddress.TryParse(ipAddress, out IPAddress ipAddressObj))
{
SqliteDBTransaction tran = transaction as SqliteDBTransaction;
int count = SetBanDateInternal(ipAddressObj, banStartDate, banEndDate, now, tran?.DBConnection, tran?.DBTransaction);
return(count != 0);
}
return(false);
}
/// <summary>
/// Get an ip address state
/// </summary>
/// <param name="ipAddress">IP address</param>
/// <param name="state">Receives ip address state or default if not found</param>
/// <param name="transaction">Transaction</param>
/// <returns>True if ip address found, false otherwise</returns>
public bool TryGetIPAddressState(string ipAddress, out IPAddressState state, object transaction = null)
{
if (IPAddress.TryParse(ipAddress, out IPAddress ipAddressObj))
{
SqliteDBTransaction tran = transaction as SqliteDBTransaction;
byte[] ipBytes = ipAddressObj.GetAddressBytes();
state = (IPAddressState)ExecuteScalar <int>("SELECT State FROM IPAddresses WHERE IPAddress = @Param0", tran?.DBConnection, tran?.DBTransaction, ipBytes);
return(true);
}
state = IPAddressState.Active;
return(false);
}
/// <summary>
/// Get an ip address state
/// </summary>
/// <param name="ipAddress">IP address</param>
/// <param name="state">Receives ip address state or default if not found</param>
/// <param name="transaction">Transaction</param>
/// <returns>True if ip address found, false otherwise</returns>
public bool TryGetIPAddressState(string ipAddress, out IPAddressState?state, object transaction = null)
{
if (IPAddress.TryParse(ipAddress, out IPAddress ipAddressObj))
{
ipAddressObj = ipAddressObj.Clean();
SqliteDBTransaction tran = transaction as SqliteDBTransaction;
byte[] ipBytes = ipAddressObj.GetAddressBytes();
if (ExecuteScalar <int>("SELECT State FROM IPAddresses WHERE IPAddress = @Param0",
tran?.DBConnection, tran?.DBTransaction, out int stateInt, ipBytes))
{
state = (IPAddressState)stateInt;
return(true);
}
}
state = null;
return(false);
}
/// <summary>
/// Increment the failed login count for an ip address
/// </summary>
/// <param name="ipAddress">IP address</param>
/// <param name="dateTime">DateTime to set for failed login</param>
/// <param name="increment">Amount to increment</param>
/// <param name="transaction">Transaction</param>
/// <returns>New failed login count</returns>
public int IncrementFailedLoginCount(string ipAddress, DateTime dateTime, int increment, object transaction = null)
{
if (IPAddress.TryParse(ipAddress, out IPAddress ipAddressObj))
{
byte[] ipBytes = ipAddressObj.GetAddressBytes();
long timestamp = dateTime.ToUnixMillisecondsLong();
SqliteDBTransaction tran = transaction as SqliteDBTransaction;
// only increment failed login for new rows or for existing rows with state 3 (failed login only, no ban bending)
string command = @"INSERT INTO IPAddresses(IPAddress, IPAddressText, LastFailedLogin, FailedLoginCount, BanDate, State, BanEndDate)
VALUES (@Param0, @Param1, @Param2, @Param3, NULL, 3, NULL)
ON CONFLICT(IPAddress)
DO UPDATE SET LastFailedLogin = @Param2, FailedLoginCount = FailedLoginCount + @Param3 WHERE State = 3;
SELECT FailedLoginCount FROM IPAddresses WHERE IPAddress = @Param0;";
return(ExecuteScalar <int>(command, tran?.DBConnection, tran?.DBTransaction, ipBytes, ipAddress, timestamp, increment));
}
return(0);
}
/// <summary>
/// Get ip address entry from the database
/// </summary>
/// <param name="ipAddress">IP address to lookup</param>
/// <param name="entry">IP address entry or default if not found</param>
/// <returns>True if ip address found, false if not</returns>
public bool TryGetIPAddress(string ipAddress, out IPAddressEntry entry, object transaction = null)
{
if (IPAddress.TryParse(ipAddress, out IPAddress ipAddressObj))
{
byte[] ipBytes = ipAddressObj.GetAddressBytes();
SqliteDBTransaction tran = transaction as SqliteDBTransaction;
using (SqliteDataReader reader = ExecuteReader("SELECT IPAddressText, LastFailedLogin, FailedLoginCount, BanDate, State, BanEndDate FROM IPAddresses WHERE IPAddress = @Param0",
tran?.DBConnection, tran?.DBTransaction, ipBytes))
{
if (reader.Read())
{
entry = ParseIPAddressEntry(reader);
return(true);
}
}
}
entry = null;
return(false);
}
/// <summary>
/// Set state of ip addresses
/// </summary>
/// <param name="ipAddresses">IP addresses to set state for. Pass null to set the entire database.</param>
/// <param name="state">State to set</param>
/// <param name="transaction">Transaction</param>
/// <returns>Number of rows affected</returns>
public int SetIPAddressesState(IEnumerable <string> ipAddresses, IPAddressState state, object transaction = null)
{
if (ipAddresses is null)
{
return(0);
}
int count = 0;
int stateInt = (int)state;
SqliteDBTransaction tran = transaction as SqliteDBTransaction;
bool commit = (transaction is null);
tran ??= BeginTransaction() as SqliteDBTransaction;
try
{
foreach (string ipAddress in ipAddresses)
{
if (IPAddress.TryParse(ipAddress, out IPAddress ipAddressObj))
{
ipAddressObj = ipAddressObj.Clean();
byte[] ipBytes = ipAddressObj.GetAddressBytes();
count += ExecuteNonQuery("UPDATE IPAddresses SET State = @Param0 WHERE IPAddress = @Param1", tran.DBConnection, tran.DBTransaction, stateInt, ipBytes);
}
}
}
catch
{
if (commit)
{
commit = false;
RollbackTransaction(tran);
}
throw;
}
finally
{
if (commit)
{
CommitTransaction(tran);
}
}
return(count);
}
/// <summary>
/// Enumerate any pending add or remove operations. When enumeration is complete, any returned ip addresses are either deleted (remove state), set to active (add state)
/// or set to failed login state (ban expired set as failed login).
/// </summary>
/// <param name="commit">Whether to commit changes (alter states and delete pending removals) when enumeration is complete</param>
/// <param name="now">Current date/time</param>
/// <param name="resetFailedLoginCount">Whether to reset failed login count to 0 for un-banned ip addresses</param>
/// <param name="transaction">Transaction</param>
/// <returns></returns>
public IEnumerable <IPBanFirewallIPAddressDelta> EnumerateIPAddressesDeltaAndUpdateState(bool commit, DateTime now, bool resetFailedLoginCount = true, object transaction = null)
{
string ipAddress;
bool added;
SqliteDBTransaction tran = transaction as SqliteDBTransaction;
bool dispose = (tran is null);
tran = (tran ?? BeginTransaction() as SqliteDBTransaction);
SqliteDataReader reader;
// C# can't yield inside a try/catch, so we have to split it up
try
{
// select ip in add pending, remove pending, or remove pending become failed login state
reader = ExecuteReader("SELECT IPAddressText, State FROM IPAddresses WHERE State IN (1, 2, 4) ORDER BY IPAddressText", tran.DBConnection, tran.DBTransaction);
}
catch
{
RollbackTransaction(tran);
throw;
}
while (true)
{
try
{
if (!reader.Read())
{
break;
}
ipAddress = reader.GetString(0);
added = (reader.GetInt32(1) == (int)IPAddressState.AddPending);
}
catch
{
RollbackTransaction(tran);
throw;
}
// if add pending, this is an add, otherwise it is a remove
yield return(new IPBanFirewallIPAddressDelta {
IPAddress = ipAddress, Added = added
});
}
try
{
if (commit)
{
// add pending (1) becomes active (0)
// remove pending no delete (4) becomes failed login (3)
// remove pending (2) is deleted entirely
// last failed login is set to current date/time if state goes from 4 to 3
long timestamp = now.ToUnixMillisecondsLong();
ExecuteNonQuery(@"UPDATE IPAddresses SET FailedLoginCount = CASE WHEN @Param0 = 1 THEN 0 ELSE FailedLoginCount END,
LastFailedLogin = CASE WHEN State = 4 THEN @Param1 ELSE LastFailedLogin END,
State = CASE WHEN State = 1 THEN 0 WHEN State = 4 THEN 3 ELSE State END WHERE State IN (1, 4);
DELETE FROM IPAddresses WHERE State = 2;", tran.DBConnection, tran.DBTransaction, resetFailedLoginCount, timestamp);
}
}
catch
{
if (commit)
{
commit = dispose = false;
RollbackTransaction(tran);
}
throw;
}
finally
{
if (commit)
{
CommitTransaction(tran);
}
else if (dispose)
{
RollbackTransaction(tran);
}
}
}