Voice of the DBA

Tag: SQL Injection

  • Prevent SQL Injection

    I would hope most of you reading this know what SQL Injection (SQi) is and how you can prevent it. Or at least what patterns cause problems. If not, here’s a short explanation that is worth reading. If you have more questions, ask in our forums.

    SQL Injection has been, and continues to be, a problem in many systems. In fact, I chatted with Mike Walsh recently after he’d published this post on an attack for one of his clients. He has some notes that explain how your database server might be vulnerable, as well as a description of a recent attack example. He also notes that many of you are responsible for protecting data, which is separate from other security mechanisms. You need to be sure you are protecting your data, even in vendor applications.

    I’ve seen similar issues in the past, both in homegrown and purchased applications, where text fields aren’t checked and SQL is built by concatenating user input with code. I’ve complained to vendors, though often a short repro helps them see the problem and I’ve found many companies will patch systems, albeit sometimes slowly.

    There are application firewalls that can help, and certainly limiting access to those users who need access is always good, but that’s not helpful when the application is something that many clients use.

    The best protection is education. If you don’t know what to do, or your developers don’t listen to you, perhaps engaging a consultant like Mike will help. I’m amazed at how often people listen to an outsider when they ignore the same advice from someone they work with. That might be especially true for managers who are more concerned with doing more new work rather than fixing something that’s not quite working well.

    Security is becoming a bigger issue in many organizations. Not because we might get fined, but often because our customers might decide to choose another service if we can’t protect their data. There are other choices these days for most of the services we provide, and many organizations are finding customers increasingly fickle and quick to leave. This might not be the case in business-to-business work, but it does happen.

    We often won’t be perfect in our security and even if we are, our systems will change and new vulnerabilities or attack vectors will appear. We can work on the problems we know and improve security over time. SQL Injection is fairly simple to prevent, but it takes some education, some practice, and some code review.

    All things good database professionals should be doing.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • SQL Injection, Still?

    It seems as though SQL Injection issues are still around. Attacks from SQL Injection are on the uptick as we begin moving through 2015. As noted in that piece, the constraints put on software developers usually mean that testing and security are the first things to let go when time becomes short. That’s sad, and in some sense, I think this means that we aren’t teaching secure coding early enough to developers, and certainly not often enough.

    I ran across a piece from Kevin Kline that asks why this keeps happening. After all, as Kevin shows, much SQL Injection is easy to prevent. The coding patterns and tools we use are simple to write. There are lots of articles out there that show a variety of techniques you can cut and paste into your code. However there are two big problems that prevent us from eradicating SQL Injection: aging code and bad habits.

    There’s no shortage of code that comes from frameworks and application templates, not to mention naive or ignorant developers. Lots of this code is vulnerable to SQL Injection. Since so many of these existing applications work, there is no great pressure to go back and change them to be more secure. Since data theft may not even be noticed, there are plenty of companies (and technologists) that have no idea their systems are vulnerable.

    The other problem is bad habits. Far too many developers and DBAs have spent years writing insecure code. When they prototype, mock, or quickly knock out code, they often rely on their experience to get work done quickly. And they do this poorly if they aren’t writing in a pattern that prevents SQL Injection. They haven’t updated their templates, tools, or their knowledge in a way that ensures all their future code will be secure.

    Ultimately we as an industry need to take SQL Injection seriously and write better code. Whether you use an ORM, a framework, or anything other shortcut to build applications, if you don’t create secure software, you’re part of the problem.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.3MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

  • Elevation of Privileges

    At SQL Bits this year I attended a security presentation from Andreas Wolter. The session examined some attack methodologies, showing the flow that an attacker might go through to gain information about your database instance with SQL Injection. It’s a scary and eye-opening talk, and one that I might recommend to all DBAs and developers so that they can understand the dangers involved with poorly coded applications.

    One of the most scary attacks was the elevation of privileges from a web user to a sysadmin on an instance, mainly because of the Trustworthy setting being enabled. I had never imagined this as an attack vector, but it was disconcerting to say the least. However it got me wondering about instances I’ve managed.

    Would I detect if a new sysadmin were added? Or an existing user added to the role? I’m not sure I would, though that’s certainly something I plan on setting up with some sort of monitoring to detect. I would guess that most DBAs, whether professional or accidental, might not catch this either, at least until some audit was performed. At that time it might be too late to protect your data, and certainly too late to protect your reputation.

    Security is a tough topic, and it’s an ongoing process to protect your systems. I hope to see more presentations like this at future events, and I’d encourage you to request them for any events you plan on attending. You can certainly do this for all SQL Saturday events (there’s a suggest a session on the schedule page).

    Security requires vigilance and vigilance requires monitoring. Both of those also need knowledge, so be sure that you don’t neglect the security of your SQL Servers and continue to educate yourself over time as well as implementing technical solutions.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.5MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

    The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.