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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.