Exemplo n.º 1
0
 /// <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);
 }
Exemplo n.º 2
0
        /// <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));
                }
            }
        }
Exemplo n.º 3
0
        /// <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);
        }
Exemplo n.º 4
0
 /// <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);
 }
Exemplo n.º 5
0
 /// <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);
 }
Exemplo n.º 6
0
 /// <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);
 }
Exemplo n.º 7
0
 /// <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);
 }
Exemplo n.º 8
0
        /// <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);
        }
Exemplo n.º 9
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);
 }
Exemplo n.º 10
0
        /// <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);
        }
Exemplo n.º 11
0
        /// <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);
                }
            }
        }