News Stay informed about the latest enterprise technology news and product updates.

Step 6: Don't expose interfaces that create dynamic SQL to the end user

Be careful not to create poisoned code when using dynamic SQL.

Dynamic SQL -- stored procedures that build other stored procedures, for instance -- can be a big boon. There are some problems that cannot be solved any other way except by dynamically generating SQL, either inside the database itself or through a program that talks to it. That said, if you do this, be sure to take extra steps to abstract the users from such things so that there is no way they can create poisoned code.

One example of this would be passing the name of a table to be dropped or modified through a stored procedure. Unless you ensure that the table name cannot be specified by the user directly (or even indirectly), you're asking for trouble, especially if later down the line the database is not talking to the same user interface code. This can poison not only your data, but also the relational structure of your database itself, and the damage may not even show up until it's far too late.


ENSURING DATA INTEGRITY IN SQL SERVER

 Home: Introduction
 Step 1: Back up, optimize and enable safety features
 Step 2: Segregate data aggressively into files and filegroups
 Step 3: Consider using implicit transactions
 Step 4: Be careful how you enforce internal referential integrity through triggers
 Step 5: Use constraints and relationships to keep out bad data
 Step 6: Don't expose interfaces that create dynamic SQL to the end user
 Step 7: Use a "check-in/check-out" mechanism for contested data

ABOUT THE AUTHOR:   
Serdar Yegulalp
Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
Copyright 2005 TechTarget

Dig Deeper on SQL Server Database Modeling and Design

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close