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)