MySQL参数化查询

2021-11-20 00:00:00 python mysql bind-variables

我很难使用 MySQLdb 模块将信息插入到我的数据库中.我需要在表中插入 6 个变量.

I am having a hard time using the MySQLdb module to insert information into my database. I need to insert 6 variables into the table. 

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

""")

有人可以帮我了解这里的语法吗?

Can someone help me with the syntax here?

推荐答案

当心对 SQL 查询使用字符串插值，因为它不会正确转义输入参数，并使您的应用程序容易受到 SQL 注入漏洞的影响.差异可能看起来微不足道，但实际上差别很大.

Beware of using string interpolation for SQL queries, since it won't escape the input parameters correctly and will leave your application open to SQL injection vulnerabilities. The difference might seem trivial, but in reality it's huge.

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 语句中参数的修饰符在不同的 DB API 实现之间有所不同，并且 mysql 客户端库使用 printf 样式语法而不是更普遍接受的 '?'标记(由例如 python-sqlite 使用).

It adds to the confusion that the modifiers used to bind parameters in a SQL statement varies between different DB API implementations and that the mysql client library uses printf style syntax instead of the more commonly accepted '?' marker (used by eg. python-sqlite).

相关文章