static bool create_database(DbType type, QofBackend *qbe, dbi_conn conn, const char* db) { const char *dbname; const char *dbcreate; if (type == DbType::DBI_MYSQL) { dbname = "mysql"; dbcreate = "CREATE DATABASE %s CHARACTER SET utf8"; } else { dbname = "postgres"; dbcreate = "CREATE DATABASE %s WITH TEMPLATE template0 ENCODING 'UTF8'"; } PairVec options; options.push_back(std::make_pair("dbname", dbname)); try { set_options(conn, options); } catch (std::runtime_error& err) { qof_backend_set_error (qbe, ERR_BACKEND_SERVER_ERR); return false; } auto result = dbi_conn_connect (conn); if (result < 0) { PERR ("Unable to connect to %s database", dbname); qof_backend_set_error (qbe, ERR_BACKEND_SERVER_ERR); return false; } if (type == DbType::DBI_MYSQL) adjust_sql_options(conn); auto dresult = dbi_conn_queryf (conn, dbcreate, db); if (dresult == nullptr) { PERR ("Unable to create database '%s'\n", db); qof_backend_set_error (qbe, ERR_BACKEND_SERVER_ERR); return false; } if (type == DbType::DBI_PGSQL) { const char *alterdb = "ALTER DATABASE %s SET " "standard_conforming_strings TO on"; dbi_conn_queryf (conn, alterdb, db); } dbi_conn_close(conn); conn = nullptr; return true; }
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; }
/** * 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; }
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; }
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; }
/////////////////////////////////// // 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; }
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; }
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; }
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; }
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); }
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; }
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; }
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; }
static int set_uas_record(struct module *mod) { int j, k; char line[LEN_4096] = {0}; char tmp[LEN_2048] = {0}; ST_UAS *pe = NULL; tDBConn *pconn = NULL; if ((pconn = attach_conn(GDSDB, gds_ip, gds_port)) == NULL) { logRecord(LOG_ERR, "%s(%d): failed to get db conn\n", __FUNCTION__, __LINE__); return -1; } /* save collect data to output_db */ if (mod->emp_record != 0 && mod->emp_array != NULL) { pe = mod->emp_array; for (j = 0; j < mod->emp_record; j++) { memset(line, '\0', LEN_4096); sprintf(line, "insert into \"%s\"(", mod->table[0].table); for (k = 0; k < mod->n_col; k++) { memset(tmp, '\0', LEN_2048); sprintf(tmp, "\"%s\",", mod->info[k].hdr); strcat(line, tmp); } line[strlen(line) - 1] = ')'; strcat(line, "values("); memset(tmp, '\0', LEN_2048); sprintf(tmp, "'%lld','%s', '%lld','%lld','%lld','%d');", pe[j].sdr_id, pe[j].sdr_time, pe[j].sdr_num, pe[j].sdr_online_times, pe[j].sdr_offline_times, pe[j].sdr_active_flag); strcat(line, tmp); dbi_conn_queryf(pconn->conn, line); logRecord(LOG_DEBUG, "%s(%d):line->%s\n", __FUNCTION__, __LINE__, line); } } if (mod->emp_array) { free(mod->emp_array); mod->emp_array = NULL; } if (mod->amp_array) { free(mod->amp_array); mod->amp_array = NULL; } if (mod->ser_array) { free(mod->ser_array); mod->ser_array = NULL; } detach_conn(pconn); return 0; }
static int set_ccs_record(struct module *mod) { int j, k; char line[LEN_4096] = {0}; char tmp[LEN_2048] = {0}; char sdr_id_field[LEN_64] = { '\0' }; ST_CCS *pe = NULL; tDBConn *pconn = NULL; if ((pconn = attach_conn(GDSDB, gds_ip, gds_port)) == NULL) { logRecord(LOG_ERR, "%s(%d): failed to get db conn\n", __FUNCTION__, __LINE__); return -1; } /* save collect data to output_db */ if (mod->emp_record != 0 && mod->emp_array != NULL) { pe = mod->emp_array; for (j = 0; j < mod->emp_record; j++) { memset(line, '\0', LEN_4096); sprintf(line, "insert into \"%s\"(", mod->table[0].table); /* for (k = 0; k < mod->n_col; k++) { memset(tmp, '\0', LEN_2048); sprintf(tmp, "\"%s\",", mod->info[k].hdr); strcat(line, tmp); } */ /* EMP: sdr_id, not include sdr_amp_id */ snprintf( sdr_id_field, sizeof(sdr_id_field) - 1, "\"%s\",", mod->info[0].hdr ); strcat( line, sdr_id_field ); for ( k = 2; k < mod->n_col; k++ ) { memset( tmp, '\0', LEN_2048 ); snprintf( tmp, sizeof(tmp) - 1 , "\"%s\",", mod->info[k].hdr ); strncat( line, tmp, sizeof(line) - 1 ); } line[strlen(line) - 1] = ')'; strcat(line, "values("); memset(tmp, '\0', LEN_2048); sprintf(tmp, "'%lld','%s','%s','%lld','%lld','%lld','%lld','%lld','%lld','%lld',\ '%lld','%lld','%lld','%lld','%lld','%lld','%lld','%lld');", pe[j].sdr_id, pe[j].sdr_record_time, pe[j].sdr_time, pe[j].sdr_cyc_type, pe[j].sdr_ptt_time, pe[j].sdr_ptt_count, pe[j].sdr_audio_time, pe[j].sdr_audio_caller_time, pe[j].sdr_audio_callee_time, pe[j].sdr_audio_count, pe[j].sdr_audio_caller_count, pe[j].sdr_audio_callee_count, pe[j].sdr_video_time, pe[j].sdr_video_caller_time, pe[j].sdr_video_callee_time, pe[j].sdr_video_count, pe[j].sdr_video_caller_count, pe[j].sdr_video_callee_count); strcat(line, tmp); dbi_conn_queryf(pconn->conn, line); logRecord(LOG_DEBUG, "%s(%d):line->%s\n", __FUNCTION__, __LINE__, line); } }
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; }
/** @brief Load the child queries subordinate to a UNION, INTERSECT, or EXCEPT query. @param state Pointer to the query-building context. @param parent ID of the UNION, INTERSECT, or EXCEPT query. @param type_str The type of the query ("UNION", "INTERSECT", or "EXCEPT"). @return If successful, a pointer to a linked list of QSeq, each bearing a pointer to a StoredQ; otherwise NULL. The @a type_str parameter is used only for building error messages. */ static QSeq* loadChildQueries( BuildSQLState* state, int parent_id, const char* type_str ) { QSeq* child_list = NULL; // The ORDER BY is in descending order so that we can build the list by adding to // the head, and it will wind up in the right order. dbi_result result = dbi_conn_queryf( state->dbhandle, "SELECT id, parent_query, seq_no, child_query " "FROM query.query_sequence WHERE parent_query = %d ORDER BY seq_no DESC", parent_id ); if( result ) { if( dbi_result_first_row( result ) ) { int count = 0; while( 1 ) { ++count; QSeq* seq = constructQSeq( state, result ); if( seq ) { PRINT( "Found a child query\n" ); PRINT( "\tid: %d\n", seq->id ); PRINT( "\tparent id: %d\n", seq->parent_query_id ); PRINT( "\tseq_no: %d\n", seq->seq_no ); // Add to the head of the list seq->next = child_list; child_list = seq; } else{ freeQSeqList( child_list ); return NULL; } if( !dbi_result_next_row( result )) break; } if( count < 2 ) { osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state, "%s query # %d has only one child query", type_str, parent_id )); state->error = 1; freeQSeqList( child_list ); return NULL; } } else { osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state, "%s query # %d has no child queries within it", type_str, parent_id )); state->error = 1; return NULL; } } else { const char* msg; int errnum = dbi_conn_error( state->dbhandle, &msg ); osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state, "Unable to query query.query_sequence table: # %d %s", errnum, msg ? msg : "No description available" )); state->error = 1; return NULL; } return child_list; }
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; }
void swd::database::set_cache_outdated(const int& profile_id, const bool& cache_outdated) { ensure_connection(); boost::unique_lock<boost::mutex> scoped_lock(dbi_mutex_); dbi_result res = dbi_conn_queryf(conn_, "UPDATE profiles SET cache_outdated = %i " "WHERE id = %i OR %i < 0", (cache_outdated ? 1 : 0), profile_id, profile_id); if (!res) { throw swd::exceptions::database_exception("Can't execute cache_outdated query"); } }
void swd::database::add_integrity_request_connector(const int& rule_id, const int& request_id) { ensure_connection(); boost::unique_lock<boost::mutex> scoped_lock(dbi_mutex_); dbi_result res = dbi_conn_queryf(conn_, "INSERT INTO integrity_requests " "(rule_id, request_id) VALUES (%i, %i)", rule_id, request_id); if (!res) { throw swd::exceptions::database_exception("Can't execute integrity_request query"); } }
void swd::database::add_whitelist_parameter_connector(const int& rule_id, const int& parameter_id) { ensure_connection(); boost::unique_lock<boost::mutex> scoped_lock(dbi_mutex_); dbi_result res = dbi_conn_queryf(conn_, "INSERT INTO whitelist_parameters " "(rule_id, parameter_id) VALUES (%i, %i)", rule_id, parameter_id); if (!res) { throw swd::exceptions::database_exception("Can't execute whitelist_parameter query"); } }
/** @brief Load a stored query. @param state Pointer to the query-building context. @param query_id ID of the query in query.stored_query. @return A pointer to the newly loaded StoredQ if successful, or NULL if not. The calling code is responsible for freeing the StoredQ by calling storedQFree(). */ StoredQ* getStoredQuery( BuildSQLState* state, int query_id ) { if( !state ) return NULL; // Check the stack to see if the current query is nested inside itself. If it is, then // abort in order to avoid infinite recursion. If it isn't, then add it to the stack. // (Make sure to pop it off the stack before returning.) if( searchIdStack( state->query_stack, query_id, NULL )) { osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state, "Infinite recursion detected; query # %d is nested within itself", query_id )); state->error = 1; return NULL; } else push_id( &state->query_stack, query_id, NULL ); StoredQ* sq = NULL; dbi_result result = dbi_conn_queryf( state->dbhandle, "SELECT id, type, use_all, use_distinct, from_clause, where_clause, having_clause " "FROM query.stored_query WHERE id = %d;", query_id ); if( result ) { if( dbi_result_first_row( result ) ) { sq = constructStoredQ( state, result ); if( sq ) { PRINT( "Got a query row\n" ); PRINT( "\tid: %d\n", sq->id ); PRINT( "\ttype: %d\n", (int) sq->type ); PRINT( "\tuse_all: %s\n", sq->use_all ? "true" : "false" ); PRINT( "\tuse_distinct: %s\n", sq->use_distinct ? "true" : "false" ); } else osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state, "Unable to build a query for id = %d", query_id )); } else { sqlAddMsg( state, "Stored query not found for id %d", query_id ); } dbi_result_free( result ); } else { const char* msg; int errnum = dbi_conn_error( state->dbhandle, &msg ); osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state, "Unable to query query.stored_query table: #%d %s", errnum, msg ? msg : "No description available" )); } pop_id( &state->query_stack ); return sq; }
void db_create_pl_ch_privileges(struct config *c, struct server *s) { dbi_result res; int flags; size_t iter, iter2; int reg_id; struct channel *ch; struct registration *reg; struct player_channel_privilege *tmp_priv; char *q = "SELECT * FROM player_channel_privileges WHERE channel_id = %i;"; logger(LOG_INFO, "Reading player channel privileges..."); ar_each(struct channel *, ch, iter, s->chans) if (!(ch->flags & CHANNEL_FLAG_UNREGISTERED)) { res = dbi_conn_queryf(c->conn, q, ch->db_id); if (res) { while (dbi_result_next_row(res)) { tmp_priv = new_player_channel_privilege(); tmp_priv->ch = ch; flags = 0; if (dbi_result_get_uint(res, "channel_admin")) flags |= CHANNEL_PRIV_CHANADMIN; if (dbi_result_get_uint(res, "operator")) flags |= CHANNEL_PRIV_OP; if (dbi_result_get_uint(res, "voice")) flags |= CHANNEL_PRIV_VOICE; if (dbi_result_get_uint(res, "auto_operator")) flags |= CHANNEL_PRIV_AUTOOP; if (dbi_result_get_uint(res, "auto_voice")) flags |= CHANNEL_PRIV_AUTOVOICE; tmp_priv->flags = flags; tmp_priv->reg = PL_CH_PRIV_REGISTERED; reg_id = dbi_result_get_uint(res, "player_id"); ar_each(struct registration *, reg, iter2, s->regs) if (reg->db_id == reg_id) tmp_priv->pl_or_reg.reg = reg; ar_end_each; if (tmp_priv->pl_or_reg.reg != NULL) add_player_channel_privilege(ch, tmp_priv); else free(tmp_priv); } dbi_result_free(res); } else {
template<> void GncDbiProviderImpl<DbType::DBI_MYSQL>::drop_index (dbi_conn conn, const std::string& index) { auto sep = index.find(' ', 0); if (index.find(' ', sep + 1) != std::string::npos) { PWARN("Drop index error: invalid MySQL index format (<index> <table>): %s", index.c_str()); return; } auto result = dbi_conn_queryf (conn, "DROP INDEX %s ON %s", index.substr(0, sep).c_str(), index.substr(sep + 1).c_str()); if (result) dbi_result_free (result); }
static SelectItem* getSelectList( BuildSQLState* state, int query_id ) { SelectItem* select_list = NULL; // The ORDER BY is in descending order so that we can build the list by adding to // the head, and it will wind up in the right order. dbi_result result = dbi_conn_queryf( state->dbhandle, "SELECT id, stored_query, seq_no, expression, column_alias, grouped_by " "FROM query.select_item WHERE stored_query = %d ORDER BY seq_no DESC", query_id ); if( result ) { if( dbi_result_first_row( result ) ) { while( 1 ) { SelectItem* item = constructSelectItem( state, result ); if( item ) { PRINT( "Found a SELECT item\n" ); PRINT( "\tid: %d\n", item->id ); PRINT( "\tstored_query_id: %d\n", item->stored_query_id ); PRINT( "\tseq_no: %d\n", item->seq_no ); PRINT( "\tcolumn_alias: %s\n", item->column_alias ? item->column_alias : "(none)" ); PRINT( "\tgrouped_by: %d\n", item->grouped_by ); item->next = select_list; select_list = item; } else { osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state, "Unable to build select list for query id #%d", query_id )); selectListFree( select_list ); select_list = NULL; break; } if( !dbi_result_next_row( result ) ) break; }; } } else { const char* msg; int errnum = dbi_conn_error( state->dbhandle, &msg ); osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state, "Unable to query query.select_list table: #%d %s", errnum, msg ? msg : "No description available" )); } return select_list; }
static Expression* getExpression( BuildSQLState* state, int id ) { // Check the stack to see if the current expression is nested inside itself. If it is, // then abort in order to avoid infinite recursion. If it isn't, then add it to the // stack. (Make sure to pop it off the stack before returning.) if( searchIdStack( state->expr_stack, id, NULL )) { osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state, "Infinite recursion detected; expression # %d is nested within itself", id )); state->error = 1; return NULL; } else push_id( &state->expr_stack, id, NULL ); Expression* exp = NULL; dbi_result result = dbi_conn_queryf( state->dbhandle, "SELECT id, type, parenthesize, parent_expr, seq_no, literal, table_alias, " "column_name, left_operand, operator, right_operand, function_id, subquery, cast_type " "FROM query.expression WHERE id = %d;", id ); if( result ) { if( dbi_result_first_row( result ) ) { exp = constructExpression( state, result ); if( exp ) { PRINT( "Got an expression\n" ); PRINT( "\tid = %d\n", exp->id ); PRINT( "\ttype = %d\n", exp->type ); PRINT( "\tparenthesize = %d\n", exp->parenthesize ); PRINT( "\tcolumn_name = %s\n", exp->column_name ? exp->column_name : "(none)" ); } else osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state, "Unable to construct an Expression for id = %d", id )); } } else { const char* msg; int errnum = dbi_conn_error( state->dbhandle, &msg ); osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state, "Unable to query query.expression table: #%d %s", errnum, msg ? msg : "No description available" )); } pop_id( &state->expr_stack ); return exp; }
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; }
/** @brief Build a list of joined relations. @param state Pointer to the query-building context. @param id ID of the parent relation. @return A pointer to the first in a linked list of FromRelations, if there are any; or NULL if there aren't any, or in case of an error. Look for relations joined directly to the parent relation, and make a list of them. */ static FromRelation* getJoinList( BuildSQLState* state, int id ) { FromRelation* join_list = NULL; // The ORDER BY is in descending order so that we can build the list by adding to // the head, and it will wind up in the right order. dbi_result result = dbi_conn_queryf( state->dbhandle, "SELECT id, type, table_name, class_name, subquery, function_call, " "table_alias, parent_relation, seq_no, join_type, on_clause " "FROM query.from_relation WHERE parent_relation = %d ORDER BY seq_no DESC", id ); if( result ) { if( dbi_result_first_row( result ) ) { while( 1 ) { FromRelation* relation = constructFromRelation( state, result ); if( relation ) { PRINT( "Found a joined relation\n" ); PRINT( "\tjoin_type: %d\n", relation->join_type ); PRINT( "\ttable_name: %s\n", relation->table_name ); relation->next = join_list; join_list = relation; } else { osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state, "Unable to build join list for from relation id #%d", id )); joinListFree( join_list ); join_list = NULL; break; } if( !dbi_result_next_row( result ) ) break; }; } } else { const char* msg; int errnum = dbi_conn_error( state->dbhandle, &msg ); osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state, "Unable to query query.from_relation table for join list: #%d %s", errnum, msg ? msg : "No description available" )); } return join_list; }
swd::blacklist_rules swd::database::get_blacklist_rules(const int& profile_id, const std::string& caller, const std::string& path) { swd::log::i()->send(swd::notice, "Get blacklist 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); dbi_result res = dbi_conn_queryf(conn_, "SELECT r.id, r.path, r.threshold " "FROM blacklist_rules AS r WHERE 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 blacklist_rules query"); } swd::blacklist_rules rules; while (dbi_result_next_row(res)) { swd::blacklist_rule_ptr rule(new swd::blacklist_rule()); rule->set_id(dbi_result_get_uint(res, "id")); rule->set_threshold(dbi_result_get_uint(res, "threshold")); rules.push_back(rule); } dbi_result_free(res); return rules; }