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.
• Inside the SQL terminal, type (substituting your new password for newPassword):
  EXEC sp_password NULL, 'newPassword', 'sa'
  GO
• Once that's complete, type exit to quit.  Go back to the SQL Configuration Manager, remove the -m; from the Startup Parameters, and restart the SQL Server instance.
• Make sure SQL Server and the browser are allowed through the Windows firewall, and try connecting from the other computer.

If it worked, you should now be able to log in using the sa user and make all the changes you need through the SSMS on the remote computer.

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 is was 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:

"SELECT * FROM customers WHERE customers_username = '" + txtUsername.Text + "' AND customers_password = '" + txtPassword.Text + "'";

Since rewriting the entire site using stored procedures was outside of the scope of the project, I created the following function to strip out possible attacks.

public class Common {
 public static void CleanSQLInputs(ref string sToClean) {
  string[] blackList = {"/*","*/","--",";-",";","@@","cursor ","declare ","delete ","drop ","execute ","insert ","select ","sysobjects","syscolumns","xp_"};
  for (int i = 0; i < blackList.Length; i++) {
   sToClean = sToClean.Replace(blackList[i], "");
  }
  sToClean = sToClean.Replace("'", "''");
  sToClean = sToClean.Replace("\"", "''");
 }
}

Now, anywhere I need to clean an input, I just call

Common.CleanSQLInputs(sUsername);

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?