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; }