DBAs often try to use dynamic SQL to support Web searches. It’s common practice to capture search data from Web pages and pass that data as parameters to a SQL Server stored procedure. The stored procedure then builds dynamic SQL and returns a result set. This provides a flexible approach for all dynamic searches. However, this approach can expose SQL Server to malicious SQL commands called SQL injection. This security vulnerability occurs in the database layer. The source is the incorrect escaping of variables embedded in SQL statements. It can lead to nefarious users retrieving data, altering server settings, or even taking control of the server. Fortunately, dynamic SQL execution can be avoided by converting it to a static SQL search.
For example, DynamicEmployeesList.sql, which Listing 1 shows, includes a stored procedure to search the Northwind database for employee data. The stored procedure uses dynamic SQL to perform the search. Depending on the search parameters passed to the stored procedure, it builds dynamic SQL and returns the data set to the calling application. If no parameters are passed, the stored procedure returns all the records from the Employees table of the Northwind database.
StaticEmployeesList.sql, which Listing 2 shows, demonstrates how this simple dynamic search SQL can be converted to static SQL search by modifying the Where clause in the stored procedure. This approach provides not only flexibility but also the best security. In this case, the user needs only execution permission on the stored procedure. Incorrect values passed to the parameter won’t allow the user to take control of the server.
— Raj Rathi
rathirajk@yahoo.com
Editor’s Note
Share your SQL Server discoveries, comments, problems, solutions, and experiences with products and reach out to other SQL Server Magazine readers. Email your contributions (400 words or less) to r2r@sqlmag.com. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you’ll get $50.