/// <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 SqliteDataReaderWrapper 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, false, failLoginCutOffUnix, banCutOffUnix);
while (reader.Reader.Read())
{
yield return(ParseIPAddressEntry(reader.Reader));
}
}
/// <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))
{
ipAddressObj = ipAddressObj.Clean();
byte[] ipBytes = ipAddressObj.GetAddressBytes();
SqliteDBTransaction tran = transaction as SqliteDBTransaction;
using SqliteDataReaderWrapper reader = ExecuteReader("SELECT BanDate, BanEndDate FROM IPAddresses WHERE IPAddress = @Param0", tran?.DBConnection, tran?.DBTransaction, false, ipBytes);
if (reader.Reader.Read())
{
DateTime?banDate = null;
DateTime?banEndDate = null;
object val = reader.Reader.GetValue(0);
object val2 = reader.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 banned ip addresses
/// </summary>
/// <returns>IP addresses with non-null ban dates</returns>
public IEnumerable <string> EnumerateBannedIPAddresses()
{
using SqliteDataReaderWrapper reader = ExecuteReader("SELECT IPAddressText FROM IPAddresses WHERE BanDate IS NOT NULL AND State = 0 ORDER BY IPAddress", null, null, false);
while (reader.Reader.Read())
{
yield return(reader.Reader.GetString(0));// ParseIPAddressEntry(reader);
}
}
/// <summary>
/// Delete all ip addresses in the specified range
/// </summary>
/// <param name="range">Range</param>
/// <returns>List of deleted ip</returns>
public IEnumerable <string> DeleteIPAddresses(IPAddressRange range)
{
byte[] start = range.Begin.GetAddressBytes();
byte[] end = range.End.GetAddressBytes();
using SqliteDataReaderWrapper reader = ExecuteReader("SELECT IPAddressText FROM IPAddresses WHERE IPAddress BETWEEN @Param0 AND @Param1 AND length(IPAddress) = length(@Param0) AND length(IPAddress) = length(@Param1); " +
"DELETE FROM IPAddresses WHERE IPAddress BETWEEN @Param0 AND @Param1 AND length(IPAddress) = length(@Param0) AND length(IPAddress) = length(@Param1);", null, null, false,
start, end);
while (reader.Reader.Read())
{
yield return(reader.Reader.GetString(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))
{
ipAddressObj = ipAddressObj.Clean();
byte[] ipBytes = ipAddressObj.GetAddressBytes();
SqliteDBTransaction tran = transaction as SqliteDBTransaction;
using SqliteDataReaderWrapper reader = ExecuteReader("SELECT IPAddressText, LastFailedLogin, FailedLoginCount, BanDate," +
"State, BanEndDate, UserName, Source FROM IPAddresses WHERE IPAddress = @Param0",
tran?.DBConnection, tran?.DBTransaction, false, ipBytes);
if (reader.Reader.Read())
{
entry = ParseIPAddressEntry(reader.Reader);
return(true);
}
}
entry = null;
return(false);
}
/// <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 ??= BeginTransaction() as SqliteDBTransaction;
using SqliteDataReaderWrapper reader = ExecuteReader("SELECT IPAddressText, State FROM IPAddresses WHERE State IN (1, 2, 4) ORDER BY IPAddressText", tran.DBConnection, tran.DBTransaction, dispose);
while (true)
{
try
{
if (!reader.Reader.Read())
{
break;
}
ipAddress = reader.Reader.GetString(0);
added = (reader.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);
}
}
}
