pymysql: insert query throws error

[wardancer84]

hi,

i',m trying to parse sudo files and inserting the results into a mysql table for eaysier analyzing an so on. parsing works well sof far, but pymysql will not let me do the insert.

the script:

#!/usr/bin/env python3


import sys
import os
import pprint
from collections import defaultdict
import pymysql
import pymysql.cursors
from SudoersLib import *

input_path="/admin/sudoers"
infiles= os.listdir(input_path)

connection = pymysql.connect(host='localhost',
                             user='sudodb',
                             password='sudodb',
                             database='sudoersdb',
                             cursorclass=pymysql.cursors.DictCursor)


for infile in infiles:
    infile=os.path.join(input_path, infile)
    sudo_obj = Sudoers(path=infile)
    file_name = os.path.basename(infile)
    sudohost = file_name.split('-')[1]
    results = defaultdict(list)
    sudo_host_list = []
    sudo_host_list.append(sudohost)
    results['sudohost'].append(sudo_host_list)

    for key in sudo_obj.host_aliases:
        host_alias_list = []
        host_alias_list.append((key, sudo_obj.host_aliases[key]))
        results['hostalias'].append(host_alias_list)
    for key in sudo_obj.cmnd_aliases:
        cmnd_alias_list = []
        cmnd_alias_list.append((key, sudo_obj.cmnd_aliases[key]))
        results['cmndalias'].append(cmnd_alias_list)
    for key in sudo_obj.runas_aliases:
        runas_alias_list = []
        runas_alias_list.append((key, sudo_obj.runas_aliases[key]))
        results['runasalias'].append(runas_alias_list)
    for key in sudo_obj.user_aliases:
        user_alias_list = []
        user_alias_list.append((key, sudo_obj.user_aliases[key]))
        results['useralias'].append(user_alias_list)
    for rule in sudo_obj.rules:
        users_list = []
        hosts_list = []
        rules_list = []
        users_list.append((rule["users"]))
        results['users'].append(users_list)
        hosts_list.append((rule["hosts"]))
        results['hosts'].append(hosts_list)
        rules_list.append((rule["users"], rule["hosts"], (rule["commands"])))
        results['commands'].append(rules_list)
        #print("%s|%s|%s|%s" % (sudohost, ",".join(rule["users"]), ",".join(rule["hosts"]), ",".join(map(str, rule["commands"]))))

        cursor = connection.cursor()
        qmarks = ",".join('?' * len(results))
        qry = "INSERT INTO rules (%s) VALUES (%s)" % (qmarks, qmarks)
        qvals = *results.keys(), *results.values()
        qvals_stringed = ','.join(map(str, qvals))
        print(type(qvals_stringed))
        print(qry, qvals_stringed)
        cursor.execute(qry, qvals_stringed)
        connection.commit()

example insert line printout:

<class 'str'>
INSERT INTO rules (?,?,?,?) VALUES (?,?,?,?) sudohost,users,hosts,commands,[['aixacodbt']],[[['root']]],[[['ALL']]],[[(['root'], ['ALL'], [{'run_as': ['ALL'], 'tags': None, 'command': 'ALL'}])]]

root@nimvie: /home/tremch/scripts # ./sudoers_parse.py
<class 'str'>
INSERT INTO rules (?,?,?,?) VALUES (?,?,?,?) sudohost,users,hosts,commands,[['aixacodbt']],[[['root']]],[[['ALL']]],[[(['root'], ['ALL'], [{'run_as': ['ALL'], 'tags': None, 'command': 'ALL'}])]]
Traceback (most recent call last):
  File "./sudoers_parse.py", line 67, in <module>
    cursor.execute(qry, qvals_stringed)
  File "/opt/freeware/lib/python3.7/site-packages/pymysql/cursors.py", line 161, in execute
    query = self.mogrify(query, args)
  File "/opt/freeware/lib/python3.7/site-packages/pymysql/cursors.py", line 140, in mogrify
    query = query % self._escape_args(args, conn)
TypeError: not all arguments converted during string formatting

