int swd::database::save_hash(const int& request_id, const std::string& algorithm,
const std::string& digest) {
ensure_connection();
boost::unique_lock<boost::mutex> scoped_lock(dbi_mutex_);
char *algorithm_esc = strdup(remove_null(algorithm).c_str());
dbi_conn_quote_string(conn_, &algorithm_esc);
char *digest_esc = strdup(remove_null(digest).c_str());
dbi_conn_quote_string(conn_, &digest_esc);
dbi_result res = dbi_conn_queryf(conn_, "INSERT INTO hashes (request_id, "
"algorithm, digest) VALUES (%i, %s, %s)", request_id, algorithm_esc, digest_esc);
free(algorithm_esc);
free(digest_esc);
if (!res) {
throw swd::exceptions::database_exception("Can't execute hash query");
}
int id = dbi_conn_sequence_last(conn_, "hashes_id_seq");
dbi_result_free(res);
return id;
}
int swd::database::save_parameter(const int& request_id, const std::string& path,
const std::string& value, const int& total_whitelist_rules,
const int& critical_impact, const int& threat) {
ensure_connection();
boost::unique_lock<boost::mutex> scoped_lock(dbi_mutex_);
char *path_esc = strdup(remove_null(path).c_str());
dbi_conn_quote_string(conn_, &path_esc);
char *value_esc = strdup(remove_null(value).c_str());
dbi_conn_quote_string(conn_, &value_esc);
dbi_result res = dbi_conn_queryf(conn_, "INSERT INTO parameters "
"(request_id, path, value, total_whitelist_rules, critical_impact, threat) "
"VALUES (%i, %s, %s, %i, %i, %i)", request_id, path_esc, value_esc,
total_whitelist_rules, critical_impact, threat);
free(path_esc);
free(value_esc);
if (!res) {
throw swd::exceptions::database_exception("Can't execute parameter query");
}
int id = dbi_conn_sequence_last(conn_, "parameters_id_seq");
dbi_result_free(res);
return id;
}
/* LAST_INSERT_ID */
unsigned long long SMSDDBI_SeqID(GSM_SMSDConfig * Config, const char *id)
{
unsigned long long new_id;
char buffer[100];
new_id = dbi_conn_sequence_last(Config->conn.dbi, NULL);
if (new_id == 0) {
new_id = dbi_conn_sequence_last(Config->conn.dbi, id);
/* Need to escape for PostgreSQL */
if (new_id == 0) {
sprintf(buffer, "\"%s\"", id);
new_id = dbi_conn_sequence_last(Config->conn.dbi, buffer);
}
}
return new_id;
}
int swd::database::save_request(const int& profile_id, const std::string& caller,
const std::string& resource, const int& mode, const std::string& client_ip,
const int& total_integrity_rules) {
std::stringstream log_message;
log_message << "Save request -> profile: " << profile_id
<< "; caller: " << caller << "; resource: " << resource
<< "; mode: " << mode << "; client_ip: " << client_ip;
swd::log::i()->send(swd::notice, log_message.str());
ensure_connection();
boost::unique_lock<boost::mutex> scoped_lock(dbi_mutex_);
char *caller_esc = strdup(caller.c_str());
dbi_conn_quote_string(conn_, &caller_esc);
char *resource_esc = strdup(resource.c_str());
dbi_conn_quote_string(conn_, &resource_esc);
char *client_ip_esc = strdup(client_ip.c_str());
dbi_conn_quote_string(conn_, &client_ip_esc);
dbi_result res = dbi_conn_queryf(conn_, "INSERT INTO requests (profile_id, "
"caller, resource, mode, client_ip, total_integrity_rules) VALUES (%i, %s, "
"%s, %i, %s, %i)", profile_id, caller_esc, resource_esc, mode, client_ip_esc,
total_integrity_rules);
free(caller_esc);
free(resource_esc);
free(client_ip_esc);
if (!res) {
throw swd::exceptions::database_exception("Can't execute request query");
}
int id = dbi_conn_sequence_last(conn_, "requests_id_seq");
dbi_result_free(res);
return id;
}
/**
* return(last insert row inode)
*/
tagsistant_inode tagsistant_last_insert_id(dbi_conn conn)
{
return(dbi_conn_sequence_last(conn, NULL));
#if 0
// -------- alternative version -----------------------------------------------
tagsistant_inode inode = 0;
switch (tagsistant.sql_database_driver) {
case TAGSISTANT_DBI_SQLITE_BACKEND:
tagsistant_query("SELECT last_insert_rowid() ", conn, tagsistant_return_integer, &inode);
break;
case TAGSISTANT_DBI_MYSQL_BACKEND:
tagsistant_query("SELECT last_insert_id() ", conn, tagsistant_return_integer, &inode);
break;
}
return (inode);
#endif
}
//*******************************************************************
// retrieve the definitions + status
// get it from the cache. if there but too old: delete
// If a probe definition does not exist, it will be created.
// in case of mysql-has-gone-away type errors, we keep on running,
// it will be caught later-on.
//*******************************************************************
void *bb_cpu_get_def(trx *t, int create)
{
struct probe_def *def;
struct bb_cpu_result *res = (struct bb_cpu_result *)t->res;
dbi_result result;
time_t now = time(NULL);
if (res->color != STAT_PURPLE && res->server == 0) {
// first we find the serverid, this will be used to find the probe definition in the hashtable
result = db_query(t->probe->db, 0, query_server_by_name, res->hostname, res->hostname,
res->hostname, res->hostname, res->hostname);
if (!result) {
return(NULL);
}
if (dbi_result_next_row(result)) {
res->server = dbi_result_get_int_idx(result, 0);
} else {
LOG(LOG_NOTICE, "%s:%u@%s: server %s not found", res->realm, res->stattime, t->fromhost, res->hostname);
dbi_result_free(result);
return(NULL);
}
dbi_result_free(result);
}
// look in the cache for the def
def = g_hash_table_lookup(t->probe->cache, &res->server);
if (def && def->stamp < now - (120 + uw_rand(240))) { // older then 2 - 6 minutes?
g_hash_table_remove(t->probe->cache, &res->server);
def = NULL;
}
// if not there retrieve from database and insert in hash
if (def == NULL) {
def = g_malloc0(t->probe->def_size);
def->stamp = time(NULL);
def->server = res->server;
def->pgroup = 1;
strcpy(def->hide, "no");
// first find the definition based on the serverid
result = db_query(t->probe->db, 0, "select id, yellow, red, contact, hide, email, sms, delay "
"from pr_%s_def where server = '%u'",
res->name, res->server);
if (!result) {
g_free(def);
return(NULL);
}
if (dbi_result_get_numrows(result) == 0) { // DEF RECORD NOT FOUND
char sequence[40];
// no def record found? Create one.
dbi_result_free(result);
result = db_query(t->probe->db, 0,
"insert into pr_%s_def (server, description) values ('%u', '%s')",
res->name, res->server, res->hostname);
dbi_result_free(result);
sprintf(sequence, "pr_%s_def_id_seq", res->name);
def->probeid = dbi_conn_sequence_last(t->probe->db, sequence);
LOG(LOG_NOTICE, "%s:%u@%s: pr_%s_def created for %s, id = %u",
res->realm, res->stattime, t->fromhost, res->name, res->hostname, def->probeid);
result = db_query(t->probe->db, 0, "select id, yellow, red, contact, hide, email, sms, delay "
"from pr_%s_def where id = '%u'",
res->name, def->probeid);
}
if (!dbi_result_next_row(result)) {
LOG(LOG_NOTICE, "%s:%u@%s: no pr_%s_def found for server %u - skipped",
res->realm, res->stattime, t->fromhost, res->name, res->server);
dbi_result_free(result);
g_free(def);
return(NULL);
}
def->probeid = dbi_result_get_int(result, "id");
def->yellow = dbi_result_get_float(result, "yellow");
def->red = dbi_result_get_float(result, "red");
def->contact = dbi_result_get_int(result, "contact");
strcpy(def->hide, dbi_result_get_string_default(result, "hide", "no"));
strcpy(def->email, dbi_result_get_string_default(result, "email", ""));
strcpy(def->sms, dbi_result_get_string_default(result, "sms", ""));
def->delay = dbi_result_get_int(result, "delay");
dbi_result_free(result);
result = db_query(t->probe->db, 0,
"select color "
"from pr_status "
"where class = '%u' and probe = '%u'", t->probe->class, def->probeid);
if (result) {
if (dbi_result_next_row(result)) {
def->color = dbi_result_get_int(result, "color");
} else {
LOG(LOG_NOTICE, "%s:%u@%s: pr_status record for %s id %u not found",
res->realm, res->stattime, t->fromhost, res->name, def->probeid);
}
dbi_result_free(result);
}
if (!def->color) def->color = res->color;
result = db_query(t->probe->db, 0,
"select stattime from pr_%s_raw use index(probstat) "
"where probe = '%u' order by stattime desc limit 1",
res->name, def->probeid);
if (result) {
if (dbi_result_next_row(result)) {
def->newest = dbi_result_get_int(result, "stattime");
}
dbi_result_free(result);
}
g_hash_table_insert(t->probe->cache, guintdup(def->server), def);
}
/**
* Make a channel persistent by inserting it into the database
*
* @param c the db config
* @param ch the channel to register
*
* @return 0 on failure, 1 on success
*/
int db_register_channel(struct config *c, struct channel *ch)
{
char *q = "INSERT INTO channels \
(server_id, name, topic, description, \
codec, maxusers, ordr, \
flag_default, flag_hierarchical, flag_moderated, \
parent_id, password) \
VALUES \
(%i, %s, %s, %s, \
%i, %i, %i, \
%i, %i, %i, \
%i, %s);";
char *name_clean, *topic_clean, *desc_clean, *pass_clean;
int flag_default, flag_hierar, flag_mod;
int insert_id, parent_id;
size_t iter;
struct channel *tmp_ch;
struct player_channel_privilege *priv;
dbi_result res;
if (ch->db_id != 0) /* already exists in the db */
return 0;
/* Secure the input before inserting */
dbi_conn_quote_string_copy(c->conn, ch->name, &name_clean);
dbi_conn_quote_string_copy(c->conn, ch->topic, &topic_clean);
dbi_conn_quote_string_copy(c->conn, ch->desc, &desc_clean);
dbi_conn_quote_string_copy(c->conn, ch->password, &pass_clean);
/* better here than in the query function */
flag_default = (ch->flags & CHANNEL_FLAG_DEFAULT);
flag_hierar = (ch->flags & CHANNEL_FLAG_SUBCHANNELS);
flag_mod = (ch->flags & CHANNEL_FLAG_MODERATED);
/* Add the ID of the parent or -1 */
if (ch->parent == NULL)
parent_id = 0xFFFFFFFF;
else
parent_id = ch->parent->db_id;
res = dbi_conn_queryf(c->conn, q,
ch->in_server->id, name_clean, topic_clean, desc_clean,
ch->codec, ch->players->max_slots, ch->sort_order,
flag_default, flag_hierar, flag_mod,
parent_id, pass_clean);
if (res == NULL) {
logger(LOG_ERR, "Insertion request failed : ");
logger(LOG_ERR, q, ch->in_server->id, name_clean, topic_clean, desc_clean,
ch->codec, ch->players->max_slots, ch->sort_order,
flag_default, flag_hierar, flag_mod,
parent_id, pass_clean);
}
insert_id = dbi_conn_sequence_last(c->conn, NULL);
ch->db_id = insert_id;
/* Register all the subchannels */
if (ch_getflags(ch) & CHANNEL_FLAG_SUBCHANNELS) {
ar_each(struct channel *, tmp_ch, iter, ch->subchannels)
db_register_channel(c, tmp_ch);
ar_end_each;
}
int rpserver_db_pair_exec(dbi_conn *db, const char *localid, const char *name, char *buf, size_t maxlen)
{
dbi_result *result;
int len = 0;
int boxid = -1;
result = dbi_conn_queryf(db, "DELETE FROM "DB_TABLE_DEVICES" WHERE name='%s' AND iid IS NULL\n", name);
if(result){
dbi_result_free(result);
} else {
const char *err;
dbi_conn_error(db, &err);
LOG(LOG_INFO, "%s():%d - dbi error querying database (%s)\n", __FUNCTION__, __LINE__, err);
return -1;
}
result = dbi_conn_queryf(db, "SELECT device_id FROM "DB_TABLE_DEVICES" WHERE localid='%s' AND name IS NULL\n", localid);
if(result){
if(dbi_result_next_row(result)) {
boxid = dbi_result_get_int(result, "device_id");
LOG(LOG_INFO, "%s():%d - boxid is %d\n", __FUNCTION__, __LINE__, boxid);
}
dbi_result_free(result);
} else {
const char *err;
dbi_conn_error(db, &err);
LOG(LOG_INFO, "%s():%d - dbi error querying database (%s)\n", __FUNCTION__, __LINE__, err);
return -1;
}
if(boxid < 0){
LOG(LOG_INFO, "%s():%d - boxid %d !!!!\n", __FUNCTION__, __LINE__, boxid);
return -1;
}
result = dbi_conn_queryf(db, "UPDATE "DB_TABLE_DEVICES" SET name='%s' WHERE device_id=%d", name, boxid);
if(result){
dbi_result_free(result);
} else {
const char *err;
dbi_conn_error(db, &err);
LOG(LOG_INFO, "%s():%d - dbi error querying database (%s)\n", __FUNCTION__, __LINE__, err);
return -1;
}
LOG(LOG_INFO, "%s():%d - name updated to '%s' for moxid %d !!!!\n", __FUNCTION__, __LINE__, name, boxid);
result = dbi_conn_queryf(db, "SELECT hname, localid FROM "DB_TABLE_DEVICES" WHERE device_id=%d", boxid);
if(result){
if(dbi_result_next_row(result)) {
len += snprintf(buf+len, maxlen-len, "%s: %s",
dbi_result_get_string(result, "localid"),
dbi_result_get_string(result, "hname")
);
}
dbi_result_free(result);
} else {
const char *err;
dbi_conn_error(db, &err);
LOG(LOG_INFO, "%s():%d - dbi error querying database (%s)\n",
__FUNCTION__, __LINE__, err);
return -1;
}
LOG(LOG_INFO, "%s():%d - read localiid and hname: %s !!!!\n", __FUNCTION__, __LINE__, name, buf);
int other_pwid = -1;
int working_pwid = 0;
result = dbi_conn_queryf(db,
"SELECT pwid , used, atid FROM acc_devices_pwl JOIN acc_types USING(atid)"
" WHERE agid = 1 AND acc_devices_pwl.enable = 1 AND acc_types.enable =1"
" AND id = %d ORDER BY atid ASC", boxid);
if(result){
if(dbi_result_next_row(result)) {
other_pwid = dbi_result_get_int(result, "pwid");
if(dbi_result_get_int(result, "used")==1)
working_pwid++;
if(dbi_result_get_int(result, "atid")== REMACC_TYPE_MAC)
working_pwid++;
}
dbi_result_free(result);
} else {
const char *err;
dbi_conn_error(db, &err);
LOG(LOG_INFO, "%s():%d - dbi error querying database (%s)\n",
__FUNCTION__, __LINE__, err);
return -1;
}
LOG(LOG_INFO, "%s():%d - searched for other pwid : %d !!!!\n", __FUNCTION__, __LINE__, working_pwid);
if(working_pwid>0){
LOG(LOG_INFO, "%s():%d - has working pwid, no new pwid are issued (%d)\n" ,__FUNCTION__, __LINE__,working_pwid);
return len;
}
char other_pwid_str[32];
if(other_pwid < 0){
sprintf(other_pwid_str, "NULL");
} else {
sprintf(other_pwid_str, "%d", other_pwid);
}
result = dbi_conn_queryf(db, "INSERT INTO `rembox`.`acc_devices_pwl`"
" (`pwid` ,`atid` ,`id` ,`username` ,`password` ,`enable` ,`exported` ,`used` ,`created` ,`replaced`)"
" VALUES (NULL , %d, %d, '%s', NULL , '1', '0', '0', CURRENT_TIMESTAMP , %s)"
, REMACC_TYPE_MAC, boxid, name, other_pwid_str);
if(result){
dbi_result_free(result);
} else {
const char *err;
dbi_conn_error(db, &err);
LOG(LOG_INFO, "%s():%d - dbi error querying database (%s)\n", __FUNCTION__, __LINE__, err);
return -1;
}
LOG(LOG_INFO, "%s():%d - inserted new pwid : %d !!!!\n", __FUNCTION__, __LINE__, dbi_conn_sequence_last(db, NULL));
return len;
}
//*******************************************************************
// retrieve the definitions + status
// get it from the cache. if there but too old: delete
// If a probe definition does not exist, it will be created.
// in case of mysql-has-gone-away type errors, we keep on running,
// it will be caught later-on.
//*******************************************************************
void *bb_get_def(trx *t, int create)
{
struct probe_def *def;
struct bb_result *res = (struct bb_result *)t->res;
dbi_result result;
def = g_malloc0(t->probe->def_size);
def->stamp = time(NULL);
def->server = res->server;
def->pgroup = 1;
strcpy(def->hide, "no");
if (res->color == STAT_PURPLE && res->probeid) {
// find the definition based on the probe id
result = db_query(t->probe->db, 0,
"select id, contact, hide, email, sms, delay from pr_bb_def "
"where id = '%u'", res->probeid);
if (!result) {
g_free(def);
return(NULL);
}
} else {
if (res->server == 0) {
// first we find the serverid, this will be used to find the probe definition in the database
if (!query_server_by_name) {
LOG(LOG_WARNING, "%s:%u@%s: don't know how to find %s by name",
res->realm, res->stattime, t->fromhost, res->hostname);
g_free(def);
return(NULL);
}
result = db_query(t->probe->db, 0, query_server_by_name, res->hostname, res->hostname,
res->hostname, res->hostname, res->hostname);
if (!result) {
g_free(def);
return(NULL);
}
if (dbi_result_next_row(result)) {
res->server = dbi_result_get_int_idx(result, 0);
} else {
LOG(LOG_WARNING, "%s:%u@%s: server %s not found", res->realm, res->stattime, t->fromhost, res->hostname);
dbi_result_free(result);
g_free(def);
return(NULL);
}
dbi_result_free(result);
}
// first find the definition based on the serverid
result = db_query(t->probe->db, 0,
"select id, contact, hide, email, sms, delay from pr_bb_def "
"where bbname = '%s' and server = '%u'", res->bbname, res->server);
if (!result) {
g_free(def);
return(NULL);
}
}
if (dbi_result_get_numrows(result) == 0) { // DEF RECORD NOT FOUND
char sequence[40];
dbi_result_free(result);
result = db_query(t->probe->db, 0,
"insert into pr_%s_def (server, ipaddress, description, bbname) "
" values ('%u', '%s', '%s', '%s')",
res->name, res->server, res->ipaddress ? res->ipaddress : "",
res->hostname, res->bbname);
dbi_result_free(result);
sprintf(sequence, "pr_%s_def_id_seq", res->name);
def->probeid = dbi_conn_sequence_last(t->probe->db, sequence);
LOG(LOG_NOTICE, "%s:%u@%s: pr_bb_def %s created for %s, id = %u",
res->realm, res->stattime, t->fromhost, res->bbname, res->hostname, def->probeid);
result = db_query(t->probe->db, 0,
"select id, contact, hide, email, sms, delay from pr_bb_def "
"where bbname = '%s' and server = '%u'", res->bbname, res->server);
if (!result) return(NULL);
}
if (!dbi_result_next_row(result)) {
LOG(LOG_NOTICE, "%s:%u@%s: no pr_%s_def found for server %u - skipped",
res->realm, res->stattime, t->fromhost, res->name, res->server);
dbi_result_free(result);
return(NULL);
}
def->probeid = dbi_result_get_int(result, "id");
def->contact = dbi_result_get_int(result, "contact");
strcpy(def->hide, dbi_result_get_string_default(result, "hide", "no"));
strcpy(def->email, dbi_result_get_string_default(result, "email", ""));
strcpy(def->sms, dbi_result_get_string_default(result, "sms", ""));
def->delay = dbi_result_get_int(result, "delay");
dbi_result_free(result);
// definition found, get the pr_status
result = db_query(t->probe->db, 0,
"select color, stattime "
"from pr_status "
"where class = '%u' and probe = '%u'", t->probe->class, def->probeid);
if (result) {
if (dbi_result_next_row(result)) {
def->color = dbi_result_get_int(result, "color");
def->newest = dbi_result_get_int(result, "stattime");
} else {
LOG(LOG_NOTICE, "%s:%u@%s: pr_status record for %s id %u (server %s) not found",
res->realm, res->stattime, t->fromhost, res->name, def->probeid, res->hostname);
}
dbi_result_free(result);
}
if (!def->color) def->color = res->color;
res->probeid = def->probeid;
return(def);
}
