You are here: Resources > IT Security Technical Resources Part3 > Anti-SQL Injection Tips and Tricks
Anti-SQL-Injection Tips & Tricks
Compromises the Whole Website
If ever an attacker successfully performs an SQL injection exploit against your site, it will allow them to manipulate queries of the underlying SQL server and lead the whole website into oblivion. To wit, launching a site that's vulnerable to such security holes is equivalent to handing the hacker administrative privileges to your webpage. It's just that easy to crack.
Identifying SQL Injection Vulnerabilities
- Check your sites that have query string values (e.g., search for URLs with "form=", “id=”, and so on in the URL).
- Send a request to your sites identified as dynamic with an altered id= statement that adds an extra quote to attempt to cancel the SQL statement (e.g., id=6').
- Parse the response sent back to look for words like "SQL" and "query"—which typically indicate that the APP is often sending back detailed error messages (a bad sign).
- Review whether the error message indicates that the parameter sent to SQL wasn't encoded correctly (in which case, the site is open to SQL injection attacks).
How Do You Protect Yourself?
SQL injection attacks are something you need to worry about regardless of the web programming technology you're using (all web frameworks need to worry about it, in fact). Here are a couple of very basic rules you must always follow:
- Don't construct dynamic SQL statements without using a type-safe parameter encoding mechanism. Most data APIs (including ADO + ADO.NET) have extra support that allows you to specify the exact type of parameter required (e.g., string, integer, and date) and can ensure that they are encoded to specifically avoid hackers trying to exploit it. Always use these features. For example, using ADO.NET with dynamic SQL will allow you to rewrite the code like so:
Dim SSN as String = Request.QueryString("SSN")
Dim CMD As new SqlCommand("SELECT au_lname, au_fname FROM authors WHERE au_id = @au_id")
Dim param = new SqlParameter("au_id", SqlDbType.VarChar)
param.Value = SSN
This will prevent someone from trying to sneak in additional SQL expressions (since the ADO.NET above knows how to string encode the AU_id value), and avoid other data problems (incorrectly typecasting values and the like). Note that the TableAdapter/DataSet designer built into the VS 2005 uses this mechanism automatically, as do the ASP.NET 2.0 data source controls.
One common misconception is that if you are using SPROCs or an ORM, you are completely safe from SQL injection attacks. This isn't true—you still need to be careful when you pass values to a SPROC. and/or when you escape or customize a query with an ORM, you should do it in a safe way.
- Always conduct a security review of your application before putting it in production. Furthermore, you should also establish a formal security process to review the whole code anytime you make updates. This later point is super important. Too often, I hear of teams that conduct a really detailed security review before going live, then have some "really minor" update they make to the site weeks/months later where they skip doing a security review ("It's just a tiny update—we'll code review it later"). As much as possible, always do a security review or double-check just to be on the safe side.
- Never store sensitive data in clear-text within a database. My personal opinion is that passwords should always be one-way hashed (I don't even like to store them encrypted). The ASP.NET 2.0 Membership API does this for you automatically and by default (it also implements secure SALT randomization behavior).
If you decide to build your own membership database store, I'd recommend checking out the source code for our own membership provider implementation that we published here. Moreover, you should make sure to encrypt credit card numbers and other private data in your database. This way, even if your database is compromised, at least your customer's private data can't be exploited.
- Make sure to write automation unit tests that specifically verify your data access layer and application against SQL injection attacks. This is great for safeguarding against the "It's just a tiny update, so I'll be safe!" scenario, and it even provides an additional safety layer that prevents you from accidentally introducing a bad security bug into your application.
- Lock down your database to grant the web application accessing it the minimal set of permissions that it needs to function. If the web application doesn't need access to certain tables, then make sure it doesn't have permissions to them. If it is only generating read-only reports from your account payables table, then make sure you disable insert/update/delete access.