From 604fed77fb7a75bb39cda96b2f06438ce71ee86a Mon Sep 17 00:00:00 2001
From: user <user@node5.net>
Date: Fri, 22 Mar 2024 15:00:01 +0100
Subject: password of the month

---
 src/db_handler.py         | 18 +++++++++++++++++-
 src/ssh_login_attempts.py |  7 +++++++
 src/templates/index.html  |  3 +++
 3 files changed, 27 insertions(+), 1 deletion(-)

diff --git a/src/db_handler.py b/src/db_handler.py
index 576612a..7766da1 100644
--- a/src/db_handler.py
+++ b/src/db_handler.py
@@ -33,8 +33,24 @@ def get_top(column: str) -> (list[dict], list[str]):
                 GROUP BY {column}
                 ORDER BY COUNT({column}) DESC 
                 LIMIT 20;
-                """).format(column=psycopg.sql.Identifier(column),))
+                """).format(column=psycopg.sql.Identifier(column), ))
 
             top_usernames = cur.fetchall()
             col_names = [desc[0] for desc in cur.description]
             return top_usernames, col_names
+
+
+def get_password_of_the_month() -> str:
+    with psycopg.connect(**db_con_params, row_factory=psycopg.rows.dict_row) as conn:
+        with conn.cursor() as cur:
+            cur.execute("""
+SELECT password
+FROM login_attempt
+WHERE timestamp BETWEEN current_timestamp - interval '1 month' AND timestamp
+GROUP BY password
+ORDER BY COUNT(password) DESC
+LIMIT 1;
+                """)
+
+            password = cur.fetchone()['password']
+            return password
diff --git a/src/ssh_login_attempts.py b/src/ssh_login_attempts.py
index cb5f477..3da31cc 100644
--- a/src/ssh_login_attempts.py
+++ b/src/ssh_login_attempts.py
@@ -37,6 +37,13 @@ logger.root.addHandler(stream_handler)
 
 app = flask.Flask(__name__, template_folder='templates', static_folder='static', static_url_path='')
 
+@app.route("/password_of_the_month")
+@app.route("/password of the month")
+@app.route("/passwd")
+def password_of_the_month():
+    password = db_handler.get_password_of_the_month()
+    return password
+
 
 @app.route("/")
 def index():
diff --git a/src/templates/index.html b/src/templates/index.html
index 2639cb7..d90be7c 100644
--- a/src/templates/index.html
+++ b/src/templates/index.html
@@ -43,6 +43,9 @@
             {% endwith %}
         </div>
     </div>
+
+    <p>Do you like living life dangerously? use <a href="/password_of_the_month">password of the month</a>
+        as your next password</p>
 </div>
 
 </body>
-- 
cgit v1.2.3