防止SQL注入和其他攻击：PREPARE、EXECUTE、parameterized queries

好，先来搞清楚我们到底在防什么，或者说——防谁。在这个level的开头我们已经提过SQL注入了，这可是数据库里最流行、最破坏力爆表的攻击之一。攻击方式其实很简单：黑客往你的SQL请求里塞恶意SQL代码，想“骗过”你的程序，拿到本不该看的数据。现在我们来详细聊聊这事儿。

SQL注入的例子

假设你有个web应用，用户只要输入用户名和密码就行。后端会执行一个SQL查询，看看数据库里有没有这个用户：

SELECT * FROM users WHERE username = 'admin' AND password = 'password123';

这个查询看起来没毛病……只要用户输入的都是正常数据。但如果有人输入：

• 用户名：admin' --
• 密码：（这个字段留空）

最终SQL就变成了这样：

SELECT * FROM users WHERE username = 'admin' -- AND password = 'password123';

注意--，在SQL里是注释的意思，后面全都被忽略了。结果就是密码校验直接被跳过，黑客就能直接以管理员身份登录！

SQL注入的潜在后果

SQL注入可能带来一堆糟糕的后果：

1. 未授权访问数据。比如黑客能看到用户的密码等敏感信息。
2. 删除或篡改数据。有人可能直接把表删了，或者把数据搞乱。
3. 执行任意SQL代码。想象一下黑客执行DROP DATABASE……简直噩梦。

不过我们可不怕！PostgreSQL给了我们一堆工具，可以帮我们防住这些攻击。

怎么防？防止SQL注入的方法

1. 用prepared statements（PREPARE和EXECUTE）

prepared statements就像你SQL代码的“安全食谱”。它的原理是：你先“准备”好SQL语句，然后把数据单独传进去。这样恶意代码根本插不进去。

正确做法的例子：

用PREPARE准备查询。

PREPARE user_login (text, text) AS
SELECT * 
FROM users 
WHERE username = $1 AND password = $2;

这个查询有两个参数$1和$2，等着你后面传真实的值。

用EXECUTE执行查询。

EXECUTE user_login('admin', 'password123');

这样PostgreSQL会自动把所有用户数据转义，恶意SQL根本插不进来。

这种方式的好处：

• SQL注入根本搞不成，因为参数只当普通数据处理，不会变成SQL代码的一部分。
• 查询更安全，执行速度也更快，因为执行计划会被缓存。

2. 参数化查询（Parameterized Queries）

这个方法在用Python、Java等语言写应用时特别常用。你不用手动写PREPARE和EXECUTE，直接用库或者ORM自动帮你处理参数。

Python配合psycopg2库的例子：

import psycopg2

connection = psycopg2.connect(
    dbname="your_db",
    user="your_user",
    password="your_password",
    host="localhost",
    port="5432"
)

cursor = connection.cursor()

# 用参数化查询
username = "admin"
password = "password123"
query = "SELECT * FROM users WHERE username = %s AND password = %s"
cursor.execute(query, (username, password))

# 你的数据绝对安全！
result = cursor.fetchall()
print(result)

注意SQL里的%s——参数就插在这里。psycopg2库会自动帮你安全传递数据。

3. 校验输入数据

如果你要用用户输入的数据，记得先检查下它们是不是你想要的。比如：

• 文本数据可以用正则表达式，确保没有非法字符。
• 数字数据要确保真的是数字。

Python例子：

import re

username = input("请输入用户名: ")

# 只允许字母、数字和下划线
if re.match(r"^\w+$", username):
    print("用户名没问题")
else:
    print("危险的用户名！")

4. 最小权限原则

确保你用来执行SQL的角色权限越小越好。比如，没必要就别给DROP TABLE或ALTER TABLE权限。

5. 记录可疑操作

你可以用PostgreSQL的参数来追踪用户的操作：

• log_statement = 'all' —— 记录所有SQL请求。
• log_connections = on —— 记录所有数据库连接。

这些设置能帮你发现潜在的恶意行为。

实现例子

例子1：用SQL写prepared statement

-- 创建prepared statement
PREPARE check_credentials (text, text) AS
SELECT * FROM users WHERE username = $1 AND password = $2;

-- 用安全参数执行查询
EXECUTE check_credentials('admin', 'password123');

例子2：Python里的参数化查询

query = "UPDATE users SET last_login = NOW() WHERE username = %s"
username = "admin"
cursor.execute(query, (username,))

安全建议

永远校验输入数据。 千万别相信用户传来的任何数据。

只用prepared statements或参数化查询。 这俩是你防SQL注入的主力。

角色权限只给最小需要的。 万一被黑客拿到，损失也能降到最低。

配置日志，定期查日志。 随时掌握数据库的动向。

SQL注入确实挺吓人，但只要你用prepared statements、参数化查询，再加上好习惯，你的数据库就很安全，晚上睡觉都不用担心有人“顺手”把你表全删了。PostgreSQL都给你准备好了，冲吧！