Last week I described an attack on databases that is becoming popular with the cracker crowd, SQL Injection Attacks. Fortunately, as scary as these attacks can be, there are some basic preventive measures you can take. It requires that developers and DBAs talk and work together, something that isn't always an easy accomplishment in some shops!
Some of the protections against SQL injection attacks include:
* Validating user input; * Carefully designing security; * Locking down system stored procedures; * Using stored procedures; * Test everything thoroughly.
Validate User Input
Text input, in particular, must be validated. If the user can type it in, it is suspect, but even unexpected combinations of list box, check box, etc. controls can throw errors that provide clues for breaking into your system. Never concatenate user input directly onto a SQL string. Check for length, presence of illegal characters, convert all single apostrophes to two apostrophes, and wrap all user input in apostrophes. Use regular expressions to control character content. Use functions like IsNumeric() to make sure you're actually getting a number. The list of validation options goes on and on, depending on the needs of the application.
Carefully Design Security
Never, never ever, use the sa user in SQL Server for a production app (and other database engines have similar admin accounts, so I'm not picking on Microsoft). In fact, with SQL Server 7.0 and later, you should never use sa even for administrative tasks other than to recover from a severely hosed server. Instead, use application users or roles, integrated security, or programmatic security, and grant only the permissions necessary for the app to do its job.
Lock Down System Stored Procedures
Just think of a user executing xp_cmdshell. That should cause nightmares for any DBA.
Use Stored Procedures
Rather than dynamic SQL, use stored procedures. By passing user input to a stored procedure, you can have T-SQL take care of rudimentary data type validation, as well as build robust data validation into the procedure. This is also a good way to limit the damage a cracker can do, as well as make it easier to tighten security on underlying database objects.
Test Thoroughly 'Nuff said
With careful design and implementation, it is quite possible today to put your database on the Web safely. At least until the next clever, misguided, and malicious hacker figures out a new way to circumvent your careful work.