Not to kiss my own ass, but Microsoft’s official advice on avoiding SQL injection attacks sounds awfully familiar to readers of this blog:
Sanitize (validate) all inputs: “This helps to ensure that the input is free from characters that cause SQL injection attacks.” It also allows you to fix the form and data type of the user input, which pretty much renders basic script kiddie attacks useless.
Parameters, not strings, as query variables: “Creating dynamic queries using string concatenation potentially allows an attacker to execute an arbitrary query through the application.”
In other words, it’s harder to break this:
@person VARCHAR(20); SELECT * FROM table WHERE person = @person;
than it is to break this:
SELECT * FROM table WHERE person = 'some user string';
Stored procedures, not free-form queries: “Stored procedures by themselves do not remove SQL injection vulnerabilities. They only raise the bar on the attacker by hiding much of the underlying database schema.” That is, the attacker can’t easily find out what columns are in a table, or what type of data is in those columns, if you use a stored procedure.
Minimal permissions: “In general, database applications should be using a low-privileged account that has the minimum permissions required to execute the statements submitted to SQL Server.” As in, create a user in your SQL database whose only permission set is to execute your Web-based stored procedures, and connect to the database server as that user.
Those are the basics. And if you don’t understand how to do them, I’ll be putting together a blog series on how to convert your old string-queried Web applications into one secured with stored procedures and proper permissions.