How Can I Securely Set Table Names in Dynamic SQL Queries Using Parameters?

Dynamic SQL query: use parameters to set table names

When processing dynamic SQL queries, it is usually necessary to dynamically set the table name based on input parameters. While setting parameters such as IDs is simple, setting table names can present challenges.

Failed attempt:

The initial approach demonstrated in the provided code is to set the table name directly in the SQL query string. However, this approach is vulnerable to SQL injection attacks.

Solution using OBJECT_ID function:

To ensure security and avoid malicious SQL injection, it is recommended to use the OBJECT_ID function to dynamically parse the object ID of the table name. By doing so, malformed or injected table names will not resolve, thus preventing security vulnerabilities.

Here is the updated code:

<code class="language-sql">...
SET @TableName = '<[db].><[schema].>tblEmployees'
SET @TableID = OBJECT_ID(@TableName) --如果格式错误/注入，则不会解析。
...
SET @SQLQuery = 'SELECT * FROM ' + QUOTENAME(OBJECT_NAME(@TableID)) + ' WHERE EmployeeID = @EmpID'</code>

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