什么是参数化查询？
参数化查询是一种将 SQL 语句的代码部分（结构）和数据部分（值）分开处理的技术。
-
传统字符串拼接查询：你需要在应用程序代码中手动将变量值拼接到 SQL 字符串中。
// 错误且危险的做法 string username = "admin"; string password = "' OR '1'='1"; // 恶意输入 string sql = "SELECT * FROM Users WHERE Username = '" + username + "' AND Password = '" + password + "'";
-
参数化查询：你定义一个 SQL 语句模板，然后使用参数（占位符）来代表数据，之后，你再将具体的值（参数）安全地传递给数据库引擎。
// 正确且安全的做法 string username = "admin"; string password = "' OR '1'='1"; // 恶意输入 string sql = "SELECT * FROM Users WHERE Username = @username AND Password = @password"; // 然后通过安全的方式设置 @username 和 @password 的值
核心思想：SQL 语句的结构（模板）在第一次解析后就会被数据库缓存和重用，而参数只是作为数据被传入，永远不会被解释为 SQL 代码的一部分。
为什么必须使用参数化查询？（核心优势）
1 防止 SQL 注入攻击
这是最重要的原因，SQL 注入是一种攻击手段，攻击者通过在输入字段中插入恶意的 SQL 代码，来操纵后台的 SQL 查询。
-
拼接查询的漏洞： 在上面的例子中，恶意输入
"' OR '1'='1"会导致最终的 SQL 语句变成：SELECT * FROM Users WHERE Username = 'admin' AND Password = '' OR '1'='1'
OR '1'='1'永远为真，因此这个查询会返回Users表中的所有用户，攻击者就可以成功登录。 -
参数化查询的安全性： 当使用参数化查询时，数据库驱动程序会做严格的处理，恶意输入
"' OR '1'='1"会被当作一个普通的字符串值来处理，而不是 SQL 代码，数据库引擎会把它当作密码字面值去匹配，而不会解析其中的OR逻辑，这个查询会因为找不到匹配的用户而安全失败。
2 提高性能和可维护性
-
查询计划重用： SQL Server 会对查询进行编译和优化，生成一个“执行计划”，当你使用参数化查询时，只要 SQL 语句的结构（模板）不变，数据库就可以重用之前缓存的执行计划，而无需每次都重新编译，这可以显著提高数据库的性能，尤其是在高并发的场景下。
（图片来源网络，侵删）- 拼接查询：每次用户名或密码不同时，整个 SQL 字符串就变了，数据库都会认为这是一个新的查询，需要重新编译，导致性能下降。
-
代码更清晰、更易于维护： 将 SQL 逻辑和数据分离，使得代码更易读、更不容易出错，你不需要再担心数据中的单引号等特殊字符会导致 SQL 语法错误。
如何在 SQL Server 中使用参数化查询？
参数化查询通常在应用程序代码中使用 ADO.NET、Entity Framework、Dapper 等 ORM 框架来实现，下面我们以最常见的 ADO.NET 为例，并介绍 SQL Server 自带的 sp_executesql 存储过程。
1 在 ADO.NET 中使用 (C# 示例)
这是在应用程序中执行参数化查询的标准方式。
using System;
using System.Data;
using System.Data.SqlClient;
public class UserRepository
{
private readonly string _connectionString;
public UserRepository(string connectionString)
{
_connectionString = connectionString;
}
public User GetUserByUsernameAndPassword(string username, string password)
{
User user = null;
// 1. 定义 SQL 查询语句模板，使用 @param 格式的参数名
string sql = "SELECT UserID, Username, Email FROM Users WHERE Username = @username AND PasswordHash = @passwordHash";
// 2. 使用 using 语句确保资源被正确释放
using (SqlConnection connection = new SqlConnection(_connectionString))
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
// 3. 添加参数并赋值
// AddWithValue 会自动推断参数类型，但显式指定类型更安全
command.Parameters.AddWithValue("@username", username);
command.Parameters.AddWithValue("@passwordHash", password); // 注意：实际存储的应是哈希值，而非明文
try
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
user = new User
{
UserID = reader.GetInt32(0),
Username = reader.GetString(1),
Email = reader.GetString(2)
};
}
}
}
catch (Exception ex)
{
// 处理异常
Console.WriteLine("数据库查询出错: " + ex.Message);
}
}
}
return user;
}
}
public class User
{
public int UserID { get; set; }
public string Username { get; set; }
public string Email { get; set; }
}
关键点：
SqlCommand对象：代表要执行的命令。Parameters集合：用于添加和管理参数。command.Parameters.AddWithValue("@paramName", value)：这是最常用的方法，它会自动根据value的 .NET 类型推断出对应的 SQL Server 类型（如NVarChar,Int,DateTime等）。- 显式指定参数类型（更推荐）：
command.Parameters.Add("@username", SqlDbType.NVarChar, 50).Value = username;
2 在存储过程中使用 sp_executesql
sp_executesql 是一个系统存储过程，它允许你动态执行一个 SQL 语句，并且支持参数，这对于在数据库内部构建动态 SQL 非常有用。
语法：
EXEC sp_executesql
@stmt, -- 要执行的 SQL 语句字符串（Nvarchar）
@params = N'@param1 data_type, @param2 data_type', -- 参数定义字符串
@param1 = 'value1', -- 参数值
@param2 = 'value2'
示例：
假设我们有一个存储过程,它可以根据用户名和状态来查询用户。
CREATE PROCEDURE dbo.GetUsersByFilter
@Username NVARCHAR(50) = NULL,
@IsActive BIT = NULL
AS
BEGIN
-- 定义动态 SQL 和参数
DECLARE @sql NVARCHAR(MAX);
DECLARE @params NVARCHAR(500);
-- 参数定义字符串
SET @params = N'@UsernameParam NVARCHAR(50), @IsActiveParam BIT';
-- 动态 SQL 模板
SET @sql = N'SELECT UserID, Username, Email FROM Users WHERE 1=1 ';
-- 根据输入参数动态添加 WHERE 条件
IF @Username IS NOT NULL
SET @sql = @sql + N' AND Username = @UsernameParam ';
IF @IsActive IS NOT NULL
SET @sql = @sql + N' AND IsActive = @IsActiveParam ';
-- 使用 sp_executesql 执行，并传递参数
EXEC sp_executesql
@sql,
@params,
@UsernameParam = @Username,
@IsActiveParam = @IsActive;
END
GO
调用这个存储过程：
-- 查询所有活跃用户 EXEC dbo.GetUsersByFilter @IsActive = 1; -- 查询用户名为 'John' 的用户 EXEC dbo.GetUsersByFilter @Username = 'John'; -- 查询用户名为 'Jane' 且不活跃的用户 EXEC dbo.GetUsersByFilter @Username = 'Jane', @IsActive = 0;
sp_executesql 的优势：
- 安全：同样可以防止 SQL 注入。
- 高效：因为参数化，SQL Server 可以缓存并重用这些动态查询的执行计划。
- 灵活：比
EXEC(@sql)更强大，后者无法传递参数，性能和安全风险都更高。
参数化查询的注意事项和局限性
1 LIKE 查询
当使用 LIKE 时，如果通配符（, _）是动态的，就不能简单地直接参数化。
-
错误的做法：
string search = "%abc%"; command.CommandText = "SELECT * FROM Products WHERE Name LIKE @search"; command.Parameters.AddWithValue("@search", search); // 这样写是可行的，但如果你想控制通配符的位置就不行了。 -
更灵活的做法： 如果你想让用户输入
abc，然后匹配以 abc 开头的产品，你应该在代码中拼接通配符，然后参数化核心部分。string userInput = "abc"; string searchPattern = userInput + "%"; // 在代码中构建模式 command.CommandText = "SELECT * FROM Products WHERE Name LIKE @search"; command.Parameters.AddWithValue("@search", searchPattern); // 参数化整个 LIKE 模式这种方式仍然是安全的,因为通配符是受信任的代码生成的，而不是来自用户输入。
2 IN 子句
IN 子句中的列表（如 IN (1, 2, 3)）不能直接参数化，一个常见的解决方案是：
- 将列表值（如
1,2,3）作为字符串传入。 - 在存储过程中,使用一个表值参数或一个临时表来解析这个字符串。
- 然后将查询
JOIN到这个临时表上。
3 ORDER BY 子句
ORDER BY 的列名也不能直接参数化，解决方案通常是：
- 在应用程序中构建一个安全的白名单,允许用户选择的排序列只能是白名单中的列。
- 根据用户的选择,拼接出完整的
ORDER BY子句。
| 特性 | 传统字符串拼接查询 | 参数化查询 |
|---|---|---|
| 安全性 | 极低，极易受到 SQL 注入攻击 | 极高，从根本上防止 SQL 注入 |
| 性能 | 较低，每次查询都可能需要重新编译执行计划 | 较高，可以重用缓存的执行计划 |
| 可维护性 | 差，代码易错，难以管理特殊字符 | 好，代码清晰，逻辑与数据分离 |
| 适用场景 | 不推荐在任何生产环境中使用 | 强烈推荐在所有数据查询、插入、更新、删除操作中使用 |
核心结论：永远不要在应用程序中手动拼接 SQL 字符串来访问数据库。 始终使用参数化查询，这是编写安全、高效、可维护数据库应用程序的黄金法则。
