SQL 清理 python
使用 python 时清理 SQL 以防止注入的最佳方法是什么？我正在使用 mysql 连接器。我读过，我应该使用类似于以下内容的结构：
import mysql.connector
connection = mysql.connector.connect(host="", port="", user="", password="", database="")
cursor = connection.cursor( buffered = True )
sql = "INSERT INTO mytable (column1, column2) VALUES (%s, %s)"
val = (myvalue1, myvalue2)
cursor.execute(sql, val)
connection.commit()
但是，我不明白为什么这可以防止注入。这足够了吗？用户可以向我介绍有关 myvalue1 或 myvalue2 的任何内容，即使不应该这样做。有什么有用的库吗？
What is the best way to sanitize a SQL to prevent injection when using python? I'm using mysql-connector. I have read that I should use a structure similar to:
import mysql.connector
connection = mysql.connector.connect(host="", port="", user="", password="", database="")
cursor = connection.cursor( buffered = True )
sql = "INSERT INTO mytable (column1, column2) VALUES (%s, %s)"
val = (myvalue1, myvalue2)
cursor.execute(sql, val)
connection.commit()
However, I don't understand why this can prevent an injection. Is this sufficient? A user could introduce me anything on myvalue1 or myvalue2, even if it is not suposed to. Is there any useful library?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
当不受信任的输入插入到 SQL 查询中并且输入包含更改查询语法的字符时，SQL 注入就会起作用。
查询参数与 SQL 查询分开，永远不会插入其中。参数值在解析后与 SQL 查询相结合，因此不再有任何机会更改语法。该参数保证被视为单个标量值（即，就好像它只是 SQL 表达式中的字符串文字）。
如果您使用 MySQLCursorPrepared 游标子类，这就是 Python 连接器的工作方式。请参阅 https://dev.mysql.com /doc/connector-python/en/connector-python-api-mysqlcursorprepared.html
否则，Python 连接器将“模拟”准备好的查询。实际上，它会在解析 SQL 查询之前将参数插入到 SQL 查询中，但它通过转义可能导致 SQL 注入的特殊字符来安全地这样做。它经过充分测试，因此可靠。
两种游标类型的使用方式相同，传递带有
%s占位符的 SQL 查询字符串，以及带有参数值元组的另一个参数。您正在正确使用它。回复 @Learningfrommasters 的评论：
是的，存储在数据库中的字符串可能会在另一个 SQL 查询中不安全地使用，并导致 SQL 注入。有些人认为只有用户输入才必须被安全地处理，但事实并非如此。 任何变量都应该被视为查询参数，无论该变量的值来自用户输入，还是从文件中读取，甚至是从您自己的数据库中提取。
示例：假设我的名字是 Bill O'Karwin。它有一个撇号，您知道它是 SQL 的特殊字符，因为它终止字符串文字。
如果我的名字存储在数据库中，然后将其提取到应用程序的变量
userlastname中，那么我可以搜索具有相同姓氏的其他人：这是不安全的，因为撇号会导致 SQL 注入。尽管该值不是直接来自用户输入，但它来自我自己的数据库。
因此，请对所有变量使用参数。那么你就不必考虑来源是否安全。
SQL injection works when untrusted input is interpolated into an SQL query and the input contains characters that change the syntax of the query.
Query parameters are kept separate from the SQL query, never interpolated into it. The values of the parameters are combined with the SQL query after it is parsed, so there is no longer any opportunity to change the syntax. The parameter is guaranteed to be treated as a single scalar value (i.e. as if it's just a string literal in an SQL expression).
This is the way the Python connector works if you use the
MySQLCursorPreparedcursor subclass. See https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursorprepared.htmlOtherwise, the Python connector "simulates" prepared queries. It actually does interpolate parameters into the SQL query before it is parsed, but it does so safely, by escaping special characters that would cause SQL injection. It is well-tested so it's reliable.
Both cursor types are used the same way, passing an SQL query string with
%splaceholders, and another argument with a tuple of parameter values. You are using it correctly.Re comment from @Learningfrommasters:
Yes, a string stored in your database can be used unsafely in another SQL query, and cause SQL injection. Some people think that only user input must be treated safely, but this is not true. Any variable should be treated as a query parameter, whether the value for that variable comes from user input, or read from a file, or even pulled out of your own database.
Example: Suppose my name is Bill O'Karwin. It has an apostrophe in it, which you know is a special character to SQL because it terminates a string literal.
If my name were stored in the database and then fetched into an application into a variable
userlastname, then I could search for other people with the same last name:That is unsafe because the apostrophe would cause SQL injection. Even though the value didn't come directly from user input, it came from my own database.
So use parameters for all variables. Then you don't have to think about whether the source is safe or not.