如何在`pyodbc`查询中放置变量? [英] How to put variable in `pyodbc` query?

问题描述

我正在使用 pyodbc 在 Microsoft SQL Server 中保存数据.

I am using pyodbc to save data in Microsoft SQL Server.

I 3 个字符串变量，user_first_name、user_last_name 和 profile_photo_path

I 3 string variables, user_first_name, user_last_name and profile_photo_path

user_first_name = 'Shams'
user_last_name = 'Nahid'
file_name = 'my_profile_photo_path'

现在当我尝试使用

cursor.execute(f'''
                    INSERT INTO pyUser.dbo.user_information (first_name, last_name, profile_photo_path)
                    VALUES
                    ({user_first_name}, {user_last_name}, {file_name})

                    ''')

我收到以下错误

无效的列名\'Shams\'.

Invalid column name \'Shams\'.

无效的列名 \'Nahid\'.

Invalid column name \'Nahid\'.

无法绑定多部分标识符directory.PNG".

The multi-part identifier "directory.PNG" could not be bound.

但不是变量，如果我把硬编码的字符串放在

But instead of the variable, if I put the hardcoded string

cursor.execute('''
                    INSERT INTO pyUser.dbo.user_information (first_name, last_name, profile_photo_path)
                    VALUES
                    ('my_user_first_name', 'my_user_last_name', 'my_file_name')

                    ''')

然后查询显示没有错误.

Then the query shows no error.

我检查了变量类型，user_first_name、user_last_name和file_name，都是

I checked the variable type, user_first_name, user_last_name and file_name, all are <class 'str'>

如何将变量放入查询中?

How can I put the variable in the query?

推荐答案

使用字符串格式将列值插入 SQL 语句是一种危险的做法，因为它会将您的代码暴露给SQL 注入漏洞.例如，您的答案中的代码适用于

Using string formatting to insert column values into an SQL statement is a dangerous practice because it exposes your code to SQL Injection vulnerabilities. For example, the code in your answer will work for

user_last_name = "Nahid"

但它会失败

user_last_name = "O'Connor"

SQL 注入也可能具有严重的安全隐患.在网络上搜索Little Bobby Tables"以查看示例.

SQL injection can also have serious security implications. Perform a web search for "Little Bobby Tables" to see an example of that.

相反，您应该像这样使用参数化查询:

Rather, you should use a parameterized query like so:

user_first_name = 'Shams'
user_last_name = 'Nahid'
file_name = 'my_profile_photo_path'

sql = '''\
INSERT INTO pyUser.dbo.user_information (first_name, last_name, profile_photo_path)
VALUES (?, ?, ?)
'''
params = (user_first_name, user_last_name, file_name, )
cursor.execute(sql, params)

这篇关于如何在`pyodbc`查询中放置变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！