Data Management

Anticipate the worst when developing SQL Server databases

Arthur Fuller advises DBAs to try to break their software in order to make sure their SQL Server databases can withstand potential attacks. See if your code can hold up to his suggested tests.

A long time ago, I learned (the hard way) that you must anticipate the worst when working with SQL Server. In order to protect your software from someone with malicious intent—this could wind up being a disgruntled employee, a criminal with monetary intent, or a competitor wanting your customer list—I advise that you actually try to break your software.

See how your code holds up to these tests: Write a stored procedure and then assault it with every ugly input you can imagine. For instance, where a date is expected, pass a bad date or a string; where a money amount is expected, send a date, and so on.

As the DBA, you can guard against almost all assaults by feeding the inputs into a stored procedure or UDF, which you should do. It is also your responsibility to inform the authors of the front-end applications that these risks exist. Nonsense inputs should be precluded by the front-end applications as much as possible. Total nonsense shouldn't make it to the stored procedure. Devious and malicious inputs (e.g., subversive but not necessarily nonsensical) may make it past the front-end inspector, at which point it is your responsibility to deal with them.

The safest path is to force the front-end to call stored procedures or UDFs. That way SQL Server will handle problematic inputs that make it past the front-end inspectors and reject them accordingly, returning the ball to the front-end application development team's court. You can return error numbers, custom text created in your stored procedures, or simply do nothing and let the failure bubble up.

Trust me (or better yet, don't trust me)—one day someone with malicious intent will attempt to hack into your database. If you haven't put your software through the wringer, you may be offering a hacker the keys to your database kingdom.

Miss a tip?

Check out the SQL Server archive, and catch up on the most recent editions of Arthur Fuller's column.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

Editor's Picks

Free Newsletters, In your Inbox