//*******************************************************************
// UPDATE SERVER COLOR
//*******************************************************************
static void update_server_color(trx *t, struct probe_result *prv)
{
dbi_result result;
int maxcolor, newcolor;
if (t->def->server < 2) return;
if (t->probe->fuse && (t->res->color < prv->color)) {
// if this probe acts like a fuse, use the previous color if it's higher
newcolor = prv->color;
} else {
newcolor = t->res->color;
}
maxcolor = newcolor; // init
result = db_query(t->probe->db, 0,
"select max(color) as color from pr_status where server = '%u'", t->def->server);
if (result && dbi_result_next_row(result)) {
maxcolor = dbi_result_get_uint(result, "color");
}
dbi_result_free(result);
//if (t->res->color <= maxcolor) return;
result = db_query(t->probe->db, 0,
// update server set color = '200' where id = '345'
"update %s set %s = '%u' where %s = '%u'",
OPT_ARG(SERVER_TABLE_NAME), OPT_ARG(SERVER_TABLE_COLOR_FIELD),
maxcolor, OPT_ARG(SERVER_TABLE_ID_FIELD), t->def->server);
dbi_result_free(result);
}
int
main (int argc, char *argv[])
{
dbi_conn conn;
dbi_result result;
const char *err_msg;
int err_code;
if (argc < 2)
{
fprintf (stderr, "%s: Need a libdir as argument!\n", argv[0]);
return 1;
}
dbi_initialize (argv[1]);
conn = dbi_conn_new ("null");
dbi_conn_connect (conn);
/*
* Queries
*/
result = dbi_conn_query (conn, "COMMIT");
assert (result != NULL);
dbi_result_free (result);
dbi_conn_set_option_numeric (conn, "null.error.commit", 1);
result = dbi_conn_query (conn, "COMMIT");
assert (result == NULL);
dbi_conn_set_option_numeric (conn, "null.error.commit", 0);
result = dbi_conn_query (conn, "COMMIT");
assert (result != NULL);
dbi_result_free (result);
/*
* Normal queries
*/
result = dbi_conn_query (conn, "SELECT * FROM DUAL");
assert (result != NULL);
dbi_result_free (result);
dbi_conn_set_option_numeric (conn, "null.error.query", 1);
result = dbi_conn_query (conn, "SELECT * FROM DUAL");
assert (result == NULL);
dbi_conn_set_option_numeric (conn, "null.error.query", 0);
result = dbi_conn_query (conn, "SELECT * FROM DUAL");
assert (result != NULL);
dbi_result_free (result);
/*
* Cleanup
*/
dbi_conn_close (conn);
dbi_shutdown ();
return 0;
}
static void calc_ser_cds(struct module *mod, dbi_conn conn, int interval)
{
int ct = 0, st = 0;
dbi_result result = 0;
ST_CDS *ps = NULL;
ST_CDS *pe = NULL;
long long rst = 0;
ps = (ST_CDS *)mod->ser_array;
pe = (ST_CDS *)mod->emp_array;
st = 0;
/* acquire ser uss stats */
result = dbi_conn_queryf(conn, "select d_id from \"T_Device\" where d_type = 'mds' order by d_id");
if (result) {
while(dbi_result_next_row(result)) {
ps[st].sdr_id = dbi_result_get_longlong(result, "d_id");
convert_time_to_string(statis.cur_time, ps[st].sdr_record_time, 0);
convert_time_to_string(statis.cur_time - 24*60*60, ps[st].sdr_time, 1);
if (interval == 0) {
ps[st].sdr_date_flag = 0;
} else if (interval == 7) {
ps[st].sdr_date_flag = 1;
} else {
ps[st].sdr_date_flag = 2;
}
st++;
}
dbi_result_free(result);
}
for (st = 0; st < mod->ser_record; st++)
{
result = dbi_conn_queryf(conn, "select e_id from \"T_Enterprise\" where \
e_mds_id = %lld order by e_id ", ps[st].sdr_id);
if (result) {
while(dbi_result_next_row(result)) {
rst = dbi_result_get_longlong(result, "e_id");
for (ct = 0; ct < mod->emp_record; ct++) {
if (rst == pe[ct].sdr_id) {
ps[st].sdr_ptt_htime += pe[ct].sdr_ptt_htime;
ps[st].sdr_ptt_hcount += pe[ct].sdr_ptt_hcount;
ps[st].sdr_call_htime += pe[ct].sdr_call_htime;
ps[st].sdr_call_hcount += pe[ct].sdr_call_hcount;
ps[st].sdr_video_htime += pe[ct].sdr_video_htime;
ps[st].sdr_video_hcount += pe[ct].sdr_video_hcount;
ps[st].sdr_audio_htime += pe[ct].sdr_audio_htime;
ps[st].sdr_audio_hcount += pe[ct].sdr_audio_hcount;
}
}
}
dbi_result_free(result);
}
}
//log_cds_stats(ps, mod->ser_record);
return;
}
/////////////////////////////////////////////////////////////////////////////////
//
// Function:
//
// Purpose:
//
// Parameters:
//
// Return value:
//
// Author: Komatsu Yuji(Zheng Chuyu)
//
/////////////////////////////////////////////////////////////////////////////////
int dateconds_execute(const apr_uint64_t proc_jobunit_id,
const time_t base_time, const int beginning_of_week)
{
int rc, rv;
dbi_result result;
datecond_t *obj = NULL;
struct tm *tm = NULL;
jhklog_trace
("In %s() proc_jobunit_id: %llu, base_time: %ld, beginning_of_week: %d",
__func__, proc_jobunit_id, base_time, beginning_of_week);
result =
jhkdb_select
("SELECT * FROM proc_dateconds WHERE proc_jobunit_id = %llu",
proc_jobunit_id);
if (result == NULL)
return -1;
// unset date conditions
if (dbi_result_get_numrows(result) == 0) {
dbi_result_free(result);
return CONNECTOR_KIND_NORMAL;
}
// init
rc = -1;
obj = datecond_new();
// date condition
while (dbi_result_next_row(result)) {
datecond_load(obj, result);
rv = datecond_execute(obj, base_time, beginning_of_week);
if (rv == JHAKO_FALSE)
continue;
if (rv == JHAKO_TRUE) {
rc = CONNECTOR_KIND_NORMAL;
} else {
rc = CONNECTOR_KIND_ERROR;
}
goto finish;
}
rc = CONNECTOR_KIND_BRANCH;
finish:
datecond_destroy(obj);
dbi_result_free(result);
return rc;
}
//*******************************************************************
// REMOVE ALL OCCURRENCES OF A PROBE IN THE HISTORY FILES
//*******************************************************************
static void delete_history(trx *t, struct probe_result *nxt)
{
dbi_result result;
result = db_query(t->probe->db, 0,
"delete from pr_hist "
"where stattime = '%u' and probe = '%u' and class = '%d'",
nxt->stattime, t->def->probeid, t->probe->class);
dbi_result_free(result);
result = db_query(t->probe->db, 0,
"delete from pr_status "
"where stattime = '%u' and probe = '%u' and class = '%d'",
nxt->stattime, t->def->probeid, t->probe->class);
dbi_result_free(result);
}
//*******************************************************************
// RETRIEVE PRECEDING RAW RECORD FROM DATABASE (well just the color)
//*******************************************************************
static struct probe_result *get_previous_record(trx *t)
{
dbi_result result;
struct probe_result *prv;
prv = g_malloc0(sizeof(struct probe_result));
if (prv == NULL) {
return(NULL);
}
prv->stattime = t->res->stattime;
prv->color = t->res->color;
if (!t->probe->store_results) return(prv); // we probably don't have a xxxx_raw table
result = db_query(t->probe->db, 0,
"select color, stattime "
"from pr_%s_raw use index(probstat) "
"where probe = '%u' and stattime < '%u' "
"order by stattime desc limit 1",
t->res->name, t->def->probeid, t->res->stattime);
if (!result) return(prv);
if (dbi_result_next_row(result)) {
prv->color = dbi_result_get_uint(result, "color");
prv->stattime = dbi_result_get_uint(result, "stattime");
}
dbi_result_free(result);
t->res->prevcolor = prv->color;
return(prv);
}
int rpserver_db_authenticate(dbi_conn db, const char *user, const char *password)
{
int retval = AUTH_UNKNOWN_USER;
dbi_result result;
LOG(LOG_INFO, "%s():%d - username: %s password: %s", __FUNCTION__, __LINE__, user, password);
return 0;
result = dbi_conn_queryf(db,
"SELECT * FROM `users` WHERE `username` = '%s' AND `password` = MD5('%s')",
user, password);
LOG(LOG_INFO, "%s():%d - SELECT * FROM `users` WHERE `username` = '%s' AND `password` = '%s'",
__FUNCTION__, __LINE__, user, password);
if (result) {
if( dbi_result_get_numrows(result) == 1 ) {
while (dbi_result_next_row(result)) {
LOG(LOG_INFO, "%s():%d - User \"%s\" is known and ACTIVE.",
__FUNCTION__, __LINE__, user);
retval = AUTH_VALID;
}
} else {
LOG(LOG_INFO, "%s():%d - User \"%s\" is unknown",
__FUNCTION__, __LINE__, user);
}
}
dbi_result_free(result);
return retval;
}
swd::blacklist_filters swd::database::get_blacklist_filters() {
swd::log::i()->send(swd::notice, "Get blacklist filters from db");
ensure_connection();
boost::unique_lock<boost::mutex> scoped_lock(dbi_mutex_);
dbi_result res = dbi_conn_query(conn_, "SELECT id, impact, rule FROM blacklist_filters");
if (!res) {
throw swd::exceptions::database_exception("Can't execute blacklist_filters query");
}
swd::blacklist_filters filters;
while (dbi_result_next_row(res)) {
swd::blacklist_filter_ptr filter(new swd::blacklist_filter());
filter->set_id(dbi_result_get_uint(res, "id"));
filter->set_impact(dbi_result_get_uint(res, "impact"));
filter->set_regex(dbi_result_get_string(res, "rule"));
filters.push_back(filter);
}
dbi_result_free(res);
return filters;
}
bool swd::database::is_flooding(const std::string& client_ip,
const int& profile_id) {
ensure_connection();
boost::unique_lock<boost::mutex> scoped_lock(dbi_mutex_);
char *client_ip_esc = strdup(client_ip.c_str());
dbi_conn_quote_string(conn_, &client_ip_esc);
dbi_result res = dbi_conn_queryf(conn_, "SELECT is_flooding(%i, %s) AS result",
profile_id, client_ip_esc);
free(client_ip_esc);
if (!res) {
throw swd::exceptions::database_exception("Can't execute request count query");
}
bool flooding = false;
if (dbi_result_get_numrows(res) == 1) {
if (!dbi_result_next_row(res)) {
throw swd::exceptions::database_exception("No flooding?");
}
flooding = (dbi_result_get_uint(res, "result") == 1);
}
dbi_result_free(res);
return flooding;
}
static char
query_handler(char *query_name, char *myq)
{
if(myq == NULL) {
myq = "SELECT NULL";
}
if(query_name == NULL) {
query_name = "noname";
}
if(query_name == "") {
query_name = "noname";
}
dbi_initialize(NULL);
conn = dbi_conn_new("mysql");
dbi_conn_set_option(conn, "host", getenv("DB_HOSTNAME"));
dbi_conn_set_option(conn, "username", getenv("DB_USERNAME"));
dbi_conn_set_option(conn, "password", getenv("DB_PASSWORD"));
dbi_conn_set_option(conn, "dbname", getenv("DB_DATABASE"));
dbi_conn_set_option(conn, "encoding", "UTF-8");
if (dbi_conn_connect(conn) < 0) {
printf("Could not connect. Please check the option settings and if the" \
"specific driver is available\n");
} else {
result = dbi_conn_queryf(conn, myq, threshold);
if (result) {
xmlAddChild(sql_node,query_doc(result,query_name));
dbi_result_free(result);
}
dbi_conn_close(conn);
}
return 0;
}
///////////////////////////////////
// developer's utils ;-)
int com_test(int p, param_list param)
{
dbi_result result;
unsigned int idnumber;
const char *fullname;
int j,k;
for(k=1;k<10;k++)
{
for(j=1; j<100; j++)
{
char *fen=get_fen();
result = dbi_conn_queryf(conn, "INSERT INTO test (fen) values ('%s')",fen);
FREE(fen);
if (result) {
while (dbi_result_next_row(result)) {
pprintf(p,"OK\n");
}
dbi_result_free(result);
}
else
{
//pprintf(p,"no result\n");
}
}
}
pprintf(p,"OK\n");
return COM_OK;
}
/////////////////////////////////////////////////////////////////////////////////
//
// Function:
//
// Purpose:
//
// Parameters:
//
// Return value:
//
// Author: Komatsu Yuji(Zheng Chuyu)
//
/////////////////////////////////////////////////////////////////////////////////
int dateconds_put_history(const apr_uint64_t proc_jobunit_id,
const apr_uint64_t hist_jobunit_id)
{
int rc;
datecond_t *obj;
dbi_result result;
jhklog_debug("In %s() proc_jobunit_id: %llu, hist_jobunit_id: %llu",
__func__, proc_jobunit_id, hist_jobunit_id);
result =
jhkdb_select
("SELECT * FROM proc_dateconds WHERE proc_jobunit_id = %llu",
proc_jobunit_id);
if (result == NULL)
return -1;
rc = -1;
obj = datecond_new();
while (dbi_result_next_row(result)) {
datecond_load(obj, result);
obj->hist_jobunit_id = hist_jobunit_id;
if (hist_datecond_insert(obj) == 0)
goto error;
}
rc = 0;
error:
datecond_destroy(obj);
dbi_result_free(result);
return rc;
}
/**
* afsql_dd_run_query:
*
* Run an SQL query on the connected database.
*
* NOTE: This function can only be called from the database thread.
**/
static gboolean
afsql_dd_run_query(AFSqlDestDriver *self, const gchar *query, gboolean silent, dbi_result *result)
{
dbi_result db_res;
msg_debug("Running SQL query",
evt_tag_str("query", query),
NULL);
db_res = dbi_conn_query(self->dbi_ctx, query);
if (!db_res)
{
const gchar *dbi_error;
if (!silent)
{
dbi_conn_error(self->dbi_ctx, &dbi_error);
msg_error("Error running SQL query",
evt_tag_str("type", self->type),
evt_tag_str("host", self->host),
evt_tag_str("port", self->port),
evt_tag_str("user", self->user),
evt_tag_str("database", self->database),
evt_tag_str("error", dbi_error),
evt_tag_str("query", query),
NULL);
}
return FALSE;
}
if (result)
*result = db_res;
else
dbi_result_free(db_res);
return TRUE;
}
/**
* afsql_dd_validate_table:
*
* Check if the given table exists in the database. If it doesn't
* create it, if it does, check if all the required fields are
* present and create them if they don't.
*
* NOTE: This function can only be called from the database thread.
**/
static gboolean
afsql_dd_ensure_table_is_syslogng_conform(AFSqlDestDriver *self, GString *table)
{
dbi_result db_res = NULL;
gboolean success = FALSE;
if (self->flags & AFSQL_DDF_DONT_CREATE_TABLES)
return TRUE;
_sanitize_sql_identifier(table->str);
if (_is_table_syslogng_conform(self, table->str))
return TRUE;
if (_is_table_present(self, table->str, &db_res))
{
/* table exists, check structure */
success = _ensure_table_is_syslogng_conform(self, db_res, table->str);
if (db_res)
dbi_result_free(db_res);
}
else
{
/* table does not exist, create it */
success = _table_create(self, table->str) && _table_create_indexes(self, table->str);
}
if (success)
{
/* we have successfully created/altered the destination table, record this information */
_remember_table_as_syslogng_conform(self, table->str);
}
return success;
}
template<> StrVec
GncDbiProviderImpl<DbType::DBI_MYSQL>::get_index_list (dbi_conn conn)
{
StrVec retval;
const char* errmsg;
auto tables = get_table_list(conn, "");
for (auto table_name : tables)
{
auto result = dbi_conn_queryf (conn,
"SHOW INDEXES IN %s WHERE Key_name != 'PRIMARY'",
table_name.c_str());
if (dbi_conn_error (conn, &errmsg) != DBI_ERROR_NONE)
{
PWARN ("Index Table Retrieval Error: %s on table %s\n",
errmsg, table_name.c_str());
continue;
}
while (dbi_result_next_row (result) != 0)
{
std::string index_name {dbi_result_get_string_idx (result, 3)};
retval.push_back(index_name + " " + table_name);
}
dbi_result_free (result);
}
return retval;
}
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(path.c_str());
dbi_conn_quote_string(conn_, &path_esc);
char *value_esc = strdup(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;
}
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(algorithm.c_str());
dbi_conn_quote_string(conn_, &algorithm_esc);
char *digest_esc = strdup(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 rpserver_db_pair_lookup(dbi_conn *db, const char *localid, char *buf, size_t maxlen)
{
dbi_result *result;
int len = 0;
result = dbi_conn_queryf(db,
"SELECT hname FROM "DB_TABLE_DEVICES
" WHERE localid='%s' AND name IS NULL "
,localid);
if(result){
if(dbi_result_next_row(result)) {
len += snprintf(buf+len, maxlen-len, "%s",
dbi_result_get_string(result, "hname"));
}
} 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;
}
dbi_result_free(result);
return len;
}
template <DbType P> void
GncDbiProviderImpl<P>::drop_index(dbi_conn conn, const std::string& index)
{
dbi_result result = dbi_conn_queryf (conn, "DROP INDEX %s", index.c_str());
if (result)
dbi_result_free (result);
}
/**
* Add a new registration to the database
*
* @param c the db configuration
* @param s the server
* @param r the registration
*
* @return 1 on success
*/
int db_add_registration(struct config *c, struct server *s, struct registration *r)
{
char *req = "INSERT INTO registrations (server_id, serveradmin, name, password) VALUES (%i, %i, %s, %s);";
char *quoted_name, *quoted_pass;
dbi_result res;
struct channel *ch;
struct player_channel_privilege *priv;
size_t iter, iter2;
dbi_conn_quote_string_copy(c->conn, r->name, "ed_name);
dbi_conn_quote_string_copy(c->conn, r->password, "ed_pass);
res = dbi_conn_queryf(c->conn, req, s->id, r->global_flags, quoted_name, quoted_pass);
if (res == NULL) {
logger(LOG_WARN, "db_add_registration : SQL query failed");
} else {
r->db_id = dbi_conn_sequence_last(c->conn, NULL);
dbi_result_free(res);
}
free(quoted_pass);
free(quoted_name);
ar_each(struct channel *, ch, iter, s->chans)
ar_each(struct player_channel_privilege *, priv, iter2, ch->pl_privileges)
if (priv->reg == PL_CH_PRIV_REGISTERED && priv->pl_or_reg.reg == r) {
logger(LOG_INFO, "db_add_registration : adding a new pl_chan_priv");
db_add_pl_chan_priv(c, priv);
}
ar_end_each;
ar_end_each;
return 1;
}
/**
* Go through the database, read and add to the server all
* the registrations stored.
*
* @param c the configuration of the db
* @param s the server
*/
int db_create_registrations(struct config *c, struct server *s)
{
char *q = "SELECT * FROM registrations WHERE server_id = %i;";
struct registration *r;
char *name, *pass;
dbi_result res;
res = dbi_conn_queryf(c->conn, q, s->id);
if (res) {
while (dbi_result_next_row(res)) {
r = new_registration();
r->db_id = dbi_result_get_uint(res, "id");
r->global_flags = dbi_result_get_uint(res, "serveradmin");
name = dbi_result_get_string_copy(res, "name");
strncpy(r->name, name, MIN(29, strlen(name)));
pass = dbi_result_get_string_copy(res, "password");
strcpy(r->password, pass);
add_registration(s, r);
/* free temporary variables */
free(pass); free(name);
}
dbi_result_free(res);
}
return 1;
}
int find_expired_probes(struct dbspec *dbspec)
{
xmlDocPtr doc;
xmlNodePtr probe;
dbi_result result;
dbi_conn conn;
int count = 0;
char buf[10];
sprintf(buf, "%d", dbspec->port);
conn = open_database(OPT_ARG(DBTYPE), dbspec->host, buf, dbspec->db,
dbspec->user, dbspec->password);
if (!conn) return 0;
doc = UpwatchXmlDoc("result", NULL);
// find all expired probes, but skip those for which processing
// has been stopped for some reason
result = db_query(conn, 0, "select probe.name, pr_status.probe, "
" pr_status.server, pr_status.color, "
" pr_status.expires "
"from pr_status, probe "
"where probe.id = pr_status.class and color <> 400 "
" and expires < UNIX_TIMESTAMP()-30 "
" and expires < probe.lastseen"
" and probe.expiry = 'yes'");
if (!result) goto errexit;
while (dbi_result_next_row(result)) {
char buffer[256];
time_t now = time(NULL);
probe = xmlNewChild(xmlDocGetRootElement(doc), NULL, dbi_result_get_string(result, "name"), NULL);
xmlSetProp(probe, "realm", dbspec->realm);
xmlSetProp(probe, "id", dbi_result_get_string(result, "probe"));
xmlSetProp(probe, "server", dbi_result_get_string(result, "server"));
sprintf(buffer, "%u", (int) now); xmlSetProp(probe, "date", buffer);
xmlSetProp(probe, "expires", dbi_result_get_string(result, "expires"));
xmlNewChild(probe, NULL, "color", "400"); // PURPLE
xmlNewChild(probe, NULL, "prevcolor", dbi_result_get_string(result, "color"));
LOG(LOG_INFO, "%s: purpled %s %d", dbspec->realm, dbi_result_get_string(result, "name"),
dbi_result_get_uint(result, "probe"));
count++;
}
if (count) {
xmlSetDocCompressMode(doc, OPT_VALUE_COMPRESS);
spool_result(OPT_ARG(SPOOLDIR), OPT_ARG(OUTPUT), doc, NULL);
LOG(LOG_INFO, "%s: purpled %u probes", dbspec->realm, count);
}
errexit:
if (result) dbi_result_free(result);
if (conn) close_database(conn);
if (doc) xmlFreeDoc(doc);
return count;
}
void refresh_database(dbi_conn conn)
{
dbi_result result;
char qry[1024];
sprintf(qry, "SELECT pr_pop3_def.id, pr_pop3_def.domid, pr_pop3_def.tblid, pr_realm.name, "
" pr_pop3_def.ipaddress, pr_pop3_def.username, "
" pr_pop3_def.password, "
" pr_pop3_def.yellow, pr_pop3_def.red "
"FROM pr_pop3_def, pr_realm "
"WHERE pr_pop3_def.id > 1 and pr_pop3_def.disable <> 'yes'"
" and pr_pop3_def.pgroup = '%d' and pr_realm.id = pr_pop3_def.domid",
(unsigned)OPT_VALUE_GROUPID);
result = db_query(conn, 1, qry);
if (!result) {
return;
}
while (dbi_result_next_row(result)) {
int id;
struct probedef *probe;
id = dbi_result_get_uint(result, "id");
probe = g_hash_table_lookup(cache, &id);
if (!probe) {
probe = g_malloc0(sizeof(struct probedef));
if (dbi_result_get_uint(result, "domid") > 1) {
probe->probeid = dbi_result_get_uint(result, "tblid");
probe->realm=strdup(dbi_result_get_string(result, "name"));
} else {
probe->probeid = probe->id;
}
g_hash_table_insert(cache, guintdup(id), probe);
}
if (probe->ipaddress) g_free(probe->ipaddress);
probe->ipaddress = dbi_result_get_string_copy(result, "ipaddress");
if (probe->username) g_free(probe->username);
probe->username = dbi_result_get_string_copy(result, "username");
if (probe->password) g_free(probe->password);
probe->password = dbi_result_get_string_copy(result, "password");
probe->yellow = dbi_result_get_float(result, "yellow");
probe->red = dbi_result_get_float(result, "red");
if (probe->msg) g_free(probe->msg);
probe->msg = NULL;
probe->seen = 1;
}
if (dbi_conn_error_flag(conn)) {
const char *errmsg;
dbi_conn_error(conn, &errmsg);
LOG(LOG_ERR, "%s", errmsg);
g_hash_table_foreach(cache, reset_seen, NULL);
} else {
g_hash_table_foreach_remove(cache, return_seen, NULL);
}
dbi_result_free(result);
}
swd::profile_ptr swd::database::get_profile(const std::string& server_ip,
const int& profile_id) {
std::stringstream log_message;
log_message << "Get profile from db -> server_ip: " << server_ip
<< "; profile_id: " << profile_id;
swd::log::i()->send(swd::notice, log_message.str());
/* Test the database connection status. Tries to reconnect if disconnected. */
ensure_connection();
/* Mutex to avoid race conditions. */
boost::unique_lock<boost::mutex> scoped_lock(dbi_mutex_);
/**
* First we escape server_ip. It comes from a trusted source, but better safe
* than sorry. This does not work with std::string though.
*/
char *server_ip_esc = strdup(server_ip.c_str());
dbi_conn_quote_string(conn_, &server_ip_esc);
/* Insert the ip and execute the query. */
dbi_result res = dbi_conn_queryf(conn_, "SELECT id, hmac_key, mode, "
"whitelist_enabled, blacklist_enabled, integrity_enabled, flooding_enabled, "
"blacklist_threshold, cache_outdated FROM profiles WHERE %s LIKE "
"prepare_wildcard(server_ip) AND id = %i", server_ip_esc, profile_id);
/* Don't forget to free server_ip_esc to avoid a memory leak. */
free(server_ip_esc);
if (!res) {
throw swd::exceptions::database_exception("Can't execute profile query");
}
if (dbi_result_get_numrows(res) != 1) {
throw swd::exceptions::database_exception("Can't get profile");
}
if (!dbi_result_next_row(res)) {
throw swd::exceptions::database_exception("No profile?");
}
swd::profile_ptr profile(new swd::profile());
profile->set_server_ip(server_ip),
profile->set_id(dbi_result_get_uint(res, "id"));
profile->set_mode(dbi_result_get_uint(res, "mode"));
profile->set_whitelist_enabled(dbi_result_get_uint(res, "whitelist_enabled") == 1);
profile->set_blacklist_enabled(dbi_result_get_uint(res, "blacklist_enabled") == 1);
profile->set_integrity_enabled(dbi_result_get_uint(res, "integrity_enabled") == 1);
profile->set_flooding_enabled(dbi_result_get_uint(res, "flooding_enabled") == 1);
profile->set_key(dbi_result_get_string(res, "hmac_key"));
profile->set_blacklist_threshold(dbi_result_get_uint(res, "blacklist_threshold"));
profile->set_cache_outdated(dbi_result_get_uint(res, "cache_outdated") == 1);
dbi_result_free(res);
return profile;
}
//*******************************************************************
// INDICATE WE HAVE NOTIFIED THIS USER
//*******************************************************************
void set_pr_hist_notified(trx *t)
{
dbi_result result;
result = db_query(t->probe->db, 0,
"update pr_hist set notified = 'yes' "
"where id = '%ull'", t->res->prevhistid);
dbi_result_free(result);
}
/* checks mysql sql_options and adjusts if necessary */
static void
adjust_sql_options (dbi_conn connection)
{
dbi_result result = dbi_conn_query( connection, "SELECT @@sql_mode");
if (result == nullptr)
{
const char* errmsg;
int err = dbi_conn_error(connection, &errmsg);
PERR("Unable to read sql_mode %d : %s", err, errmsg);
return;
}
dbi_result_first_row(result);
std::string str{dbi_result_get_string_idx(result, 1)};
dbi_result_free(result);
if (str.empty())
{
const char* errmsg;
int err = dbi_conn_error(connection, &errmsg);
if (err)
PERR("Unable to get sql_mode %d : %s", err, errmsg);
else
PINFO("Sql_mode isn't set.");
return;
}
PINFO("Initial sql_mode: %s", str.c_str());
if(str.find(SQL_OPTION_TO_REMOVE) == std::string::npos)
return;
std::string adjusted_str{adjust_sql_options_string(str)};
PINFO("Setting sql_mode to %s", adjusted_str.c_str());
std::string set_str{"SET sql_mode=" + std::move(adjusted_str)};
dbi_result set_result = dbi_conn_query(connection,
set_str.c_str());
if (set_result)
{
dbi_result_free(set_result);
}
else
{
const char* errmsg;
int err = dbi_conn_error(connection, &errmsg);
PERR("Unable to set sql_mode %d : %s", err, errmsg);
}
}
int db_del_registration(struct config *c, struct server *s, struct registration *r)
{
char *q = "DELETE FROM registrations WHERE id = %i;";
char *q2 = "DELETE FROM player_channel_privileges WHERE player_id = %i;";
dbi_result res;
res = dbi_conn_queryf(c->conn, q, r->db_id);
if (res == NULL)
logger(LOG_WARN, "db_del_registration : SQL query failed");
else
dbi_result_free(res);
res = dbi_conn_queryf(c->conn, q2, r->db_id);
if (res == NULL)
logger(LOG_WARN, "db_del_registration : SQL query failed (2)");
else
dbi_result_free(res);
return 1;
}
int dbiw_commit(db_wrap * self)
{
dbi_result dbir;
DB_DECL(DB_WRAP_E_BAD_ARG);
dbir = dbi_conn_query(conn, "COMMIT");
if (dbir)
dbi_result_free(dbir);
return 0;
}
swd::whitelist_rules swd::database::get_whitelist_rules(const int& profile_id,
const std::string& caller, const std::string& path) {
swd::log::i()->send(swd::notice, "Get whitelist rules from db");
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 *path_esc = strdup(path.c_str());
dbi_conn_quote_string(conn_, &path_esc);
/**
* Remove LIKE single character wildcard, because it could result easily in security
* problems if a user forgets to escape an underscore. And instead of a percentage sign
* it is nicer to use an asterisk, because it is more common.
*/
dbi_result res = dbi_conn_queryf(conn_, "SELECT r.id, r.path, f.id as filter_id, "
"f.rule, f.impact, r.min_length, r.max_length FROM whitelist_rules AS r, "
"whitelist_filters AS f WHERE r.filter_id = f.id AND r.profile_id = %i AND %s LIKE "
"prepare_wildcard(r.caller) AND %s LIKE prepare_wildcard(r.path) AND r.status = %i",
profile_id, caller_esc, path_esc, STATUS_ACTIVATED);
free(caller_esc);
free(path_esc);
if (!res) {
throw swd::exceptions::database_exception("Can't execute whitelist_rules query");
}
swd::whitelist_rules rules;
while (dbi_result_next_row(res)) {
swd::whitelist_filter_ptr filter(new swd::whitelist_filter());
filter->set_id(dbi_result_get_uint(res, "filter_id"));
filter->set_regex(dbi_result_get_string(res, "rule"));
swd::whitelist_rule_ptr rule(new swd::whitelist_rule());
rule->set_id(dbi_result_get_uint(res, "id"));
rule->set_filter(filter);
rule->set_min_length(dbi_result_get_uint(res, "min_length"));
rule->set_max_length(dbi_result_get_uint(res, "max_length"));
rules.push_back(rule);
}
dbi_result_free(res);
return rules;
}
/* This is used too early to call GncDbiProvider::get_table_list(). */
static bool
save_may_clobber_data (dbi_conn conn, const std::string& dbname)
{
/* Data may be clobbered iff the number of tables != 0 */
auto result = dbi_conn_get_table_list (conn, dbname.c_str(), nullptr);
bool retval = false;
if (result)
{
retval = dbi_result_get_numrows (result) > 0;
dbi_result_free (result);
}
return retval;
}
