Python字典和使用pymysql的参数化MySQL（安全编码）

Question

我对用python编写数据库驱动的代码非常陌生。。还有python。我正在尝试编写一些代码来获取字典输出，将其参数化（以避免SQL注入作为一个好的实践），并将输出放入mysql数据库中的单独列中。代码执行并输出，它告诉我参数化等明显有问题，这是我需要帮助的主要原因。

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

字典输出如下：

^{pr2}$

代码如下：

#!/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()

数据库结构如下：

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);

将try/except从组合中取出将显示以下回溯：

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

Answer 1

由于您使用的是位置占位符-请列出一个参数列表：

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) 

在用裸except将代码块包装到try/except之前，还应该三思而后行。至少，你让调试和理解哪里和什么问题更难。另请参见：

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