def safe_query_tables(conn, db): """ safely show tables in schema Args: * [conn] connection of sql * [db] database name Returns: * [rows] dict, the execution results * [sql] str, the command of sql """ res = _sql_args_check(db) if res is False: raise Exceptions.SQLInjectionException( f"SQL injection detected, params: db[{db}]") if conn is None: raise Exceptions.InvalidParamException("Conn cannot be null") # generating sql str_sql = f"SHOW TABLES IN `{db}`" # executing sql final_results = [] for i in PySQLConnection.query(conn, str_sql): # obtaining a list for k, v in i.items(): final_results.append(v) return final_results
def safe_query_columns(conn, db, table): """ safely show columns in table Args: * [conn] connection of sql * [db] database name * [table] table name Returns: * [rows] dict, the execution results * [sql] str, the command of sql """ res = _sql_args_check(db, table) if res is False: raise Exceptions.SQLInjectionException( f"SQL injection detected, params: db[{db}] table[{table}]") if conn is None: raise Exceptions.InvalidParamException("Conn cannot be null") # generating sql str_sql = f'SHOW COLUMNS IN `{db}`.`{table}`' # executing sql return PySQLConnection.query(conn, str_sql) # obtaining a list
def safe_delete(conn, db, table, item_id, debug=False): """ safely deleting row in table Args: * [conn] connection of sql * [db] database name * [table] table name * [item_id] the item id want to delete Returns: * [sql] str, the command of sql """ res = _sql_args_check(table, item_id) if res is False: raise Exceptions.SQLInjectionException( f"SQL injection detected, params: table[{table}] item_id[{item_id}]" ) if conn is None: raise Exceptions.InvalidParamException("Conn cannot be null") # generate sql str_sql = f"DELETE FROM `{db}`.`{table}` WHERE `id` = '{item_id}'" if debug: # for deubg print(str_sql) # executing sql return PySQLConnection.execute(conn, str_sql)
def update_id(self, new_id): if type(new_id) is int: self.lastid = new_id elif type(new_id) is str: self.lastid = int(new_id) else: raise excepts.InvalidParamException( "given id must be string or integer")
def cherry_pick(sets: list, item: object): """ cherry pick item from sets. For example [1, 2, 3, 4, 5]. After calling cherry_pick(sample, 3). The rest should be: sample = [1, 2, 4, 5] """ from siki.basics import Exceptions if not sets or len(sets) <= 0: raise Exceptions.InvalidParamException("the sets is empty") if not isinstance(item, type(sets[0])): raise Exceptions.InvalidParamException("the type of element to pick from sets is invalid") for index in range(0, len(sets)): if sets[index] == item: # element found return sets.pop(index) return None
def safe_insert(conn, db, table, args, debug=False): """ safely inserting database, and avoid sql injection attack Args: * [conn] connection of sql * [db] database name * [table] table name * [args(dict)] the data to insert, something like: {id:1, key1:val1, key2:val2, ...} * [debug] default to False, if you wannar to see the output sql statement, make it to True Returns: * [sql] str, command of sql """ res = _sql_args_check(db, table, args.keys(), args.values()) if res is False: raise Exceptions.SQLInjectionException( f"SQL injection detected, params: table[{table}], args[{args}]") if conn is None: raise Exceptions.InvalidParamException("conn cannot be null") if type(args) is not dict: raise Exceptions.InvalidParamException("args must be dict type") # generate a insert sql command keys = "`" + "`, `".join(args.keys()) + "`" values = "'" + "', '".join(args.values()) + "'" # generate insert sentence str_sql = f"INSERT INTO `{db}`.`{table}` ({keys}) VALUES ({values})" if debug: # for debug only print(str_sql) # executing sql command return PySQLConnection.execute(conn, str_sql)
def _check_id_valid(self, leaf_id): if not isinstance(leaf_id, type(self._item_id)): raise Exceptions.InvalidParamException( "leaf id must be the same type of TreeNode id") if leaf_id == self._item_id: return False for leaf in self._item_leaves: if leaf.self_id() == leaf_id: return False return True
def encode(self, data: object): """ convert given data into redis token Args: * [data(any)] data can be anything, bytes, int, float or whatever Returns: * [token(bytes)] composed token consisted of data, type, timestamp """ # reset self.reset() if data is None: raise excepts.NullPointerException("data cannot be a null type") # 1. create timestamp self.timestamp = ticker.time_since1970() # 2. assign bytes to self.data if type(data) is bytes: self.dtype = "bytes" self.data = convert.binary_to_string(base64.b64encode(data)) else: # other types self.data = data if type(data) is int: self.dtype = "int" elif type(data) is float: self.dtype = "float" elif type(data) is str: self.dtype = "str" elif type(data) is list: self.dtype = "list" elif type(data) is dict: self.dtype = "dict" else: raise excepts.InvalidParamException( "{} cannot be processed into string nor bytes".format( type(data))) # 3. composing a token dictionary type self.token = { "data": self.data, "type": self.dtype, "timestamp": self.timestamp } # return to caller return convert.dict_to_binary(self.token)
def merge(self, queue): """ merge two queues into one """ try: self.lock.acquire() if type(queue) is list or type(queue) is Queue: for item in queue: self.enqueue(item) else: raise Exceptions.InvalidParamException("cannot merge a non-list or non-queue type") finally: self.lock.release()
def search_delete(self, leaf_id): if not isinstance(leaf_id, type(self._item_id)): raise Exceptions.InvalidParamException( "leaf id must be the same type of TreeNode id") leaf = self.search_tree(leaf_id) if leaf is None: return None # nothing to delete root = leaf._item_root if isinstance(root, CommonTreeNode): return ListExtern.cherry_pick(root._item_leaves, leaf) else: # the leaf is not real leaf, it is the root of tree return self
def safe_simple_query(conn, db, table, select_con, where_con=None, order_by=None, debug=False): """ safely simple querying, not allow nested querying to avoid sql injection. Args: * [conn] connection of sql * [db] database name * [table] table name * [select_con] selection condition * [where_con] where condition * [order_by] order by command, default sequency is asc, if you want a desc results, append "DESC" to your command * [debug] default to False, if you wannar to see the output sql statement, make it to True Returns: * [rows(dict/list)] dict, the execution results """ res = _sql_args_check(db, table, select_con, where_con, order_by) if res is False: raise Exceptions.SQLInjectionException( f"SQL injection detected, params: table[{table}] select[{select_con}] where[{where_con}] order[{order_by}]" ) if conn is None: raise Exceptions.InvalidParamException("Conn cannot be null") # generate simple querying str_sql = f'SELECT {select_con} FROM `{db}`.`{table}`' if where_con is not None: str_sql += f' WHERE {where_con}' if order_by is not None: str_sql += f' ORDER BY {order_by}' if debug: # for debug only print(str_sql) # executing sql return PySQLConnection.query(conn, str_sql)
def search_tree(self, leaf_id): if not isinstance(leaf_id, type(self._item_id)): raise Exceptions.InvalidParamException( "leaf id must be the same type of TreeNode id") if self._item_id == leaf_id: # found return self for leaf in self._item_leaves: # this node is not we want if leaf.self_id() == leaf_id: # found return leaf else: if len(leaf) > 0: sub_leaf = leaf.search_tree(leaf_id) if isinstance(sub_leaf, CommonTreeNode): return sub_leaf return None # nothing found
def safe_update(conn, db, table, item_id, args, debug=False): """ safely updating database, and avoid sql injection attack Args: * [conn] connection of sql * [db] database name * [table] table name * [item_id] the item id want to update * [args(dict)] the data to update, something like: {id:1, key1:val1, key2:val2, ...} * [debug] default to False, if you wannar to see the output sql statement, make it to True Returns: * [sql] str, the command of sql """ res = _sql_args_check(db, table, item_id, args.keys(), args.values()) if res is False: raise Exceptions.SQLInjectionException( f"SQL injection detected, params: table[{table}] item_id[{item_id}] vals[{args}]" ) if conn is None: raise Exceptions.InvalidParamException("Conn cannot be null") # generate sql querying setval = [] for key, val in args.items(): if val: setval.append("`" + str(key) + "`='" + str(val) + "'") else: setval.append("`" + str(key) + "`=NULL") p_vals = ", ".join(setval) # generate sql str_sql = f"UPDATE `{db}`.`{table}` SET {p_vals} WHERE `id`='{item_id}'" if debug: # for debug only print(str_sql) # executing sql return PySQLConnection.execute(conn, str_sql)