Jan-25-2022, 10:27 AM
Too many sudoers spoil the broth!
pymysql: insert query throws error
|
Jan-25-2022, 10:27 AM
Too many sudoers spoil the broth!
Jan-27-2022, 11:14 AM
Hi @wardancer84 ,
I had some time left and got interested in your problem. I also found a sudoers file on my laptop. It only contained "rules", so I played around with it. The important thing with relational databases is that the data is organized in tables and tables have columns. Column types can only be string, numeric or date (and some other special types). So the first step must be to break down your data in these elementary types. You cannot just insert a dictionary in a text column. Breaking down the data can be done as follows. from pysudoers import Sudoers # Variables for print_nested(). level = 0 tab = "\t" def print_nested(x: object) -> None: """ Print the data of a nested data stucture so it can be understood. """ global level if isinstance(x, str): print(f"{tab * level}{x}") elif isinstance(x, list): print(f"{tab * level}LIST:") level += 1 for i in x: print_nested(i) level -= 1 elif isinstance(x, dict): print(f"{tab * level}DICT:") level += 1 for k, v in x.items(): print_nested(f"{k} (key)") level += 1 print_nested(v) level -= 1 level -= 1 elif x == None: print_nested("None") else: print(f"Unknown type: {type(x)}") sobj = Sudoers(path="/home/ibreeden/tmp/sudoers-mylaptop") print("Rules") for rule in sobj.rules: print("DEBUG: sudoers-rule", rule) print("Decomposition:") print_nested(rule) Here we learn there are only string and None values. So we can design a table with these column types.You must realize this is only an example. You should really design your datamodel into more tables, related with foreign keys. Where you see a list or a dictionary in the breakdown is a candidate for a new table. It also depends on how you will be going to query the database. But to keep the example simple, I created one simple table, based on the break-down. And then the data can be inserted in that table. from pysudoers import Sudoers import sqlite3 def do_insert(tablename: str, columnnames: list, columnvalues: list) -> None: todo = "insert into " + tablename + " (" todo += ", ".join(columnnames) todo += ") values (" todo += ", ".join('?' * len(columnnames)) todo += ")" colvalues = tuple(columnvalues) print("DEBUG: ", todo, colvalues) cur.execute(todo, colvalues) def insert_nested(x: object, colnames: list = [], colvals: list = []) -> None: """ generate sql inserts for nested data stucture so it can be inserted in a table. """ ccolnames = colnames[:] # Deep copy. ccolvals = colvals[:] # Deep copy. if isinstance(x, str): ccolvals.append(x) do_insert("sudo_rules", ccolnames, ccolvals) elif isinstance(x, list): for i in x: insert_nested(i, ccolnames, ccolvals) elif isinstance(x, dict): for k, v in x.items(): if len(ccolnames) == len(ccolvals): insert_nested(v, ccolnames + [k], ccolvals) else: insert_nested(v, ccolnames + [k], ccolvals + [k]) elif x == None: insert_nested("NULL", ccolnames, ccolvals) else: print(f"Unknown type: {type(x)}") con = sqlite3.connect("sudoers.db") cur = con.cursor() # CREATE THE TABLE (ONCE). cur.execute("""create table if not exists sudo_rules (sudohost text, users text, hosts text, commands text, run_as text, tags text, command text) """) cur.execute("delete from sudo_rules") # For testing, start with blank table. con.commit() sobj = Sudoers(path="/tmp/sudoers-mylaptop") print("Rules") for rule in sobj.rules: print("DEBUG: sudoers-rule: ", rule) insert_nested(rule, ["sudohost"], ["mylaptop"]) con.commit() print() print("Contents of table") cur.execute("select * from sudo_rules") table = cur.fetchall() for row in table: print(row) cur.close() con.close() I hope this helps you with your problem.
Jan-28-2022, 06:48 AM
ah...very nice, thank you. i will post code here when the prototype is finished, might be usefull for someone.
|
|