Tips to Prevent SQL Injection

The following are examples of steps you can take to ensure some level of protection from SQL Injection however, any situation where you are generating dynamic SQL will leave you vulnerable to clever hackers.

In order to reduce the surface area for attack, do not enable functionality that isn't required such as the SQL Server Agent service or xp_cmdshell (which allows arbitrary commands to be run on the server)

Always provide minimal permissions to the executing user in order to limit their options and reduce your exposure. In SQL Server 2005, you can impersonate users, so the new credentials will even apply to code invoked dynamically at the server. This feature opens up a whole new set of security concerns. Dynamic SQL can now run under impersonated user credentials and
not even require direct permissions from the user executing the stored procedure.

Inspect users thoroughly and used stored procedures. If characters are allowed, use pattern matching to check whether SQL injection constructs (such as single quote, two dashes, sp_, xp_, UNION etc) exist in the input.

Always limit the lengths of inputs when possible. This will help reduce the hacker's ability to damage your system. Email address fields shouldn't be thoudands of characters long.

Use stored procedures! Stored procedures encapsulate user input to the database, type checking the input as well as allowing certain permissions.

In general dynamic sql is always dangerous since the users input can end up being executed. If possible its always safer to use static code as long as attention is paid to security issues. There are several tricks you can use to avoid dynamic sql such as using functions to parse input and invoke static code. Using static code will also give you a performance edge since the current implementation of stored procedures generates a new execution plan for each input.

If you ever need to expect quotes in your input (such as text inputs for a blog or something) a safe way to prevent sql injections is to simple replace CHAR(39) with CHAR(39)+CHAR(39) this will make it impossiblefor the hacker to escape the string. Using dynamic sql can be very powerful, however misuse and/or abuse can causeinefficient code that may open your database to attacks.