Error:
<class 'str'>
INSERT INTO rules (?,?,?,?) VALUES (?,?,?,?) sudohost,users,hosts,commands,[['aixacodbt']],[[['root']]],[[['ALL']]],[[(['root'], ['ALL'], [{'run_as': ['ALL'], 'tags': None, 'command': 'ALL'}])]]
Traceback (most recent call last):
  File "./sudoers_parse.py", line 67, in <module>
    cursor.execute(qry, qvals_stringed)
  File "/opt/freeware/lib/python3.7/site-packages/pymysql/cursors.py", line 161, in execute
    query = self.mogrify(query, args)
  File "/opt/freeware/lib/python3.7/site-packages/pymysql/cursors.py", line 140, in mogrify
    query = query % self._escape_args(args, conn)
TypeError: not all arguments converted during string formatting

any tips welcome..

wbr

chris

[wardancer84]

nobody?

[ibreeden]

Hi @wardancer84 , I have a lot of troubles to understand the logic of your program. I cannot see what data you are getting from the sudoers environment. But for a start: placeholders are for values, not column names. So this is wrong:

INSERT INTO rules (?,?,?,?,?) VALUES (?,?,?,?,?)

It should be:

INSERT INTO rules (sudohost, users, hosts, commands, aixacodbt) VALUES (?,?,?,?,?)

... assuming I understood it right that these are de column names of your "rules" table.

Next problem: what the hell is this?

Output:
[[('TSM_SSI', ['/opt/tivoli/tsm/devices/bin/kill.acs_ssi', '/opt/tivoli/tsm/devices/bin/rc.acs_ssi *'])], [('SU_TSMWIN', ['/usr/bin/su - tsmwin'])], [('SU_TSMUNIX', ['/usr/bin/su - tsmunix'])], [('SU_TSMLIBMGR', ['/usr/bin/su - libmgr'])]], [['%storage_staff'], ['31tkr']], [['ALL'], ['ALL']], [[('%storage_staff', 'ALL', "{'run_as': ['ALL'], 'tags': ['NOPASSWD'], 'command': 'TSM_SSI'},{'run_as': ['ALL'], 'tags': ['NOPASSWD'], 'command': 'SU_TSMWIN'},{'run_as': ['ALL'], 'tags': ['NOPASSWD'], 'command': 'SU_TSMUNIX'},{'run_as': ['ALL'], 'tags': ['NOPASSWD'], 'command': 'SU_TSMLIBMGR'}")], [('31tkr', 'ALL', "{'run_as': ['ALL'], 'tags': ['NOPASSWD'], 'command': '/usr/bin/su -'}")]])

Assuming "qry" contains the insert statement I proposed, you should do something like this:

cursor.execute(qry, datatuple)

... where datatuple is a tuple of exactly the five values to be inserted.
It is possible to insert more than one row at a time. In that case you should use "executemany()" instead of "execute()" and you should provide a list of tuples, where each tuple again contains exactly the five values to be inserted.

But there are more things I don't understand.

for key in sudo_obj.host_aliases:
        host_alias_list = []
        host_alias_list.append((key, list(sudo_obj.host_aliases[key])))
        results['hostalias'].append(host_alias_list)

Each iteration of the "for" loop you initialize "host_alias_list" to an empty list. Then you append something to that list. So "host_alias_list" will only hold the result of the last appended item. Is that what you want?

[wardancer84]

hi,

thanks for answering. you are right, this needs more explaination.

First, i use python-sudoers for parsing sudoers files.
So this is part of the procedure of parsing various aspects of a sudoers file into python structures.

for key in sudo_obj.host_aliases:
        host_alias_list = []
        host_alias_list.append((key, list(sudo_obj.host_aliases[key])))
        results['hostalias'].append(host_alias_list)

this ist the output from the parsing library and represents a complete sudoers files. i added items= tablenames for clarification.

[['aixacodbt']] = sudohost 
[[['root']]] = users
[[['ALL']]] = hosts
[[(['root'], ['ALL'], [{'run_as': ['ALL'], 'tags': None, 'command': 'ALL'}])]] = commands

the next part prints out a complete insert statement...

qmarks = ",".join('?' * len(results))
        qry = "INSERT INTO rules (%s) VALUES (%s)" % (qmarks, qmarks)
        qvals = *results.keys(), *results.values()
        qvals_stringed = ','.join(map(str, qvals))
        print(type(qvals_stringed))
        print(qry, qvals_stringed)

