edb-pgsql: Added prepared SQL statements needed for the admin authentication

Aslo moved one query from the "plugin section" to the "admin section" where
it belongs.

Signed-off-by: David Sommerseth <dazo@users.sourceforge.net>

2 files changed, 107 insertions, 9 deletions

diff --git a/database/postgresql/prepared-sql.c b/database/postgresql/prepared-sql.c
index c33fe3a..7697478 100644
--- a/database/postgresql/prepared-sql.c
+++ b/database/postgresql/prepared-sql.c
@@ -214,13 +214,6 @@ static const _ePGprepStatements_t _ePGprepStatementsPlugin[] = {
          "       AND dataval = $1::VARCHAR"
         },
 
-        {.prepid = PREPSQL_SESSIONKEY_UNIQ_ADMIN,
-         .name = "eurephia_sessionkey_uniq_admin", .numargs = 1,
-         .sql =
-         "SELECT count(sessionkey) = 0 "
-         "FROM eurephia_adminlog WHERE sessionkey = $1::VARCHAR"
-        },
-
         {.prepid = PREPSQL_SESSIONKEY_UNIQ_PLAUTH,
          .name = "eurephia_sessionkey_uniq_plauth", .numargs = 1,
          .sql =
@@ -339,6 +332,109 @@ static const _ePGprepStatements_t _ePGprepStatementsPlugin[] = {
 
 
 static const _ePGprepStatements_t _ePGprepStatementsAdmin[] = {
+
+        {.prepid = PREPSQL_SESSIONVARS_LOAD,
+         .name = "eurephia_sessionvars_load", .numargs = 1,
+         .sql =
+         "SELECT datakey, dataval"
+         "  FROM sessions"
+         " WHERE sessionkey = $1::VARCHAR"
+        },
+
+        {.prepid = PREPSQL_SESSIONVARS_STORE_NEW,
+         .name = "eurephia_sessionvars_store_new", .numargs = 3,
+         .sql =
+         "INSERT INTO sessions (sessionkey, datakey, dataval) "
+         "VALUES ($1::VARCHAR,$2::VARCHAR,$3::VARCHAR)"
+        },
+
+        {.prepid = PREPSQL_SESSIONVARS_STORE_UPDATE,
+         .name = "eurephia_sessionvars_store_upd", .numargs = 3,
+         .sql =
+        "UPDATE sessions SET dataval = $3::VARCHAR "
+         " WHERE sessionkey = $1::VARCHAR"
+         "       AND datakey = $2::VARCHAR"
+        },
+
+        {.prepid = PREPSQL_SESSIONVARS_STORE_DELETE,
+         .name = "eurephia_sessionvars_store_del", .numargs = 2,
+         .sql =
+         "DELETE FROM sessions "
+         " WHERE sessionkey = $1::VARCHAR"
+         "       AND datakey = $2::VARCHAR"
+        },
+
+        {.prepid = PREPSQL_SESSIONS_DESTROY_SESS,
+         .name = "eurephia_sessions_destr_sessions", .numargs = 1,
+         .sql = "DELETE FROM sessions WHERE sessionkey = $1::VARCHAR"
+        },
+
+
+        {.prepid = PREPSQL_ADMIN_AUTHUSER,
+         .name = "eurephiadm_authuser", .numargs = 1,
+         .sql =
+         "SELECT activated, deactivated, bl.blid, "
+         "       password, uid "
+         "  FROM users ou"
+         "  LEFT JOIN blacklist bl USING (username)"
+         " WHERE ou.username = $1::VARCHAR"
+        },
+
+        {.prepid = PREPSQL_ADMIN_AUTHSESS,
+         .name = "eurephiadm_authsess", .numargs = 4,
+         .sql =
+         "SELECT (CURRENT_TIMESTAMP - last_action) > ($1::VARCHAR || ' minutes')::INTERVAL AS exp,"
+         "       (access IS NOT NULL) AS access"
+         "  FROM eurephia_adminlog"
+         "  LEFT JOIN eurephia_adminaccess USING(uid,interface)"
+         " WHERE status IN (1,2)"
+         "       AND sessionkey = $2::VARCHAR"
+         "       AND access = $3::VARCHAR"
+         "       AND interface = $4::CHAR(1)",
+        },
+
+        {.prepid = PREPSQL_ADMIN_ADMLOG_LASTACT,
+         .name = "eurephiadm_adminlog_lastaction_upd", .numargs = 1,
+         .sql =
+         "UPDATE eurephia_adminlog_update"
+         "   SET last_action = CURRENT_TIMESTAMP, status = 2"
+         " WHERE sessionkey = $1"
+        },
+
+        {.prepid = PREPSQL_ADMIN_ADMLOG_LOGOUT,
+         .name = "eurephiadm_adminlog_logoutstatus", .numargs = 2,
+         .sql =
+         "UPDATE eurephia_adminlog_update"
+         "   SET logout = CURRENT_TIMESTAMP, status = $1"
+         " WHERE sessionkey = $2"
+        },
+
+        {.prepid = PREPSQL_ADMIN_REGLOGIN,
+         .name = "eurephiadm_reglogin", .numargs = 3,
+         .sql =
+         "INSERT INTO eurephia_adminlog "
+         "       (uid,interface,status,login,last_action,sessionkey) "
+         "VALUES ($1,$2,1,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,$3)",
+        },
+
+        {.prepid = PREPSQL_ADMIN_REGLOGOUT,
+         .name = "eurephiadm_reglogout", .numargs = 0, .sql = ""},
+
+        {.prepid = PREPSQL_ADMIN_CHECKACL,
+         .name = "eurephiadm_checkacl", .numargs = 3,
+         .sql =
+         "SELECT (count(*) = 1) AS access "
+         "  FROM eurephia_adminaccess"
+         " WHERE uid = $1 AND interface = $2 AND access = $3"},
+
+        {.prepid = PREPSQL_SESSIONKEY_UNIQ_ADMIN,
+         .name = "eurephia_sessionkey_uniq_admin", .numargs = 1,
+         .sql =
+         "SELECT count(sessionkey) = 0 "
+         "FROM eurephia_adminlog WHERE sessionkey = $1::VARCHAR"
+        },
+
+
         {.prepid = PREPSQL_NONE,
          .name = NULL, .numargs = 0, .sql = ""}
 };
@@ -422,7 +518,6 @@ const char const * ePGprepStatementGetName(eurephiaCTX *ctx, ePG_prepID prepid)
  *
  *  @return Returns 1 on success, otherwise 0.
  */
-
 static int _ePGprepDoStatementLoad(eurephiaCTX *ctx, const _ePGprepStatements_t *prep) {
         const _ePGprepStatements_t * ptr = NULL;
 
diff --git a/database/postgresql/prepared-sql.h b/database/postgresql/prepared-sql.h
index c8664db..f5325b6 100644
--- a/database/postgresql/prepared-sql.h
+++ b/database/postgresql/prepared-sql.h
@@ -52,7 +52,10 @@ typedef enum { PREPSQL_NONE = 0, PREPSQL_TLS_AUTH, PREPSQL_USERPWD_AUTH,
                PREPSQL_SESSIONS_DESTROY_LASTLOG, PREPSQL_SESSIONS_DESTROY_SESS,
                PREPSQL_REGISTER_LOGIN, PREPSQL_REGISTER_LOGOUT,
                PREPSQL_MACHISTORY_REGISTER, PREPSQL_MACHISTORY_LASTLOG,
-               PREPSQL_FIREWALL_GETPROFILE
+               PREPSQL_FIREWALL_GETPROFILE,
+               PREPSQL_ADMIN_AUTHUSER, PREPSQL_ADMIN_AUTHSESS,
+               PREPSQL_ADMIN_REGLOGIN, PREPSQL_ADMIN_REGLOGOUT, PREPSQL_ADMIN_CHECKACL,
+               PREPSQL_ADMIN_ADMLOG_LASTACT, PREPSQL_ADMIN_ADMLOG_LOGOUT
 } ePG_prepID;
 
 typedef struct _ePGprepParam_s {