What is it?
An SQL Injection, when malicious code is inserted into strings that are passed to the database or parsing and execution, is still counted among the biggest source of network intrusions and data breaches. It’s an old hack, but the revelation this month that a Russian gang has amassed over one billion pairs of passwords and usernames. But stopping them isn’t hard, say experts.
Never trust users
Make no assumptions about the size, type or content of data that may be put into a field. Consider how your application will behave if someone enters a 10 MB MPEG file where the application expects a postal code, or a DROP TABLE statement is embedded in a text field. So put appropriate limits on what can be entered in certain fields.
Check it out
Test the content of string variables so the application accepts only expected values. The application should reject entries that contain binary data, escape sequences, and comment characters. This can help prevent script injection and can protect against some buffer overrun exploits.
Never use dynamic SQL
Use parameterized SQL or stored procedures to greatly reduce the hacker’s ability to inject SQL into the code. Parameterized SQL is great if you absolutely must use ad hoc SQL, writes Litwin. If at all possible, however, you should employ stored procedures for the added ability to remove all permissions to the base tables in the database.
Execute commands with limited access accounts to connect to the database – never use a connection string that employs the sa (system administrator) or any high-privilege user account. Instead create a limited access account. A “reader” or “LimitedUser” account could limit access to reading of tables or the right to execute a stored procedure and no rights to the underlying tables.
Store secrets securely
One of the biggest targets in a database is the list of usernames and passwords, which is often stored as clear text. Instead store encrypted or hashed passwords in the database. Hashed passwords are more secure than encrypted passwords because they can’t be decrypted. You can harden a hashed password further by adding salt (a cryptographically secure random value) to the hash. Create code that compares the user entered password to a salted hashed version of the password.
Give nothing away
Error pages are a great tool for diagnosing and refining hacking attempts because of the information they provide. So don’t reveal too much information in error messages; use customErrors to display minimal information in the event of unhandled error; set debug to false.
These tips are just the tip of the iceberg for what you need to do to stop SQL Injection attacks. For detailed advice, see this article by Paul Litwin, as well as this page from Microsoft’s TechNet library.