<class 'str'>
INSERT INTO rules (?,?,?,?) VALUES (?,?,?,?) sudohost,users,hosts,commands,[['aixacodbt']],[[['root']]],[[['ALL']]],[[(['root'], ['ALL'], [{'run_as': ['ALL'], 'tags': None, 'command': 'ALL'}])]]

updated original post with slightly modified script and outputs from a more simple sudoers file.

wbr chris

[ibreeden]

Thanks for the explanation. I now see the result of:

qry = "INSERT INTO rules (%s) VALUES (%s)" % (qmarks, qmarks)

... is:

Output:
INSERT INTO rules (?,?,?,?) VALUES (?,?,?,?)

This is wrong. The first qmarks should be column_names so that the result would be:

Output:
INSERT INTO rules (sudohost,users,hosts,commands) VALUES (?,?,?,?)

Then I see "qvals_stringed" contains:

Output:
INSERT INTO rules (?,?,?,?) VALUES (?,?,?,?) sudohost,users,hosts,commands,[['aixacodbt']],[[['root']]],[[['ALL']]],[[(['root'], ['ALL'], [{'run_as': ['ALL'], 'tags': None, 'command': 'ALL'}])]]

That is also wrong. It should contain a tuple with four elements like this:

Output:
('aixacodbt', 'root', 'ALL',    "['root'], ['ALL'], [{'run_as': ['ALL'], 'tags': None, 'command': 'ALL'"  )

If you got that fixed you should be able to execute:

cursor.execute(qry, qvals_stringed)

... without error.

[wardancer84]

very helpful inputs...thanks for that.

i changed the script according to your suggestions...

#!/usr/bin/env python3


import sys
import os
import pprint
from collections import defaultdict
import pymysql
import pymysql.cursors
from SudoersLib import *

input_path="/admin/sudoers"
infiles= os.listdir(input_path)

connection = pymysql.connect(host='localhost',
                             user='sudodb',
                             password='sudodb',
                             database='sudoersdb',
                             cursorclass=pymysql.cursors.DictCursor)


for infile in infiles:
    infile=os.path.join(input_path, infile)
    sudo_obj = Sudoers(path=infile)
    file_name = os.path.basename(infile)
    sudohost = file_name.split('-')[1]
    results = defaultdict(list)
    sudo_host_list = []
    sudo_host_list.append(sudohost)
    results['sudohost'].append(sudo_host_list)

    for key in sudo_obj.host_aliases:
        host_alias_list = []
        host_alias_list.append((key, sudo_obj.host_aliases[key]))
        results['hostalias'].append(host_alias_list)
    for key in sudo_obj.cmnd_aliases:
        cmnd_alias_list = []
        cmnd_alias_list.append((key, sudo_obj.cmnd_aliases[key]))
        results['cmndalias'].append(cmnd_alias_list)
    for key in sudo_obj.runas_aliases:
        runas_alias_list = []
        runas_alias_list.append((key, sudo_obj.runas_aliases[key]))
        results['runasalias'].append(runas_alias_list)
    for key in sudo_obj.user_aliases:
        user_alias_list = []
        user_alias_list.append((key, sudo_obj.user_aliases[key]))
        results['useralias'].append(user_alias_list)
    for rule in sudo_obj.rules:
        users_list = []
        hosts_list = []
        rules_list = []
        users_list.append((rule["users"]))
        results['users'].append(users_list)
        hosts_list.append((rule["hosts"]))
        results['hosts'].append(hosts_list)
        rules_list.append((rule["users"], rule["hosts"], (rule["commands"])))
        results['commands'].append(rules_list)
        #print("%s|%s|%s|%s" % (sudohost, ",".join(rule["users"]), ",".join(rule["hosts"]), ",".join(map(str, rule["commands"]))))

        cursor = connection.cursor()
        qcolname = ",".join(map(str, results.keys()))
        qmarks = ",".join('?' * len(results))
        qry = "INSERT INTO rules (%s) VALUES (%s)" % (qcolname, qmarks)
        #qvals =  ",".join(map(str, results.values()))
        qvals = list(results.values())
        qvals_tupledx = tuple(qvals)
        #qvals_stringed = ','.join(map(str, qvals))
        #print(qvals_stringed)
        print(type(qvals_tupledx))
        print(qry, qvals_tupledx)
        #cursor.execute(qry, qvals_stringed)
        #connection.commit()

