Python Dictionary and parameterized MySQL using pymysql (secure coding)

Question

I'm very new to doing database driven coding in python.. and python for that matter as well. I'm trying to write some code that will take the dictionary output, parameterize it (to avoid SQL injection as good practice,) and place the output into seperate columns in a mysql database. The code executes and outputs which tells me i'm obviously doing something wrong with my parameterization etc, which is primarily what i need help with.

something broke with the SQL thing
something broke with the SQL thing
something broke with the SQL thing
etc...

The dictionary output is as follows:

{'abx.com': ['abc.com', '103.245.222.133', '', 'alt3.aspmx.l.google.com', 'ns-331.awsdns-41.com', 'Australia', '', '', 1445889980]}
{'abd.com': ['abc.com', '12.27.179.65', '', '', 'g4.nstld.com', 'United States', '', '', 1445889980]}
{'abf.com': ['abc.com', '159.204.50.123', '', 'mx01.data-tronics.com', 'ns2.data-tronics.com', 'United States', '', '', 1445889980]}
{'abv.com': ['abc.com', '192.185.225.77', '', 'abv.com.inbound10.mxlogic.net', 'ns1085.hostgator.com', 'United States', '', '', 1445889980]}
{'bac.com': ['abc.com', '171.161.206.99', '', 'mxa-0000ec05.gslb.pphosted.com', 'ns12.bac.com', 'United States', '', '', 1445889980]}
{'acb.com': ['abc.com', '92.54.21.223', '', 'mx0.acb.com', 'ns-2008.awsdns-59.co.uk', 'Spain', '', '', 1445889980]}

The code is below:

#!/usr/bin/env python3.4

import subprocess
import pymysql
import json
import time

conn = pymysql.connect(host="localhost", user="myuser", passwd="superpass", db="dnstwist")
cur = conn.cursor()
epoch = int(time.time())
insert_sql =    "INSERT INTO domains(fakedomain, origdomain, a_record, aaaa_record, mx_record, ns_record, country, created, updated, epoch) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"

# dictionary key structure
# { fakedomain: [ site, A, AAAA, MX, NS, Country, Created, Updated ] } 

domain_dict = {}
domaininfo = []


def build_dict():
    sites = ['abc.com', 'nbc.com']                
    for site in sites:
        proc = subprocess.Popen(["python dnstwist.py -g -c " + site + " --threads 1 | grep -v ,,,,,,,, | sed -e '1,1d' "],shell=True,stdout=subprocess.PIPE,universal_newlines=True)

        while True:
            line = proc.stdout.readline()
            domaininfo = line.split(',')

            if line!= '':
                fakedomain = domaininfo[1]
                A = domaininfo[2]
                AAAA = domaininfo[3]
                MX = domaininfo[4]
                NS = domaininfo[5]
                country = domaininfo[6]
                created = domaininfo[7]
                updated = domaininfo[8]
                SSDEEP = domaininfo[9]

                try: 
                    domain_dict = { fakedomain: [ site, A, AAAA, MX, NS, country, created, updated, epoch ] }

                    try: 
                        cur.execute(insert_sql, (json.dumps(domain_dict)))
                        cur.commit()
                    except:  
                        print('something broke with the SQL thing')

                except:
                    print('you hit an exception')
                    continue

            else:
                print('you hit the break')
                break
    cur.close()
    conn.close()

build_dict()

The database structure is as follows:

CREATE TABLE domains(
    fakedomain INT PRIMARY KEY AUTO_INCREMENT NOT NULL,\
    origdomain TEXT NULL,\
    a_record TEXT NULL,\
    aaaa_record TEXT NULL,\
    mx_record TEXT NULL,\
    ns_record TEXT NULL,\
    country TEXT NULL,\
    created TEXT NULL,\
    updated TEXT NULL,\
    epoch INT(11) NULL);

taking the try/except out of the mix shows the following traceback:

Traceback (most recent call last):
  File "./twistdb.py", line 60, in <module>
    build_dict()
  File "./twistdb.py", line 45, in build_dict
    cur.execute(insert_sql, (json.dumps(domain_dict)))
  File "/usr/local/lib/python3.4/site-packages/pymysql/cursors.py", line 144, in execute
    query = self.mogrify(query, args)
  File "/usr/local/lib/python3.4/site-packages/pymysql/cursors.py", line 135, in mogrify
    query = query % self._escape_args(args, conn)
TypeError: not enough arguments for format string

Remove the try/except and let it fail - post the traceback that is printed. — alecxe, Oct 26 '15 at 20:18

score 2 · Accepted Answer · edited May 23 '17 at 10:27

Since you are using positional placeholders - make a list of parameters:

insert_sql = """
    INSERT INTO 
        domains
        (fakedomain, origdomain, a_record, aaaa_record, mx_record, ns_record, country, created, updated, epoch) 
    VALUES 
        (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
"""

params = [fakedomain, site, A, AAAA, MX, NS, country, created, updated, epoch]
cur.execute(insert_sql, params)

You should also think twice before wrapping your code blocks into try/except with a bare except. You are, at least, making the debugging and understanding of where and what the problem is harder. Also see:

Should I always specify an exception type in `except` statements?

Python Dictionary and parameterized MySQL using pymysql (secure coding)

1 Answers1