def query_db(self, query, args=()): ''' Simple queries to DB :param query: SQL query :param args: Arguments to inject into the query :return: Single hit for the given query ''' db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db) cursor = db.cursor() cur = cursor.execute(query, args) try: rv = cursor.fetchone() # Returns tuple debug_log.info(rv) if rv is not None: db.close() return rv[1] # The second value in the tuple. else: return None except Exception as e: debug_log.exception(e) debug_log.info(cur) db.close() return None
def storeCSR_JSON(self, DictionaryToStore): """ Store CSR into database :param DictionaryToStore: Dictionary in form {"key" : "dict_to_store"} :return: None """ cr_id = DictionaryToStore["cr_id"] csr_id = DictionaryToStore["csr_id"] consent_status = DictionaryToStore["consent_status"] rs_id = DictionaryToStore["rs_id"] surrogate_id = DictionaryToStore["surrogate_id"] previous_record_id = DictionaryToStore["previous_record_id"] slr_id = DictionaryToStore["slr_id"] json = DictionaryToStore["json"] db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db) cursor = db.cursor() debug_log.info("Storing following csr structure:") debug_log.info(DictionaryToStore) # debug_log.info(key) try: cursor.execute("INSERT INTO csr_storage (cr_id, csr_id, previous_record_id, consent_status, surrogate_id, slr_id, rs_id, json) \ VALUES (%s, %s, %s, %s, %s, %s, %s, %s)", [cr_id, csr_id, previous_record_id, consent_status, surrogate_id, slr_id, rs_id, dumps(json)]) db.commit() except IntegrityError as e: # db.execute("UPDATE csr_storage SET json=? WHERE cr_id=? ;", [dumps(DictionaryToStore[key]), key]) # db.commit() db.rollback() raise DetailedHTTPException(detail={"msg": "Adding CSR to the database has failed.", }, title="Failure in CSR storage", exception=e)
def verifySurrogate(self, code, surrogate): db = db_handler.get_db(self.db_path) for code_row in self.query_db("select * from codes where ID = ? AND code = ?;", [code, surrogate]): code_from_db = code_row["code"] # TODO: Could we remove code and surrogate_id after this check to ensure they wont be abused later. return True return False
def storeRS_ID(self, rs_id): db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db) cursor = db.cursor() rs_id_status = False cursor.execute("INSERT INTO rs_id_tbl (rs_id, used) \ VALUES (%s, %s)", (rs_id, rs_id_status)) db.commit() debug_log.info("Stored RS_ID({}) into DB".format(rs_id)) cursor.close()
def store_service_key_json(self, kid, surrogate_id, key_json): db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db) cursor = db.cursor() cursor.execute("INSERT INTO service_keys_tbl (kid, surrogate_id, key_json) \ VALUES (%s, %s, %s);", (kid, surrogate_id, dumps(key_json))) db.commit() # cursor.execute("UPDATE service_keys_tbl SET key_json=%s WHERE kid=%s ;", (dumps(key_json), kid)) # db.commit() debug_log.info("Stored key_json({}) for surrogate_id({}) into DB".format(key_json, surrogate_id)) cursor.close()
def storeRS_ID(self, rs_id): db = db_handler.get_db(self.db_path) try: db_handler.init_db(db) except OperationalError: pass rs_id_status = False db.execute("INSERT INTO rs_id_tbl (rs_id, used) \ VALUES (?, ?)", [rs_id, rs_id_status]) db.commit() db.close()
def add_surrogate_id_to_code(self, code, surrogate_id): db = db_handler.get_db(self.db_path) try: db_handler.init_db(db) except OperationalError: pass for code in self.query_db("select * from codes where code = ?;", [code]): code_from_db = code["code"] code_is_valid_and_unused = "!" in code_from_db if (code_is_valid_and_unused): db.execute("UPDATE codes SET code=? WHERE ID=? ;", [surrogate_id, code]) db.commit() else: raise Exception("Invalid code")
def storeSurrogateJSON(DictionaryToStore): db = db_handler.get_db() try: db_handler.init_db(db) except OperationalError: pass debug_log.info(DictionaryToStore) for key in DictionaryToStore: debug_log.info(key) db.execute( "INSERT INTO surrogate_and_user_mapping (user_id, surrogate_id) \ VALUES (?, ?)", [key, dumps(DictionaryToStore[key])]) db.commit()
def storeCode(self, code): """ Store generated code into database :param code: :return: None """ db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db) cursor = db.cursor() code_key = list(code.keys())[0] code_value = code[code_key] cursor.execute("INSERT INTO codes (ID,code) \ VALUES (%s, %s)", (code_key, code_value)) db.commit() debug_log.info("Storing code(key,value): {}, {}".format(code_key, code_value)) db.close()
def get_user_id_with_code(code): db = db_handler.get_db() for code_row in db_handler.query_db( "select * from code_and_user_mapping where code = ?;", [code]): user_from_db = code_row["user_id"] return user_from_db raise DetailedHTTPException( status=500, detail={ "msg": "Unable to link code to user_id in database", "detail": { "code": code } }, title="Failed to link code to user_id")
def storeCodeUser(DictionaryToStore): # {"code": "user_id"} db = db_handler.get_db() try: db_handler.init_db(db) except OperationalError: pass debug_log.info(DictionaryToStore) for key in DictionaryToStore: debug_log.info(key) db.execute( "INSERT INTO code_and_user_mapping (code, user_id) \ VALUES (?, ?)", [key, dumps(DictionaryToStore[key])]) db.commit()
def storeJSON(self, DictionaryToStore): db = db_handler.get_db(self.db_path) try: db_handler.init_db(db) except OperationalError: pass debug_log.info(DictionaryToStore) for key in DictionaryToStore: debug_log.info(key) try: db.execute("INSERT INTO storage (surrogate_id,json) \ VALUES (?, ?)", [key, dumps(DictionaryToStore[key])]) db.commit() except IntegrityError as e: db.execute("UPDATE storage SET json=? WHERE surrogate_id=? ;", [dumps(DictionaryToStore[key]), key]) db.commit()
def storeCode(self, code): db = db_handler.get_db(self.db_path) try: db_handler.init_db(db) except OperationalError: pass code_key = list(code.keys())[0] code_value = code[code_key] db.execute("INSERT INTO codes (ID,code) \ VALUES (?, ?)", [code_key, code_value]) db.commit() debug_log.info("{} {}".format(code_key, code_value)) for code in self.query_db("select * from codes where ID = ?;", [code_key]): debug_log.info(code["code"]) db.close()
def storeSurrogateJSON(self, DictionaryToStore): db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db) cursor = db.cursor() debug_log.info(DictionaryToStore) for key in DictionaryToStore: debug_log.info(key) cursor.execute( "INSERT INTO surrogate_and_user_mapping (user_id, surrogate_id) \ VALUES (%s, %s)", [key, dumps(DictionaryToStore[key])]) db.commit() db.close()
def store_ssr_JSON(self, json): """ Store SSR into database :param record_id: :param surrogate_id: :param slr_id: :param json: :return: """ db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db) cursor = db.cursor() decoded_payload = base_token_tool.decode_payload( json["attributes"]["payload"]) record_id = decoded_payload["record_id"] surrogate_id = decoded_payload["surrogate_id"] slr_id = decoded_payload["slr_id"] debug_log.info("Storing SSR '{}' momentarily.\n {}".format( record_id, decoded_payload)) prev_id = decoded_payload["prev_record_id"] if prev_id != "NULL": debug_log.info( "Verifying SSR chain is unbroken.\n Looking up previous record '{}'" .format(prev_id)) prev_record = self.query_db( "select record_id, json from ssr_storage where record_id = %s", (prev_id, )) if prev_record is None: raise TypeError( "Previous record_id is not found" ) # Todo We make this basic check but is it enough? debug_log.info("Found record: \n{}".format(prev_record)) debug_log.info( "Storing SSR '{}' belonging to surrogate_id '{}' with content:\n {}" .format(record_id, surrogate_id, json)) cursor.execute( "INSERT INTO ssr_storage (surrogate_id,json,record_id,slr_id,prev_record_id) \ VALUES (%s, %s, %s, %s, %s)", (surrogate_id, dumps(json), record_id, slr_id, prev_id)) db.commit() db.close()
def storeCodeUser(self, DictionaryToStore): # {"code": "user_id"} db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db) cursor = db.cursor() debug_log.info(DictionaryToStore) for key in DictionaryToStore: debug_log.info(key) cursor.execute( "INSERT INTO code_and_user_mapping (code, user_id) \ VALUES (%s, %s)", (key, dumps(DictionaryToStore[key]))) db.commit() db.close()
def change_rs_id_status(self, rs_id, status): db = db_handler.get_db(self.db_path) try: db_handler.init_db(db) except OperationalError: pass for rs_id_object in self.query_db("select * from rs_id_tbl where rs_id = ?;", [rs_id]): rs_id_from_db = rs_id_object["rs_id"] status_from_db = bool(rs_id_object["used"]) status_is_unused = status_from_db == False if (status_is_unused): db.execute("UPDATE rs_id_tbl SET used=? WHERE rs_id=? ;", [status, rs_id]) db.commit() db.close() return True else: db.close() return False
def store_session(self, DictionaryToStore): db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db) cursor = db.cursor() debug_log.info(DictionaryToStore) for key in DictionaryToStore: debug_log.info(key) try: cursor.execute("INSERT INTO session_store (code,json) \ VALUES (%s, %s)", (key, dumps(DictionaryToStore[key]))) db.commit() # db.close() except IntegrityError as e: cursor.execute("UPDATE session_store SET json=%s WHERE code=%s ;", (dumps(DictionaryToStore[key]), key)) db.commit() # db.close() db.close()
def change_rs_id_status(self, rs_id, status): db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db) cursor = db.cursor() query = cursor.execute("select * from rs_id_tbl where rs_id=%s;", (rs_id,)) result = cursor.fetchone() rs_id = result[0] used = result[1] debug_log.info(result) status_from_db = bool(used) status_is_unused = status_from_db is False if status_is_unused: cursor.execute("UPDATE rs_id_tbl SET used=%s WHERE rs_id=%s ;", (status, rs_id)) db.commit() cursor.close() return True else: cursor.close() return False
def storeToken(self, DictionaryToStore): """ Store token into database :param DictionaryToStore: Dictionary in form {"key" : "dict_to_store"} :return: """ db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db) cursor = db.cursor() for key in DictionaryToStore: try: cursor.execute("INSERT INTO token_storage (cr_id,token) \ VALUES (%s, %s)", (key, dumps(DictionaryToStore[key]))) db.commit() except IntegrityError as e: # Rewrite incase we get new token. cursor.execute("UPDATE token_storage SET token=? WHERE cr_id=%s ;", (dumps(DictionaryToStore[key]), key)) db.commit() db.close()
def store_session(self, DictionaryToStore): db = db_handler.get_db(self.db_path) try: db_handler.init_db(db) except OperationalError: pass debug_log.info(DictionaryToStore) for key in DictionaryToStore: debug_log.info(key) try: db.execute("INSERT INTO session_store (code,json) \ VALUES (?, ?)", [key, dumps(DictionaryToStore[key])]) db.commit() db.close() except IntegrityError as e: db.execute("UPDATE session_store SET json=? WHERE code=? ;", [dumps(DictionaryToStore[key]), key]) db.commit() db.close()
def add_surrogate_id_to_code(self, code, surrogate_id): """ Link code with a surrogate_id :param code: :param surrogate_id: :return: None """ db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db) cursor = db.cursor() debug_log.info("Code we look up is {}".format(code)) code = self.query_db("select * from codes where ID = %s;", (code,)) debug_log.info("Result for query: {}".format(code)) code_from_db = code code_is_valid_and_unused = "!" in code_from_db if (code_is_valid_and_unused): cursor.execute("UPDATE codes SET code=%s WHERE ID=%s ;", (surrogate_id, code)) db.commit() db.close() else: raise Exception("Invalid code")
def store_slr_JSON(self, json, slr_id, surrogate_id): """ Store SLR into database :param surrogate_id: :param slr_id: :param json: :return: """ db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db) cursor = db.cursor() debug_log.info( "Storing SLR '{}' belonging to surrogate_id '{}' with content:\n {}" .format(slr_id, surrogate_id, json)) cursor.execute( "INSERT INTO storage (surrogate_id,json,slr_id) \ VALUES (%s, %s, %s)", (surrogate_id, dumps(json), slr_id)) db.commit() db.close()
def storeJSON(DictionaryToStore): db = db_handler.get_db() try: db_handler.init_db(db) except OperationalError: pass debug_log.info(DictionaryToStore) for key in DictionaryToStore: debug_log.info(key) # codes = {"jsons": {}} # codes = {"jsons": {}} try: db.execute( "INSERT INTO storage (ID,json) \ VALUES (?, ?)", [key, dumps(DictionaryToStore[key])]) db.commit() except IntegrityError as e: db.execute("UPDATE storage SET json=? WHERE ID=? ;", [dumps(DictionaryToStore[key]), key]) db.commit()
def storeJSON(self, DictionaryToStore): """ Store SLR into database :param DictionaryToStore: Dictionary in form {"key" : "dict_to_store"} :return: """ db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db) cursor = db.cursor() debug_log.info("Storing dictionary:") debug_log.info(DictionaryToStore) for key in DictionaryToStore: debug_log.info("Storing key:") debug_log.info(key) try: cursor.execute("INSERT INTO storage (surrogate_id,json) \ VALUES (%s, %s)", (key, dumps(DictionaryToStore[key]))) db.commit() except IntegrityError as e: cursor.execute("UPDATE storage SET json=%s WHERE surrogate_id=%s ;", (dumps(DictionaryToStore[key]), key)) db.commit() db.close()
def query_db_multiple(self, query, args=(), one=False): ''' Simple queries to DB :param query: SQL query :param args: Arguments to inject into the query :return: all hits for the given query ''' db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db) cursor = db.cursor() cur = cursor.execute(query, args) if one: try: rv = cursor.fetchone() # Returns tuple debug_log.info(rv) if rv is not None: db.close() return rv # The second value in the tuple. else: return None except Exception as e: debug_log.exception(e) debug_log.info(cur) db.close() return None else: try: rv = cursor.fetchall() # Returns tuple debug_log.info(rv) if rv is not None: db.close() return rv # This should be list of tuples [(1,2,3), (3,4,5)...] else: return None except Exception as e: debug_log.exception(e) debug_log.info(cur) db.close() return None
def get_user_id_with_code(self, code): try: db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db) query = self.query_db( "select * from code_and_user_mapping where code=%s;", (code, )) debug_log.info(query) user_from_db = loads(query) return user_from_db except Exception as e: debug_log.exception(e) raise DetailedHTTPException( status=500, detail={ "msg": "Unable to link code to user_id in database", "detail": { "code": code } }, title="Failed to link code to user_id")
def storeSurrogateJSON(self, user_id, surrogate_id, operator_id): db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db) cursor = db.cursor() debug_log.info( "Mapping surrogate_id '{}' with user_id '{}' for operator '{}'". format(surrogate_id, user_id, operator_id)) try: cursor.execute( "INSERT INTO surrogate_and_user_mapping (user_id, surrogate_id, operator_id) \ VALUES (%s, %s, %s)", [user_id, surrogate_id, operator_id]) except Exception as e: debug_log.exception(e) debug_log.debug( "Storing surrogate_id into user/surrogate mapping FAILED," " likely surrogate_id assigned already.") db.commit() db.close()
def storeCSR_JSON(self, DictionaryToStore): cr_id = DictionaryToStore["cr_id"] rs_id = DictionaryToStore["rs_id"] surrogate_id = DictionaryToStore["surrogate_id"] slr_id = DictionaryToStore["slr_id"] json = DictionaryToStore["json"] db = db_handler.get_db(self.db_path) try: db_handler.init_db(db) except OperationalError: pass debug_log.info(DictionaryToStore) # debug_log.info(key) try: db.execute("INSERT INTO csr_storage (cr_id, surrogate_id, slr_id, rs_id, json) \ VALUES (?, ?, ?, ?, ?)", [cr_id, surrogate_id, slr_id, rs_id, dumps(json)]) db.commit() except IntegrityError as e: # db.execute("UPDATE csr_storage SET json=? WHERE cr_id=? ;", [dumps(DictionaryToStore[key]), key]) # db.commit() db.rollback() raise DetailedHTTPException(detail={"msg": "Adding CSR to the database has failed.",}, title="Failure in CSR storage", exception=e)
def verifyCode(self, code): db = db_handler.get_db(self.db_path) for code_row in self.query_db("select * from codes where ID = ?;", [code]): code_from_db = code_row["code"] return True return False