output is now a tuple and the sql string looks more or less ok..

<class 'tuple'>
INSERT INTO rules (sudohost,users,hosts,commands) VALUES (?,?,?,?) ([['aixacodbt']], [[['root']]], [[['ALL']]], [[(['root'], ['ALL'], [{'run_as': ['ALL'], 't
ags': None, 'command': 'ALL'}])]])

what puzzles me all time time with this kind of stuff...how to get rid of all those square brakets in the output as this
looks not right.

wbr

chris

[ibreeden]

what puzzles me all time time with this kind of stuff...how to get rid of all those square brakets in the output as this
looks not right.

No it does not look right. When I see [['aixacodbt']] I interpret it as a string in a list in a list. It needs unwrapping.

a = [['aixacodbt']]
b = a[0][0]
b
'aixacodbt'
print(b)
aixacodbt

You could unwrap these data before adding them to the tuple, but why are they wrapped in the first place? You should attack the problem at the source.

results = defaultdict(list)    # This designates the values to be lists
...
for key in sudo_obj.host_aliases:
        host_alias_list = []
        host_alias_list.append((key, sudo_obj.host_aliases[key]))  # host_alias_list is a list. Why double parentheses?? This would make a tuple of the contents of the list. Not useful in this stage.
        results['hostalias'].append(host_alias_list)   # Here you append the list to the list

You should add print statements to test what exactly happens in these steps. I have the impression it would be sufficient to do this:

for key in sudo_obj.host_aliases:
        results['hostalias'].append(sudo_obj.host_aliases[key])

I also tried to understand the sudoers object so I followed your link to python-sudoers but the examples all start with "from pysudoers import Sudoers" while you are doing: "from SudoersLib import *". I find no manuals on the usage of SudoersLib so I can't help with that.

[Pedroski55]

Are you not overly complicating a simple matter?

Admittedly, I am the only user on my laptop and I don't know much about this, but you are dealing with a simple text file.

My /etc/sudoers file looks like this in nano:

#
# This file MUST be edited with the 'visudo' command as root.
#
# Please consider adding local content in /etc/sudoers.d/ instead of
# directly modifying this file.
#
# See the man page for details on how to write a sudoers file.
#
Defaults env_reset
Defaults mail_badpass
Defaults secure_path="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/>

# Host alias specification

# User alias specification

# Cmnd alias specification

# User privilege specification
root ALL=(ALL:ALL) ALL

# Members of the admin group may gain root privileges
%admin ALL=(ALL) ALL

# Allow members of group sudo to execute any command
%sudo ALL=(ALL:ALL) ALL

# See sudoers(5) for more information on "#include" directives:

What do you want to get from this?

You could theoretically have 1 column for DEFAULTS and 5 columns for user, host, user_alias, group_alias, command in your db

root ALL=(ALL:ALL) ALL
The first field indicates the username that the rule will apply to (root).

root ALL=(ALL:ALL) ALL
The first “ALL” indicates that this rule applies to all hosts.

root ALL=(ALL:ALL) ALL
This “ALL” indicates that the root user can run commands as all users.

root ALL=(ALL:ALL) ALL
This “ALL” indicates that the root user can run commands as all groups.

root ALL=(ALL:ALL) ALL
The last “ALL” indicates these rules apply to all commands.

Can't you just parse the text file to a dictionary and put in your db?

[wardancer84]

Are you not overly complicating a simple matter?

might be, the thing is i have sudoers files with an insane linecount above 1000 with all variations/combinations of
aliases and rules, so i would say the whole matter is kind if supercomplicated.

[wardancer84]

I also tried to understand the sudoers object so I followed your link to python-sudoers but the examples all start with "from pysudoers import Sudoers" while you are doing: "from SudoersLib import *". I find no manuals on the usage of SudoersLib so I can't help with that.

its the same stuff, i just renamed and imported the file to make doing various edits easier.