I've had the luxury of attending a number of webcasts lately, and in doing so have glommed onto a pile of information relating to application security across the web. While there are many, the most insidious (IMHO) is SQL Injection.
SQL Injection is the process of adding SQL statements in user input. This technique is used by hackers to:
- probe databases
- bypass authorization
- execute multiple SQL statements
- call built-in SPs (including extended SPs).
Web sites that build SQL strings dynamically are most at risk to this particular attack. At best, a hacker will get access to data they shouldn't; at worst the potential exists for someone to execute DOS commands and dust your system drive. If your site allows plain-text input to add a parameter to a SQL string, you may be at risk. I borrowed these examples from Joel Semeniuk's webcast. His RSS feed is http://weblogs.asp.net/jsemeniuk/Rss.aspx.
Let's say you have a text box on your site that appends the value for the WHERE clause. On the server side, a listing for all customers where you type 'Seattle' in the box would yield:
SELECT * FROM Customers where City = 'Seattle'.
This gives you exactly what you want and all is well with the world.
Now, if that user an OR statement that resolves as true, the statement might be:
SELECT * FROM Customers where City = 'Seattle' OR 1=1
This statement would return all rows in the table (as the OR construct resolves as 'true'). This might not be what you want; even if viewing the data were 'harmless', the performance hit on the SQL box and web server could cause other problems.
Now, if that user adds a semi-colon character and your text box is not trapping for them (this allows another SQL statement to be executed after the first), this innocent statement can become:
SELECT * FROM Customers where City = 'Seattle'; update Customers SET Phone = '555-1212' where CustomerID = 'yaya'
This updates a customer record (assuming you have a CustomerID equivalent to 'yaya'). This can't be good for business; if nothing else, 'yaya' would be very unhappy if they were expecting any calls. Note that the previous injection sample (the one with the 1=1 construct) could give an attacker your entire customer list, thus allowing a significant opportunity for disruption.
If the account used to access the SQL server has DBO privileges, you can only imagine the havok this statement would wreak:
SELECT * FROM Customers where City = 'Seattle'; drop table “customers"
If the account being used has administration privileges over the machine:
SELECT * FROM Customers where City = 'Seattle'; exec master..xp_cmdshell "ipconfig /release
In this case, disconnects the system from the network.
So, what do you do? We're all singing the “all input is evil“ and “don't trust the users“ songs at the moment. Hence:
- Cleanse (even sanitize) all input; consider free-text input as harmful until you can prove otherwise. Go as far to failing characters like “;“ and “--“.
- Use regular expressions to look for invalid characters on the client side.
- Run with least privilege (a non-sa SQL account, a non-administrator system account).
- Use stored procedures or SQL parameterized queries.
- Do not echo errors to the user (while these echoes are handy during development and debugging, they're information to opportunistic hackers).
- Log all fail attempts.
- Set alerts for particular failures.
- Avoid batch mode (multiple command execution).
This article from SQL Server Magazine (the link is to MSDN) has four solid methods of protecting yourself from injection attacks.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag04/html/InjectionProtection.asp