如何使用MySQLdb向MySQL插入整数和字符串值？

Question

我正在尝试用下面的代码插入包含整数和字符串的值：

def log_summoner(summonerId):
    c = ['profileIconId', 'summonerLevel', 'revisionDate', 'id', 'name']
    v = summonerId.values()

    con = connect(MYSQL['HOST'], MYSQL['USER'], MYSQL['PASSWORD'], MYSQL['DB'], charset='utf8')

    with con:
        cur = con.cursor()
        q = "INSERT IGNORE INTO "
        q += "summoners({0},{1},{2},{3},{4}) "
        q += "VALUES({5},{6},{7},{8},{9})"
        print 'c = ', c
        print 'v = ', v
        cur.execute(q.format(*(c+v)))

但是我收到了以下的输出和错误信息：

c =  ['profileIconId', 'summonerLevel', 'revisionDate', 'id', 'name']
v =  [627, 30, 1398712111000, 60783, u'TheOddOne']
Traceback (most recent call last):
  File "./game_crawler.py", line 245, in <module>
    log_summoner(s)
  File "./game_crawler.py", line 172, in log_summoner
    cur.execute(q.format(*(c+v)))
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1054, "Unknown column 'TheOddOne' in 'field list'")

这是表格“summoners”的描述：

mysql> DESCRIBE summoners;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| id            | bigint(20)   | NO   | PRI | NULL    |       |
| name          | varchar(255) | NO   |     | NULL    |       |
| profileIconId | smallint(6)  | NO   |     | NULL    |       |
| revisionDate  | bigint(20)   | NO   |     | NULL    |       |
| summonerLevel | tinyint(4)   | NO   |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

如果我把'TheOddOne'改成一个整数，它就能正常工作。我到底哪里出错了呢？谢谢大家！

Answer 1

你应该把'TheOddOne'作为一个字符串插入。要做到这一点，只需在{9}周围加上引号：

q += "VALUES({5},{6},{7},{8},'{9}')"

但是，这种方法容易受到SQL注入攻击。看看下面这个例子：

#Testing SQL Injection
print 'Testing SQL Injection'
c = ['\' or 1 = 1 or \'']
q = "SELECT * FROM summoners WHERE name = '{0}'"
query =  q.format(*c)
print query
cursor.execute(query)

if cursor.rowcount > 0:  
    print cursor.fetchall()
else:
    print "no item found"

这个方法会返回所有的记录。

最好的解决办法是使用参数化查询，像这样：

q = "INSERT IGNORE INTO summoners (profileIconId, summonerLevel, revisionDate, id, name) VALUES   (%s,%s,%s,%s,%s) "
cursor.execute(q, v)

不过，由于你需要动态插入列名，适合你的解决方案是将参数化查询和MySQLdb.escape_string结合起来：

q = "INSERT IGNORE INTO summoners({0},{1},{2},{3},{4}) VALUES (%s,%s,%s,%s,%s)".format(*c)
query =  MySQLdb.escape_string(q)
cursor.execute(query, v)