Archive for tag:
Databases

I'm still haven't seen SQL Server Management Studio (SSMS)
available to install through the Visual Studio 2010
installation. It's such an easy and painless way to interact
with SQL, but it still isn't available as a separate download as
far as I know. A big part of me is happy Microsoft finally
realized that you have to give poor developers access to the same
tools that the big boys use, but there are still places for future
work.

I could reinstall SQL Server Express with the Advanced Tools,
but I'm in a hurry and need to connect. To work around this,
I'm attempting to connect using another machine with SSMS 2008 on
it. Unfortunately, SSMS won't allow you to authenticate using
a different computer's users (domain users wouldn't have this
problem, I'm guessing). This leaves me with the option to
login as the superuser SA.

So what do you do when you've been relying on Windows
authentication and you need to reset the SA password?

Fire up the SQL Server Configuration Manager and double-click
on the SQL instance you need to reset. Click over to the
Advanced tab.

In the Startup Parameters, prepend -m;to the list to put SQL
server into Single-User mode. Click OK and
then right-click on the SQL Server instance and click
Restart.

Open up a command prompt, and type:
OSQL -S localhost -E
The -S parameter tells OSQL what server to connect to, and the -E
parameter creates a trusted connection. Capitalization of the
switches does matter.

I was
reading this article recently on InfoWorld, and even with all
of the best practices and options available to avoid it, SQL injection
attacks still make up 20% of the world's hacks and are the
single largest attack vector in use. I thought of all the
times I was mocking up a project and passed values straight
through, only to have to go back later and cleanse the
inputs. How many people mean well, but forget to go back and
address their //TODOs? There are a few ways to mitigate
this:

Only allow database access through stored procedures

Only allow database access through a DAL that strips out
injection attacks

Cleanse all text inputs of common injection code before passing
them through

I have a client whose site iswas filled
with myriad opportunities for an attack of this nature to
proceed. The original site architect never designed a DAL,
had business logic mixed throughout the code-behind pages, and
constructed most SQL commands as:

before passing sUsername into the SQL command. I've seen
other solutions that implement this as a function returning a
string, but I prefer to do it this way at the beginning of each
method for any strings that are being passed in from the UI to the
database so I have to keep track of what's been cleansed.

Luckily, the site in question had never been exploited.
But past precedent is no substitute for real security, and the site
owner was relieved to hear that this was in place.

Have you ever been hit with a SQL injection attack? What
steps have you taken to shore up your code from attacks?