0

I'm trying to insert JSON data into an MySQL database:

def mapClients():
    for d in devices:
        clientMap =  d['dot11.device']['dot11.device.associated_client_map'].keys()
        for item in clientMap:
            clientList = kr.device_by_mac(item)
            times = kr.device_summary_since()
            for c in clientList:
                sqlMac =  c['kismet.device.base.macaddr'],
                sqlType = c['kismet.device.base.type'],
                sqlManuf = c['kismet.device.base.manuf'],
                ktime = c['kismet.device.base.last_time'],
                for t in ktime:
                    sqlTime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(t)) 
                    cur.execute("INSERT INTO devices(apID,mac,type,manuf,last_seen) VALUES(1,'" + str(sqlMac) + "','" + str(sqlType) + "','" + str(sqlManuf) + "','" + sqlTime + "');")
                    conn.commit()

mapClients()

This returns the following error:

pymysql.err.ProgrammingError: (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUES(1,'(u'58:E2:8F:CF:20:B3',)','(u'Wi-Fi Client',)','(u'Apple',)','20-10-201' at line 1")

I can see from the error that the various values are being suffixed with a 'u'. I understand through a lot of searching and learning that (I think) this means the data is unicode.

What I want to do is find a way of converting/decoding the data so the INSERT statements work. Some of the variables are tuples, some strings. Any help much appreciated.

Improve this question
4
  • 1
    for the columns who have (u'value') use variable[0]. like replace str(sqlMac) with str(sqlMac[0]) Commented Oct 20, 2018 at 18:17
  • @AbdullahRazzaki Thank you this got it working! I can't close this as answered unless you post that as an answer :) if you do I'll close it off. Great thinking and thanks again :) Commented Oct 20, 2018 at 18:56
  • 1
    You're welcome but my approach is incorrect because I overlooked the commas and tought you got tuples from the functions use @Martjin's approach. It's the right way Commented Oct 20, 2018 at 19:11
  • @AbdullahRazzaki Thank you. I'm having a look at this now with fresh eyes and a coffee :) Commented Oct 21, 2018 at 8:19

2 Answers 2

Reset to default
2

You are inserting tuples, not strings; remove the trailing commas:

sqlMac =  c['kismet.device.base.macaddr']
sqlType = c['kismet.device.base.type']
sqlManuf = c['kismet.device.base.manuf']
ktime = c['kismet.device.base.last_time']
sqlTime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(ktime))

It’s the trailing comma that turns those expressions into tuples, and str() on a tuple gives you the container Unicode string as the u'....' representation that then clashes with the ' quoting you are adding.

Note that removes the need to loop over ktime!

Next, you really want to use SQL parameters, not string concatenation. Use placeholders instead of '" + str(...) + "', and leave handling of quoting to the database adapter:

cur.execute("""
    INSERT INTO devices (apID, mac, type, manuf, last_seen)
    VALUES (1, %s, %s, %s, %s)
""", (sqlMac, sqlType, sqlManuf, sqlTime))

The %s are the placeholders; depending on your exact MySQL Python library, you may need to use ? questionmarks instead.

Not only would this let you avoid having to think about quoting, it also removes a serious security issue: the JSON you load could contain a SQL injection attack and SQL parameters are the best way to neutralise that attack vector.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Martjin thank you for your response, it's a really good answer with a lot of detail. If I remove the trailing commas (sqlMac = c['kismet.device.base.macaddr']) then I'll get a TypeError as you can't iterate over 'int' objects. The SQl parameters - fair enough, I'm using PyMySql. I'll look into that. For now I got this working by following the advice of @AbdullahRazzaki in the comment below my question.
@JamesPy PyMySQL uses %s placeholders, yes.
@JamesPy I suppose you're getting the error because ktime is an int now so you can just remove the for loop too and it should work. Remove the commas, I didn't notice the commas. @Martjin's right
@AbdullahRazzaki aha! That’s where there is an attempt at iteration.
@JamesPy with the latest updates the iteration issue should be resolved too. Sorry about that, my attention is a bit divided at the moment, I should have spotted that earlier.
|
-1

For the error message you posted, you have forgotten to place the closing parentheses before the VALUES in your SQL Query. The query should be like:

cur.execute("INSERT INTO devices(apID,mac,type,manuf,last_seen) VALUES(1,'" + str(sqlMac) + "','" + str(sqlType) + "','" + str(sqlManuf) + "','" + str(sqlTime) + "');")
Improve this answer

4 Comments

This doesn’t address the real problem with the quoting.
Yeah, but the error message must have changed with editing and correcting the parentheses, though! :-)
But not the one posted in OP.
@shahsani thank you for this. I posted in OP comments that the sql was incorrect, as I'd being chopping the code up throughout the day and had accidentally copied in some old code there!