数据库 Security
Protect 你的数据库: SQL injection, access control, encryption, and auditing.
你将学到
- SQL injection — 大多数常见数据库 attack and 如何 prevent 它
- Using parameterized 查询 / prepared 语句
- 原则的 最少 privilege 使用 roles and grants
- Encrypting sensitive 数据 at REST and in transit
- Auditing, backup, and recovery 最佳实践
概念讲解
SQL 注入
SQL injection is the #1 database security risk. It occurs when user input is concatenated into a SQL string: "SELECT * FROM users WHERE name = '" + userInput + "'". If userInput is ' OR '1'='1, the query becomes SELECT * FROM users WHERE name = '' OR '1'='1 — returning every user. Worse, '; DROP TABLE users; -- can destroy your database. The fix is always to use parameterized queries (also called prepared statements), where the database separates the SQL template from the values: query("SELECT * FROM users WHERE name = $1", [userInput]). The database never interprets values as SQL.
Principle of Least Privilege
Create database roles with the minimum permissions needed. The application's database user should not be able to DROP tables or access other databases. Use GRANT SELECT, INSERT, UPDATE ON 顺序 TO app_role — not GRANT ALL ON *.* TO app_role. Separate read-only roles for reporting from read-write roles for the application. Never use the superuser (postgres, root) as your application's database user.
Encryption
- In transit — always connect over TLS/SSL. Never allow unencrypted connections to a production database.
- At rest — enable database-level encryption (Transparent Data Encryption in SQL Server/Oracle, or disk-level encryption with LUKS/eBPF). For specific sensitive columns (SSNs, credit cards), use column-level encryption or a vault service.
- Passwords — never store passwords in plain text or with reversible encryption. Use a password hashing function (bcrypt, scrypt, Argon2) with a salt.
Auditing and Backups
Enable audit logging to track who accessed or modified what data and when. This is essential for compliance (GDPR, HIPAA, SOC 2) and for forensic investigation after an incident. Maintain a backup strategy: full backups regularly, point-in-time recovery (via WAL archiving in PostgreSQL or binlog in MySQL), and test restores — an untested backup is no backup. Store backups encrypted and off-site.
Connection Security
Use connection string secrets from a secrets manager, not hardcoded in source or environment files. Restrict network access to the database (firewall, security groups, private network). Use connection pooling with authentication. Rotate credentials regularly and use short-lived credentials where possible (e.g. IAM database authentication on AWS).
示例代码
-- WRONG: SQL injection via string concatenation
-- query("SELECT * FROM users WHERE email = '" + email + "'")
-- If email = "x'; DROP TABLE users; --" -> disaster!
-- RIGHT: parameterized query (PostgreSQL / Node.js pg)
-- const res = await pool.query(
-- 'SELECT * FROM users WHERE email = $1', [email]
-- );
-- Create a restricted role for the application
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_random_password';
GRANT SELECT, INSERT, UPDATE ON users, orders, order_items TO app_user;
GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO app_user;
-- app_user CANNOT DROP tables or access other databases
-- Create a read-only role for reporting
CREATE ROLE report_reader WITH LOGIN PASSWORD 'another_secure_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO report_reader;
-- Force SSL connections (PostgreSQL)
-- In postgresql.conf:
-- ssl = on
-- ssl_cert_file = 'server.crt'
-- ssl_key_file = 'server.key'
-- Enable audit logging (PostgreSQL)
-- log_statement = 'all'
-- log_connections = on
-- log_disconnections = on
Parameterized 查询到 prevent injection, 最少-privilege roles (app_user and report_reader), SSL 配置, and audit logging 设置.
动手试一试
- SQL Escaper
Escape 特殊 characters — 但 prefer parameterized 查询.
- 密码 Generator
Generate strong 密码 for 数据库 roles.
常见错误
错误写法
Concatenating 用户输入到 SQL 字符串
正确做法
这个是 SQL injection. ALWAYS use parameterized 查询 / prepared 语句. 数据库 separates code 从数据, 制作 injection 不可能.
错误写法
Using 数据库 superuser (postgres/根) as app 连接
正确做法
创建 dedicated role 使用最小值需要 privileges (SELECT, INSERT, UPDATE on 特定表). superuser 能 DROP 数据库 and bypass 所有 security.
练习 数据库 Security
2 个练习
查阅不认识的术语
新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。
动手做项目
通过引导式项目应用所学,含起始代码与解决方案。
解读错误信息
用大白话解释常见错误——什么意思、为什么发生、如何修复。