MySQL参数化查询

Question

我在使用MySQLdb模块向数据库插入信息方面遇到了麻烦。我需要将6个变量插入到表中。

cursor.execute ("""
    INSERT INTO Songs (SongName, SongArtist, SongAlbum, SongGenre, SongLength, SongLocation)
    VALUES
        (var1, var2, var3, var4, var5, var6)

""")

有人能帮我看一下这里的语法吗？

- Specto

Answer 1

不要使用字符串插值来构建SQL查询，因为它不能正确转义输入参数，并会使你的应用程序容易受到SQL注入漏洞攻击。 虽然差别看起来微不足道，但实际上差别很大。

存在安全问题的错误做法

c.execute("SELECT * FROM foo WHERE bar = %s AND baz = %s" % (param1, param2))

正确的写法（含转义）

c.execute("SELECT * FROM foo WHERE bar = %s AND baz = %s", (param1, param2))

SQL语句中用于绑定参数的修饰符在不同的数据库API实现中有所不同，而且mysql客户端库使用的是printf风格的语法，而不是更常见的“？”标记（例如python-sqlite中使用的方式），这增加了混淆。

- Emil H

---

在我看来，始终坚持正确和安全的实现方式是有意义的。这可以养成正确的编程习惯和良好的文化。此外，没有人知道你编写的代码将来会如何被使用；有人可能会在其他系统或网站中后续使用它。 - Roman Podlinov

来自php/pdo的我对printf风格的%s标记感到非常困惑，我有点害怕自己写出了易受攻击的查询语句。谢谢你消除了我的疑虑！ :) - Darragh Enright

当只有一个参数时，记得要加逗号：c.execute("SELECT * FROM foo WHERE bar = %s", (param1,))。 - Marius Lian

关于游标执行中参数的上下文（以及为什么仍然需要％s），MySQLdb游标的API参考在http://mysql-python.sourceforge.net/MySQLdb-1.2.2/public/MySQLdb.cursors.BaseCursor-class.html#execute。 - RedBarron

如果我没错的话，这也是我们向记录器函数（如info、error等）中插入字符串的方式。 - Ciasto piekarz

Answer 2

你有几种选择。你需要熟悉Python的字符串插值。这是一个术语，将来你想了解这样的东西时可能会更加成功地搜索到。

更适合查询：

some_dictionary_with_the_data = {
    'name': 'awesome song',
    'artist': 'some band',
    etc...
}
cursor.execute ("""
            INSERT INTO Songs (SongName, SongArtist, SongAlbum, SongGenre, SongLength, SongLocation)
            VALUES
                (%(name)s, %(artist)s, %(album)s, %(genre)s, %(length)s, %(location)s)

        """, some_dictionary_with_the_data)

考虑到你可能已经把所有的数据都放在对象或字典中了，第二种格式会更适合你。而且当你在一年后需要回来更新这个方法时，统计字符串中"%s"的出现次数非常麻烦。

- Trey Stout

---

看起来在 SQL 语句中，如果一个给定的绑定变量需要在多个地方使用，那么字典方法的效果更好。采用位置方法时，我们需要将变量传递多次，这并不是很理想。 - codeforester

Answer 3

链接的文档提供了以下示例：

   cursor.execute ("""
         UPDATE animal SET name = %s
         WHERE name = %s
       """, ("snake", "turtle"))
   print "Number of rows updated: %d" % cursor.rowcount

所以你只需要将这个示例代码适应到你自己的代码中：

cursor.execute ("""
            INSERT INTO Songs (SongName, SongArtist, SongAlbum, SongGenre, SongLength, SongLocation)
            VALUES
                (%s, %s, %s, %s, %s, %s)

        """, (var1, var2, var3, var4, var5, var6))

< p >（如果 SongLength 是数字，您可能需要使用 %d 而不是 %s）。< /p >

- Marcel Guzman

---

这个会起作用吗？如果var1和var2包含像“或'这样的字符。 - sheki

据我所知，无论哪种类型，你都必须使用“%s”进行格式化。@sheki：是的。 - ThiefMaster

Answer 4

实际上，即使您的变量（SongLength）是数字，仍然需要使用％s格式化它以正确绑定参数。如果尝试使用％d，则会出现错误。以下是来自此链接的小节选http://mysql-python.sourceforge.net/MySQLdb.html：

要执行查询，首先需要一个游标，然后可以在其上执行查询：

c=db.cursor()
max_price=5
c.execute("""SELECT spam, eggs, sausage FROM breakfast
          WHERE price < %s""", (max_price,))

在这个例子中，max_price = 5。那么为什么在字符串中使用%s呢？因为MySQLdb会将其转换为SQL字面值，即字符串“5”。完成后，查询实际上会说“...WHERE price < 5”。

- daSong

---

是的，这确实很奇怪...你认为“printf”格式应该意味着...实际上是printf格式，而不仅仅是在任何地方使用％s。 - fthinker

Answer 5

作为选择答案的替代方案，并具有Marcel所选择的安全语义，以下是一种使用Python字典指定值的紧凑方式。它的好处在于很容易修改，可以随着插入或删除列而进行：

  meta_cols = ('SongName','SongArtist','SongAlbum','SongGenre')
  insert = 'insert into Songs ({0}) values ({1})'.format(
      ','.join(meta_cols), ','.join( ['%s']*len(meta_cols)))
  args = [ meta[i] for i in meta_cols ]
  cursor = db.cursor()
  cursor.execute(insert,args)
  db.commit()

meta 是包含需要插入的值的字典。可以通过相同的方式进行更新:

  meta_cols = ('SongName','SongArtist','SongAlbum','SongGenre')
  update='update Songs set {0} where id=%s'.
        .format(','.join([ '{0}=%s'.format(c) for c in meta_cols ]))
  args = [ meta[i] for i in meta_cols ]
  args.append(songid)
  cursor=db.cursor()
  cursor.execute(update,args)
  db.commit()

- FDS

---

我印象深刻，你竟然能把 Python 代码写得这么难以阅读！ - Iharob Al Asimi

Answer 6

第一种解决方案很好。我想在这里添加一个小细节。确保您要替换/更新的变量将必须是str类型。我的mysql类型是decimal，但我不得不将参数变量设置为str才能执行查询。

temp = "100"
myCursor.execute("UPDATE testDB.UPS SET netAmount = %s WHERE auditSysNum = '42452'",(temp,))
myCursor.execute(var)

- Parth Shah

Answer 7

这里有另一种方法可以实现。它在MySQL官方网站上有详细的文档。 https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html

从本质上讲，它使用了与@Trey Stout答案相同的机制。但是，我发现这个更漂亮、更易读。

insert_stmt = (
  "INSERT INTO employees (emp_no, first_name, last_name, hire_date) "
  "VALUES (%s, %s, %s, %s)"
)
data = (2, 'Jane', 'Doe', datetime.date(2012, 3, 23))
cursor.execute(insert_stmt, data)

为了更好地说明变量的必要性：

注意进行的转义。

employee_id = 2
first_name = "Jane"
last_name = "Doe"

insert_stmt = (
  "INSERT INTO employees (emp_no, first_name, last_name, hire_date) "
  "VALUES (%s, %s, %s, %s)"
)
data = (employee_id, conn.escape_string(first_name), conn.escape_string(last_name), datetime.date(2012, 3, 23))
cursor.execute(insert_stmt, data)

- Djidiouf