How Can I Safely Set Table Names Dynamically in SQL Queries?

Employing Dynamic Table Names in SQL Queries Securely

Dynamically specifying table names within SQL queries is achievable, but necessitates robust safeguards against SQL injection vulnerabilities. The recommended approach leverages built-in SQL Server functions:

1. Declare a variable to hold the table name:

    DECLARE @TableName NVARCHAR(100);

2. Assign the table name to the variable:

    SET @TableName = '<[db].><[schema].>tblEmployees';

3. Retrieve the table's object ID:

    SET @TableID = OBJECT_ID(@TableName);

4. Construct the SQL query using the object ID for safety:

    SET @SQLQuery = 'SELECT * FROM ' + QUOTENAME(OBJECT_NAME(@TableID)) + ' WHERE EmployeeID = @EmpID';

5. Execute the query using sp_executesql:

    EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID;

This method ensures that the table name is handled securely, preventing SQL injection attacks by using OBJECT_ID and QUOTENAME to sanitize the input before it's incorporated into the SQL statement. The use of sp_executesql with parameterized queries further strengthens security.

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