Level 2 of the Stairway to Synapse Analytics: A nalyze Data in Storage Account using the Server less SQL pool (2024-08-12)

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 - SSC Editor

Join the debate, and respond to today's editorial on the forums