Voice of the DBA

Author: way0utwest

  • More SQL Injection

    If you can't prevent this in your application, you need to think about upgrading your skills.

    One milly-yon sites hit by a SQL Injection attack. That happened according to a headline I saw recently, with an attack similar to Lizamoon affecting seven figures worth of ASP.NET sites. How can this still be happening on large scales? I’d like to think that this was mostly at small sites that people had set up for themselves, but I’m sure some decent sized companies were involved in this.

    This isn’t good for your brand as a developer. If you don’t know what SQL Injection is, you shouldn’t be developing software. If you don’t know how to code to avoid it, you shouldn’t be hired by anyone to build software. If you can’t write a stored procedure around a query or built a parameterized call to a database engine, you need to learn how or find another career.

    It’s sad that years after we’ve had the SQL injection problem make headlines, and change the way many companies write software, we will have thousands of applications being used every day that are still vulnerable to this type of attack. There is a lot of old code out there, but it can’t remain. We are regularly adding new data to our systems, and new data to applications. There’s no excuse for companies not making a complete review of older code and updating it to avoid unvalidated input or passing through queries that could be hacked.

    Educate yourself, save these headlines for your boss, and ask that no new applications, including third party ones, be purchased if they haven’t updated the code to prevent SQL Injection attacks.

    Steve Jones

    The Voice of the DBA Podcasts

  • Do You Want to be a DBA in Space?

    Not the trip for me, but it might be for you. Enter today.

    I had a chance to see a few of the DBA in Space videos before the contest was released and I shared them with my kids. My son’s first reaction was “can I go?” He couldn’t, but it did excite him a little to think about potentially going into space at some point in his life and he thought it was amazing that some average computer worker, like me, would get the chance. It’s an amazing contest and I’m still stunned that my employer, Red Gate Software, is offering it to anyone that works with databases.

    And that’s interesting. Developers, administrators, if you’re administering databases in any way, you’ve got a chance to enter the contest. You can get started now by signing up and going through all the videos, answering the questions as you go. The contest ends Friday, November 18, 2011, so there are a few days left to follow Brad McGehee on his space alien adventure and have fun with a few silly questions.

    I, however, wouldn’t go to space. I don’t like flying, and I’m not thrilled with heights, and it wouldn’t be my choice for a prize. I do know many people that love science fiction, the space program, and would be thrilled to go. If I could win (I’m not eligible), I’d take the cash alternative, $102,000, and probably take the trip of a lifetime. Maybe take the family and ski in Switzerland, maybe fly to Australia and enjoy surfing and diving down under with the kids, but definitely fly first class everywhere.

    Let your mind wander, ask your friends and family where they’re like to go? Would they have the adventure of a lifetime with you or would they think about sending you up in a rocket, as many of the Slashdotters want to do. It could be your choice, but only if you enter the contest this week.

    Steve Jones

    The Voice of the DBA Podcasts

  • The First Thing To Do After Creating Keys

    I’ve been looking at some security code in preparation for a few talks I plan on doing next year and there’s one thing I don’t see in many of the samples: backups.

    When you get ready to encrypt data in a table, or you enable Transparent Data Encryption (TDE), you will end up creating a key of some sort, or a certificate. Some of these keys, especially the database master key, needs to be backed up. Otherwise you won’t be able to decrypt your database or data later on if you recover this database on another server.

    As soon as you create a key for security, make sure you back it up right then. You might forget later, and then it might be too late. A little preparation will go a long way here.

    The commands are simple, and linked below:

    Securing these keys can be complex, but I would recommend that you place them in an administrative folder with limited access from anyone.

     

     

     

     

  • Network Databases? Is it time to change?

    Is it time to consider storing MDFs on NAS devices?

    In the two decades I’ve worked with SQL Server, there has been some advice that I’ve always given to people: don’t backup to the network, and don’t try to put a database on a file share. SQL Server hasn’t dealt with either of those situations well for most of my career, and they’ve been a regular source of issues for clients and posters to the forums at SQLServerCentral. Network technology, while quickly improving, was still flaky enough that I always recommend that people keep their data and backups local as a guideline.

    However technology evolves, and when it does, it’s time for the advice to change. Lately I have seen more and more people making backups across the network that seem very reliable. Even the tendency of Ethernet protocols to delay packets when collisions occur is mitigated by the increasingly common 1Gbps and 10Gbps network speeds that many companies are using. While I might still be a little nervous about running backups across the network, that’s my personal bias and I don’t know if I would continue to recommend against those backups.

    I was surprised, however, to see Kevin Farlee of Microsoft post a blog noting it might be time to reconsider the placement of data files on a network share. He outlines the improvements in technology and protocols that may make this a viable way to store our SQL Server databases in the future. It’s an interesting argument though I suspect that it is slightly premature for most of us.

    I do think that as networks become even more reliable, this is one way that we may better manage storage for data in the future, and perhaps see Shared Scalable Databases widely deployed as a way to scale out those read queries on other servers.

    Steve Jones

    The Voice of the DBA Podcasts