CODEWITHSUNDEEP
pythonmysql
vPilot
vPilot

Reputation: 48

Using Wildcards alongside special characters in SQL query via Python

Still a bit new to Python, so bear with me. I'm writing a Python script to perform an audit of several MySQL databases to specifically pull variable information with a directory in the value column. Simple query via MySQL is SHOW VARIABLES WHERE VALUE LIKE '/%';, which is what I'm trying to convert and get working in this script. The first issue appears to be declaring the % wildcard, since Python considers it a string, but on top of that is declaring the / since I'm looking for any variables with a directory.

Here is the section of script in question, followed by the error:

def dbQuery(server,username,password):
    conn = MySQLdb.connect(host=server, user=username, passwd=password, db="")
    cursor = conn.cursor(MySQLdb.cursors.DictCursor)
    cursor.execute("SHOW VARIABLES WHERE VALUE LIKE $s", ('%%/%',))
    result = cursor.fetchall()
    #Cleanup
    cursor.close()
    conn.close()

Traceback (most recent call last):
  File "./check-mysql-var.py", line 118, in <module>
    main()
  File "./check-mysql-var.py", line 109, in main
    ret = checkVar(options.server,uname,mysqlPass)
  File "./check-mysql-var.py", line 47, in checkVar
    result = dbQuery(server,username,password)
  File "./check-mysql-var.py", line 39, in dbQuery
    cursor.execute("SHOW VARIABLES WHERE VALUE LIKE $s", ('%%/%',))
  File "/usr/local/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 187, in execute
    query = query % tuple([db.literal(item) for item in args])
TypeError: not all arguments converted during string formatting

Thoughts on what is wrong with declaring the wildcard and character?

EDIT 1:

Using the raw strings info from @Noah, I made the adjustment to the variable for '/%' but it didn't seem to change anything error-wise. I used Skulpt to test a simple print of the variable and it formatted properly, but I'm still not there yet. I know this makes my script vulnerable, but I split off the query to a separate variable in the short term to test, too, and saw no change:

    v = "\t'/%'\n"
    query = """SHOW VARIABLES WHERE VALUE LIKE $s"""
    cursor.execute(query, (v))

EDIT 2:

After sleeping on it a bit, I think I'm closer but still missing something. I changed around the variable a bit and got a result, but not what I expected. So I am assuming I'm close but still missing a part somewhere. Here is what changed:

    v = "'/%\'"
    cursor.execute("SHOW VARIABLES WHERE VALUE LIKE %s", (v, ))

I know with Bash scripts you can run them with bash -x to debug and see what the output is of each command is within it. Can you do something like that with Python, and if so how? This may help me see what the script is putting in place of the %s.

Upvotes: 0

Views: 581

Answers (2)

vPilot
vPilot

Reputation: 48

I was able to resolve my issue. Turns out there were a few things going on in the overall script that were leading to my problem:

  • 1st, because of the way I was attempting to originally declare the wildcard variable, it continued to interfere. I got a recommendation from another source to use the conversion of the ASCII identifier for %. It required a few more variables declared, but I did get it working:
def dbQuery(server,username,password):
    conn = MySQLdb.connect(host=server, user=username, passwd=password, db="")
    cursor = conn.cursor(MySQLdb.cursors.DictCursor)
    v = chr(37)
    v2 = '/'
    v3 = v2 + v
    cursor.execute("SHOW VARIABLES WHERE VALUE LIKE %s", (v3, ))
    result = cursor.fetchall()
    #Cleanup
    cursor.close()
    conn.close()
    return result
  • 2nd, I was missing a return result at the end of my definition (see above), so the result was not being carried on through the rest of the script, thus triggering some failure conditions I have set up later on.

Upvotes: 0

Johols
Johols

Reputation: 136

If I understand correctly the reason it doesn't work is because the %s is a way for the method cursor.execute() to format the data sent by a variable. It decides whether the data sent in should be a string, int, or any other type according to MySQL. So if you have a variable var='hello world' and want to send that to the cursor.execute() you need to write

var = 'hello world'
cursor.execute("SHOW VARIABLES WHERE VALUE LIKE %s;", (var))
result = cursor.fetchall()
#Cleanup
cursor.close()
conn.close()

This checks if the input should be a string or not,

I hope I answered your question.

Upvotes: 1

Related Questions