How Can I Dynamically Set a Table Name in a SQL Query Safely?

Dynamicly set table name in SQL query

This article explores how to safely and dynamically set table names in SQL queries, especially when using parameter values. While dynamic parameter values ​​have been successfully implemented, dynamic table names pose different challenges.

Method using OBJECT_ID and QUOTENAME functions

To prevent SQL injection and ensure query integrity, it is recommended to use functions instead of direct string concatenation. An efficient way is to use the OBJECT_ID and QUOTENAME functions:

DECLARE @TableName NVARCHAR(255);
SET @TableName = '<[数据库名称].><[模式名称].>[表名称]';

-- 检查表是否存在以防止注入
DECLARE @TableID INT;
SET @TableID = OBJECT_ID(@TableName);
IF @TableID IS NULL
    RETURN '未找到表';

-- 使用QUOTENAME构建查询字符串以转义特殊字符
SET @SQLQuery = 'SELECT * FROM ' + QUOTENAME(OBJECT_NAME(@TableID)) + ' WHERE EmployeeID = @EmpID';

This approach ensures that table names are escaped correctly and prevents potentially malicious input. The QUOTENAME function adds square brackets around any special characters or reserved words in the table name, preventing potential SQL injection attacks.

The above is the detailed content of How Can I Dynamically Set a Table Name in a SQL Query Safely?. For more information, please follow other related articles on the PHP Chinese website!