void pgsql_update_lease(const uint8_t* mac, const struct in_addr* yip, const char* ifname, const uint32_t expiresAt, const enum t_lease_update_src reason)
{
/* only write DHCP ACK packet changes back */
if (reason != UPDATED_LEASE_FROM_DHCP)
return;
/* the pgsql commands are both run always, as the initial entry might have been created on another device. */
/* though, we restrict ACKs to be received on APs that saw the request - no roaming between REQ/ACK */
/* add to pgsql */
if (!pgsql_connected())
return;
const uint32_t now = reltime();
eprintf(DEBUG_VERBOSE, "sql: update lease: MAC: %s IP: %s VLAN: %s expiresIn: %d", ether_ntoa_z((struct ether_addr *)mac), inet_ntoa(*yip), ifname, expiresAt - now);
char *sql_esc_bridge = PQescapeLiteral(pgsql, ifname, strlen(ifname));
if (!sql_esc_bridge) return;
char sql[2048];
if (expiresAt > now) {
snprintf(sql, sizeof(sql), "INSERT INTO " PGSQLLEASETABLE " (bridge, mac, ip, validUntil) VALUES(%s, '%s', '%s', CURRENT_TIMESTAMP + interval '%d seconds') ON CONFLICT (bridge, mac, ip) DO UPDATE SET validUntil = CURRENT_TIMESTAMP + interval '%d seconds';", sql_esc_bridge, ether_ntoa_z((struct ether_addr *)mac), inet_ntoa(*yip), expiresAt - now, expiresAt - now);
} else {
snprintf(sql, sizeof(sql), "UPDATE " PGSQLLEASETABLE " SET validUntil = CURRENT_TIMESTAMP WHERE bridge = %s AND mac = '%s';", sql_esc_bridge, ether_ntoa_z((struct ether_addr *)mac));
}
PQfreemem(sql_esc_bridge); sql_esc_bridge = NULL;
eprintf(DEBUG_GENERAL, "write sql: %s", sql);
pgsql_query_errprint(sql);
}
char *postgresql_util_escape_literal(postgresql_conn_t *conn, const char *str,
size_t length)
{
char *escaped = PQescapeLiteral(conn->conn, str, length);
if (!escaped) {
msg->err("[FD %i] PostgreSQL Escape Literal Error: %s", PQerrorMessage(conn->conn));
}
return escaped;
}
char * pq_escape (PGconn* cxn, const char* input, int len)
{
char *output;
output = PQescapeLiteral(cxn, input, len);
if (output == NULL)
LOGSTDERR(ERROR, PQresStatus(PGRES_FATAL_ERROR),
"Failed to escape string: %s", input);
// free output in caller
return(output);
}
/*
* Variable-fetching callback for flex lexer
*
* If the specified variable exists, return its value as a string (malloc'd
* and expected to be freed by the caller); else return NULL.
*
* If "escape" is true, return the value suitably quoted and escaped,
* as an identifier or string literal depending on "as_ident".
* (Failure in escaping should lead to returning NULL.)
*/
char *
psql_get_variable(const char *varname, bool escape, bool as_ident)
{
char *result;
const char *value;
value = GetVariable(pset.vars, varname);
if (!value)
return NULL;
if (escape)
{
char *escaped_value;
if (!pset.db)
{
psql_error("cannot escape without active connection\n");
return NULL;
}
if (as_ident)
escaped_value =
PQescapeIdentifier(pset.db, value, strlen(value));
else
escaped_value =
PQescapeLiteral(pset.db, value, strlen(value));
if (escaped_value == NULL)
{
const char *error = PQerrorMessage(pset.db);
psql_error("%s", error);
return NULL;
}
/*
* Rather than complicate the lexer's API with a notion of which
* free() routine to use, just pay the price of an extra strdup().
*/
result = pg_strdup(escaped_value);
PQfreemem(escaped_value);
}
else
result = pg_strdup(value);
return result;
}
void pgsql_iterate_lease_for_ifname_and_mac(const char* ifname, const uint8_t* mac, lease_cb cb)
{
/* query sql for lease and add local rules*/
PGresult * res;
char sql[1024];
char *sql_esc_bridge;
const uint32_t now = reltime();
if (!pgsql_connected())
return;
eprintf(DEBUG_NEIGH, "query pgsql\n");
sql_esc_bridge = PQescapeLiteral(pgsql, ifname, strlen(ifname));
snprintf(sql, sizeof(sql), "SELECT ip::varchar as ip, ceil(extract('epoch' from MAX(validUntil) - CURRENT_TIMESTAMP))::varchar as expiresin FROM " PGSQLLEASETABLE " WHERE validUntil > CURRENT_TIMESTAMP AND bridge = %s AND mac = '%s' GROUP BY ip;", sql_esc_bridge, ether_ntoa_z((struct ether_addr *)mac));
PQfreemem(sql_esc_bridge); sql_esc_bridge = NULL;
eprintf(DEBUG_NEIGH, "query: %s", sql);
res = pgsql_query_errprint_query(sql);
if (res == NULL)
goto out;
/* pgsql query sucessfull */
int colIp = PQfnumber(res, "ip");
int colExpiresIn = PQfnumber(res, "expiresin");
for (int row = 0; row < PQntuples(res); row++) {
char *ip = PQgetvalue(res, row, colIp);
char *expiresIn = PQgetvalue(res, row, colExpiresIn);
eprintf(DEBUG_NEIGH, "query pgsql: got row ip = %s, expiresAt = %s", ip ? ip : "NULL", expiresIn ? expiresIn : "NULL");
if (!ip || !expiresIn)
continue;
struct in_addr yip;
if (!inet_aton(ip, &yip)) {
eprintf(DEBUG_NEIGH, "cannot parse ip");
continue;
}
uint32_t expiresAt = atoi(expiresIn) + now;
cb (mac, &yip, ifname, expiresAt, UPDATED_LEASE_FROM_EXTERNAL);
}
PQclear(res); res = NULL;
out:
eprintf(DEBUG_NEIGH, "pgsql completed");
}
static SCM pg_format_sql(SCM conn, SCM obj) {
struct pg_conn *pgc;
SCM out;
if (SCM_SMOB_PREDICATE(time_tag, obj)) {
out = format_time(obj, c2s("'%Y-%m-%d %H:%M:%S'"));
}
else if (scm_boolean_p(obj) == SCM_BOOL_T) {
if (scm_is_true(obj)) out = c2s("'t'");
else out = c2s("'f'");
}
else if (scm_is_number(obj)) {
out = scm_number_to_string(obj,
scm_from_signed_integer(10));
}
else if (scm_is_symbol(obj)) {
out = pg_format_sql(conn, scm_symbol_to_string(obj));
}
else if (scm_is_string(obj)) {
if (scm_string_null_p(obj) == SCM_BOOL_T) out = c2s("NULL");
else {
char *src = scm_to_utf8_string(obj);
scm_assert_smob_type(pg_conn_tag, conn);
pgc = (struct pg_conn *)SCM_SMOB_DATA(conn);
scm_lock_mutex(pgc->mutex);
char *sql = PQescapeLiteral(pgc->conn,
src, strlen(src));
out = safe_from_utf8(sql);
scm_unlock_mutex(pgc->mutex);
free(src);
PQfreemem(sql);
}
}
else if (scm_is_null(obj)) out = c2s("NULL");
else out = c2s("NULL");
scm_remember_upto_here_1(out);
scm_remember_upto_here_2(conn, obj);
return out;
}
int pgsql_update_lease_from_sql(const char* ifname, const uint8_t* mac, const struct in_addr* ip, uint32_t* expiresAt)
{
PGresult * res;
char sql[1024];
char *sql_esc_bridge;
if (!pgsql_connected())
return -1;
sql_esc_bridge = PQescapeLiteral(pgsql, ifname, strlen(ifname));
snprintf(sql, sizeof(sql), "SELECT ceil(extract('epoch' from (validUntil - CURRENT_TIMESTAMP)))::varchar as expiresin FROM " PGSQLLEASETABLE " WHERE validUntil > CURRENT_TIMESTAMP AND bridge = %s AND mac = '%s' AND ip = '%s';", sql_esc_bridge, ether_ntoa_z((struct ether_addr *)mac), inet_ntoa(*ip));
PQfreemem(sql_esc_bridge); sql_esc_bridge = NULL;
res = pgsql_query_errprint_query(sql);
if (res == NULL)
return -1;
/* pgsql query sucessfull */
int col = -1;
if (PQntuples(res) > 0) {
col = PQfnumber(res, "expiresin");
if (col == -1)
eprintf(DEBUG_ERROR, "sql: update lease from sql did not find column expiresin");
}
if (col != -1) {
char *val = PQgetvalue(res, 0, col);
const int now = reltime();
int expiresIn = atoi(val);
eprintf(DEBUG_VERBOSE, "sql: update lease from sql: MAC: %s IP: %s VLAN: %s expiresIn (old): %d expiresIn (new): %d raw: %s", ether_ntoa_z((struct ether_addr *)mac), inet_ntoa(*ip), ifname, *expiresAt - now, expiresIn, val);
*expiresAt = expiresIn + now;
} else {
*expiresAt = 0;
}
PQclear(res); res = NULL;
return 0;
}
