4 Common Misconceptions About SQL Injection Attacks

Published on: 2017-11-07

Photo by Jaanus Jagomägi on Unsplash

Interested in learning more about SQL injection attacks, including how to prevent them?  Attend my online webcast on Tuesday November 14, 2017 at 1PM Eastern at the PASS Security Virtual Group.

SQL injection continues to be one of the biggest security risks that we face as database professionals.

Every year, millions of users’ personal information is leaked due to poorly written queries exploited by SQL injection.  The sad truth is that SQL injection is completely preventable with the right knowledge.

My goal today is to cover four common misconceptions that people have about SQL injection in an effort to dissuade any illusions that an injection attack is not something that can happen to you.

Prefer watching me get angry about these common misconceptions?  You can watch this content on my YouTube channel.

1. “My database information isn’t public”

Let’s see, without me knowing anything about your databases, I’m guessing you might have some tables with names like:

  • Users
  • Inventory
  • Products
  • Sales
  • etc…

Any of those sound familiar?

You might not be publicly publishing your database object names, but that doesn’t mean they aren’t easy to guess.

All a malicious user needs is a list of common database table names and they can iterate over the ones they are interested in until they find the ones that match in your system.

2. “But I obfuscate all of my table and column names!”

Oh jeez.  I hope you don’t do this.

Some people do this for job security (“since only I can understand my naming conventions, I’m guaranteeing myself a job!”) and that’s a terrible reason in and of itself.

Doing it for security reasons is just as horrible though.  Why?  Well, have you ever heard of some system tables like sys.objects and sys.columns?

SELECT 
	t.name, c.name 
FROM 
	sys.objects t
	INNER JOIN sys.columns c 
		on t.object_id = c.object_id

A hacker wanting to get into your system can easily write queries like the ones above, revealing your “secure” naming conventions.

Security through obscurity doesn’t work.  If you have table names that aren’t common, that’s perfectly fine, but don’t use that as your only form of prevention.

3. “Injection is the developer’s/dba’s/somebody else’s problem”

You’re exactly right.  SQL injection is a problem that should be tackled by the developer/dba/other person.

But it’s also a problem that benefits from multiple layers of security, meaning it’s your problem to solve as well.

Preventing sql injection is hard.

Developers should be validating, sanitizing, parameterizing etc…  DBAs should be parameterizing, sanitizing, restricting access, etc..

Multiple layers of security in the app and in the database are the only way to confidently prevent an injection attack.

4. “I’m too small of a fish in a big pond – no one would go out of their way to attack me”

So you run a niche business making and selling bespoke garden gnomes.

You only have a few dozen/hundred customers, so who would bother trying to steal your data with SQL injection?

Well, most SQL injection attacks can be completely automated with tools like sqlmap.  Someone might not care about your business enough to handcraft some SQL injection code, but that won’t stop them from stealing your handcrafted garden gnome customers’ data through automated means.

No app, big or small, is protected from the wrath of automated SQL injection tooling.

Interested in learning more about SQL injection attacks, including how to prevent them?  Attend my online webcast on Tuesday November 14, 2017 at 1PM Eastern at the PASS Security Virtual Group.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

The Quickest Way To Get SQL Command Help

Published on: 2017-10-31

Formula One …. F1 …. Photo by Jp Valery on Unsplash

Every once in a while I discover a SQL Server Management Studio trick that’s apparently been around forever but is completely new to me.

Today I want to point out one of those features that had me thinking “how did I not know about this before”:

The F1 keyboard shortcut.

Prefer video?  Watch this week’s tip on my Youtube channel.

To use it, highlight a command or function that you want to know more information about and then press F1.  Simple as that.

Pressing F1 brings up the Microsoft online documentation for that keyword/function, making it the fastest way of getting to Microsoft’s online documentation.  You’ll solve your own questions faster than a coworker can tell you “to google it.”
Most recently I’ve been using the F1 shortcut in the following scenarios:
  • Can’t remember the date/time style formats when using CONVERT?  Highlight CONVERT and press F1: BOOM! All date and time style codes appear before you.
  • Need to use some option for CREATE INDEX and don’t remember the syntax?  Just highlight CREATE INDEX and press F1!  Everything you need is there.
  • Do you remember if BETWEEN is inclusive or exclusive?  F1 knows.  Just press it.

You get the idea.

Assuming you use the online Microsoft docs 10 times per day, 250 days a year, and each time it takes you 10 seconds to open a browser and search for the doc…

( 10/day * 250/year * 10 sec ) / 60 sec / 60 min = 6.94 hours saved.  Your welcome.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

10 Questions To Spark Conversation At Your Next SQL Event

Published on: 2017-10-24

Photo by rawpixel.com on Unsplash

Here’s a word for word transcription of a conversation I’ve had a hundred times over:

“Hi I’m Bert.  What do you do?”

“I’m ____ and I’m a SQL developer.”

“That’s cool, me too.”

*crickets*

*I look down at phone because I don’t know what to talk about*

Sound familiar?

In the next few weeks, you might find yourself at a conference like PASS Summit or SQLintersection.  If not a conference, then maybe a local user group, meetup, or SQL Saturday.

Inevitably you will find yourself surrounded by strangers.  Strangers who you know share common interests with you (SQL, duh!).

But if you are like me, starting a meaningful conversation with those strangers can be uncomfortable.  Most people have interesting stories to share, the challenge is to get them to talk about them.

The good news is that I’ve developed an easy way to get a conversation started with the people you just met:

Come prepared with interesting open-ended questions.

Prefer watching on YouTube?  Go ahead!  Otherwise, keep reading below.

I keep a memorized list of open-ended questions that I can ask whenever I don’t know how to keep the conversation going.  Try asking any of these questions the next time you don’t know what to say (and reciprocate by sharing your own fun story); I guarantee these will spark some interesting conversations.

1. “What’s your best SQL Server war story?”

We’ve all been in the trenches and have had to play the hero.

2. “What are your thoughts on EntityFramework/ORMs?”

If you ever want to get a table full of SQL DBAs going, this will do it.

3. “What’s the oldest version of SQL Server you are still stuck supporting?”

Although this one elicits a one-word response, the next easy follow-up is “why/how!?”

4. “What was your biggest “oops” moment?”

Backups were corrupt?  Yeahhhhh….

5. “What’s the most recent feature you started using in SQL Server 2014/2016/2017? How is it?”

I love hearing people’s answers to this because it’s a good way to figure out what new features really add value and which ones are over-hyped/limited in functionality.

6. “Are you using <feature you are interested in learning>?  How is it?”

Similar to #5, this is a great way to get real-world feedback about certain features.

7. “What’s your favorite session from today/this week?  What did you like most about it?”

I love finding out what sessions other people found useful – once again, real world reviews on what I should check out in the future.

8. “Have you been to <city> before? Do you have any recommendations for what I should do/see/eat?”

Great way to get to know the surrounding area without having to read reviews online.

9. “Do you use PowerShell or any other software to automate/do dev ops?”

PowerShell is the future.  Start learning how others are incorporating it into their environments, what struggles they’ve had implementing automated processes, etc…

10. “Are there any other events going on tonight?”

Especially great if talking to people who have attended the event before.  Find out what’s worth going to, if it’s better to show up early or late, is there a “best seat” in the house, etc…

I hope this list of questions encourages you to become better acquainted with your fellow conference goers.  And if I see you at PASS Summit…don’t be surprised if you hear me ask you one of these questions!

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!