Preventing SQL Injection Attacks

Another site's code has been revised to prevent SQL injection attack. While this is becoming a lucrative addition to the normal work I do it is frustrating to have to spend time looking over old code for vulnerabilities.

A friend of mine, who is an IT manager, asked me what he could do to ensure this didn't happen again. He was looking for some magic solution to make sure all the programmers and freelances that work for their company don't write bad code. I had to tell him there was nothing available to do what he wanted. They could implement all the standards and work flow that they wanted, but all it takes is one exception, and all their efforts could be for not. Don’t get me wrong I understand the need for programming standard, work flow processes and code review. They are a vital part of application development. They won’t prevent every problem resulting from bad code, including SQL injection vulnerability.

He had a resigned look on his face, and wasn’t really happy with my answer. I told him there was something his DBA and he could do together, although it might not be popular with the developers - lock down the databases.

Require the developers to write stored procedures to access the database. Set up the SQL user login so it only has permission to execute those procedures. Don’t allow the login used for the database connection to insert or update tables (and views). No inline SQL statements in the web site application code. We went and talked to the DBA and he was surprised to find out that the DBA had considered mentioning this as an option but didn’t want to upset the developers.

After some discussion between all the parties they determined that they would require the use of stored procedures for inserts and updates. Developers can write inline SQL for database reads they must justify it to the DBA, for example on ad-hoc report pages. The connection string’s user login by default will only be able to execute stored procedures, if needed it will be given read access to specified tables.

Lessons Learned:

1. Talk to other people in your organization they might have a solution to your problem.
2. If you have control of your database talk to your DBA about locking down the database to prevent SQL injection attacks.
   
3. If your site has been hacked, and you want some help then contact me through Zajon's web site.