By way of Clemens:
You read it here first. Kimberly Tripp blogs (rss). If you do anything with SQL Server: Subscribe!
Subscribed!
This past week, I concluded my talk on Security Coding: Best Practices at my work site. This was a continuation of Part 1 that I started last week. In particular, I dealt with SQL Injection and issues with Encryption and Cryptography.
I spent a little more time on SQL Injection, because this is a very interesting security issue to me. Even though you think your data is safe behind a few layers (UI, middle-tier), if you are not careful in how you process your calls to your database using the input from the UI, an attacker can easily gain lots of information about and from your database, including retrieving sensitive data and the structure and names of your tables. An attacker can also drop tables and kill your database completely.
SQL Injection is especially possible when concatenated strings are used for SQL queries. For example, if an input form requests a user to enter his/her name and password, and those inputs are used in a query string to determine access:
sql = “select * from users where userId = '“ + userName + “' and password = '“ + password + “'“;
An attacker can use a single quote (') in the userName input field to stop the string. After that, an “ OR 1 = 1 --” could be appended. This sets up a logical condition that will always be true. Plus, the “--” comments out the rest of the SQL query. So, the above query would be sent to the database like this:
select * from users where userId = '' OR 1 = 1 -- ' and password = ''
This would effectively bring back all user information. Very bad.
The key is checking all user input (“Never, ever trust user input“). Check that numeric fields are numeric. Check that string fields have double “single quotes“ (i.e. use a replace function to change single quotes (') into double single quotes ('')). Use stored procedures for access to any data, and when using stored procedures, implement them using the ADO command object or SQLParameter collection and classes so that variables are strongly typed. Always design and code for security from the first day, and test, test, test.
I have put together a list of further resources I have found useful in learning about SQL Injection as well as how to protect against it.
Advanced SQL Injection
Whitepaper on SQL Injection
Protecting Yourself from SQL Injection Attacks
SQL Injection FAQ