New to MySQL, can't work out why code doesn't work

[kintarowonders]

I'm creating a script called rootrun, it runs things as root that were put in a MySQL database but only if they match regular expressions in the database. It should be telling me "hello word" but it doesn't.

The database is pretty simple:

MariaDB [rootrun]> SHOW TABLES;
+-------------------+
| Tables_in_rootrun |
+-------------------+
| allowed           |
| operations        |
+-------------------+
2 rows in set (0.001 sec)

MariaDB [rootrun]> SELECT * FROM allowed;
+----+------+
| id | rule |
+----+------+
|  1 | (.*) |
+----+------+
1 row in set (0.000 sec)

MariaDB [rootrun]> SELECT * FROM operations;
+----+------------------+
| id | oper             |
+----+------------------+
|  1 | echo hello world |
+----+------------------+
1 row in set (0.000 sec)

Keep in mind the regexp there is meant to just let everything through for now.

Here is the script which uses that database, but doesn't seem to be working. It doesn't return any output like it should.

#!/usr/bin/python3
import mysql.connector
import re
import os

global mydb

mydb = mysql.connector.connect(
        host="localhost",
        user="rootrun",
        passwd="4tFZYUfey8Lv5Vg3Gc7q8XE4",
        database="rootrun")

def allowExec(rootcmd):
    global mydb;
    cursor = mydb.cursor()
    cursor.execute("SELECT rule FROM allowed")
    result = cursor.fetchall()

    itran = False

    for rule in result:
        if (re.match(str(rule), str(rootcmd))):
            if (itran == False):
                os.system(rootcmd)
            itran = True


def runOperations():
    cursor = mydb.cursor()
    cursor.execute("SELECT id FROM operations")
    result = cursor.fetchall()

    for operid in result:
        cursor.execute("SELECT oper FROM operations WHERE id=\'" + str(operid) +"'")
        operation = cursor.fetchall()
        allowExec(operation)
        cursor.execute("DELETE FROM operations WHERE id=\'" + str(operid) +"'")

runOperations()

[buran]

1. You are not doing anything with the result of the cursor.execute() on line 35, e.g. fetch, print/return. etc.
2. even if you did, the result will not be what you expect. result (line 32) is tuple of tuples. So operid is tuple and str(operid) will have extra brackets, so your SELECT will return empty tuple

[kintarowonders]

The 'result' variable on line 32 is used in the foreach loop for operid. Can you demonstrate your point with code?

[buran]

The 'result' variable on line 32 is used in the foreach loop for operid. Can you demonstrate your point with code?

yes it is used in the for loop and operid (which is tuple) is used in second SELECT query, but you do nothing with the result of this second query

---

def runOperations():
    cursor = mydb.cursor()
    cursor.execute("SELECT id FROM operations")
    result = cursor.fetchall()
 
    for operid in result:
        print(operid)
        cmd = "SELECT oper FROM operations WHERE id=\'" + str(operid) +"'"
        print(cmd)
        cursor.execute(cmd)
        print(cursor.fetchall())

by the way the way you construct cmd is potentially dangerous. Use like this

cursor.execute("SELECT oper FROM operations WHERE id=%s", operid)

not tested, you may need '

[kintarowonders]

Alright, here is how I fixed it up, but for some reason the row isn't deleted by the database on line 48, and now I'm stuck there.

#!/usr/bin/python3
import mysql.connector
import re
import os
from itertools import chain

global mydb

mydb = mysql.connector.connect(
        host="localhost",
        user="rootrun",
        passwd="4tFZYUfey8Lv5Vg3Gc7q8XE4",
        database="rootrun")

def allowExec(rootcmd):
    global mydb;
    cursor = mydb.cursor()
    cursor.execute("SELECT rule FROM allowed")
    result = cursor.fetchall()

    itran = False

    lresult = list(result)

    for rule in lresult:
        frule = rule[0]
        if (re.match(str(frule), str(rootcmd[0]))):
            if (itran == False):
                os.system(rootcmd[0])
            itran = True

    print(itran)


def runOperations():
    global mydb
    cursor = mydb.cursor()
    cursor.execute("SELECT id FROM operations")
    result = cursor.fetchall()

    lresult = list(result)

    for operid in lresult:
        idstr = operid[0]
        cursor.execute("SELECT oper FROM operations WHERE id=\'" + str(idstr) +"'")
        operation = cursor.fetchall()
        allowExec(list(operation)[0])
        cursor.execute("DELETE FROM operations WHERE id=\'" + str(idstr) +"'")

runOperations()

[buran]

You need to commit the changes
add mydb.commit()

[kintarowonders]

Works now.. Thanks so much!

#!/usr/bin/python3
import mysql.connector
import re
import os
from itertools import chain

global mydb

mydb = mysql.connector.connect(
        host="localhost",
        user="rootrun",
        passwd="4tFZYUfey8Lv5Vg3Gc7q8XE4",
        database="rootrun")

def allowExec(rootcmd):
    global mydb;
    cursor = mydb.cursor()
    cursor.execute("SELECT rule FROM allowed")
    result = cursor.fetchall()

    itran = False

    lresult = list(result)

    for rule in lresult:
        frule = rule[0]
        if (re.match(str(frule), str(rootcmd[0]))):
            if (itran == False):
                os.system(rootcmd[0])
            itran = True

    print(itran)


def runOperations():
    global mydb
    cursor = mydb.cursor()
    cursor.execute("SELECT id FROM operations")
    result = cursor.fetchall()

    lresult = list(result)

    for operid in lresult:
        idstr = operid[0]
        cursor.execute("SELECT oper FROM operations WHERE id=\'" + str(idstr) + "'")
        operation = cursor.fetchall()
        allowExec(list(operation)[0])
        cursor.execute("DELETE FROM operations WHERE id=\'" + str(idstr) + "'")
        mydb.commit()

runOperations()