Thursday, February 23, 2017

SQL Injection: What it is and how to avoid it

A while back, we (where I work) took over a project from another company. I was going to make some small changes and have it up before my second cup of coffee! Then I opened the code and realized I would be working late that day instead.

Among other issues, the code was vulnerable to SQL Injection.

SQL Injection is one of the easiest hacks to do. It's also one of the easiest to protect against. Still, it's a pretty common vulnerability! As much as I would love to have a backdoor into tons of sites, I can't help but lose sleep over this problem being out there. I have to share a few solutions.

What is SQL Injection?

For an example, let's take a very simple login page. I won't post the code, but the basic idea is to take a username and password, compare it to what's in the database, and log them in if it's a match. Easy peasy!

Here's the SQL to match it up in the database:

SELECT id FROM users WHERE username='bob' AND password='1234'

If it doesn't return anything, the username and password is wrong. If it finds a match, it returns the user's ID.

What if we change the username to bob'--?

SELECT id FROM users WHERE username='bob'--' AND password='1234'

Whoah! We just made it so that we don't even need the password! We can log in as anybody so long as we know their username!

The problems don't stop there. We can change a page that displays a list of items to sort/filter however we wish. We can increase the number of results returned and use that to do a very good DOS attack.

If the webhost is particularly unlucky, we can even execute SQL for other websites that they host -- even if those sites aren't vulnerable! (Hint: "USE abcDatabase;")

How do we protect against it?

That's easy. Sanitize your inputs!

Way 1 - Escaping data

You can properly escape the data before putting it into the SQL query.

Most languages have a function to escape out harmful characters, and it's usually pretty easy to use. This post is language-agnostic, but the basic idea in most languages is to pass in a potentially harmful string and get back a properly escaped one. The SQL query becomes the following:

SELECT id FROM users WHERE username='bob''--' AND password='1234'

As you can see, the SQL injection attempt will now be unsuccessful!

This time.

Watch out!

What if we have the following query that deletes the message with a given ID?

DELETE FROM messages WHERE messageId=12

It looks pretty harmless. Of course, we'll be sanitizing any user input anyways, right?

What if we give our code "1 OR 1=1" as the ID to delete? There are no special characters to escape. The string will get substituted as-is. What does this do to our query?

DELETE FROM messages WHERE messageId=1 OR 1=1

Oh, it just deletes everything. Even though we escaped all of the harmful characters, we are still vulnerable to SQL injection!

The quick solution is to put single quotes around every "variable" you're substituting in. It will work, but then you might run into weird logic errors when doing comparisons. A better solution is to use parameterized queries. Read on.

Way 2 - Parameterized Queries

Parameterized queries will properly escape any data, will verify that the type of data matches up, and will (hopefully) make sure everything's dandy with the character encodings. They are the best way to do SQL queries.

SELECT id FROM users WHERE username=@username AND password=@password;
DELETE FROM messages WHERE messageId=@messageId;

It might look a little different in different languages, but that's the basic idea.

Now you need the code that puts in the data. This depends a lot on the language, but the basic idea is to say what type of data each parameter is and then to set it to a value. In VB.Net, here is the code for the second query:

Dim query = "DELETE FROM messages WHERE messageId=@messageId"
Dim cmd As New SqlCommand(query, connection)
cmd.Parameters.Add("@messageId", SqlDbType.Int)
cmd.Parameters("@messageId").Value = 12

Parameterized queries are more verbose and somewhat confusing at first, but do use them. Use parameterized queries. Use parameterized queries.

Conclusion and Final Thoughts

SQL Injection is very easy to do, very common, and yet very easy to protect against. The simple solution is to just use parameterized queries everywhere. This OWASP page has some handy code snippets for your reference.

Here's a rule: Always use parameterized queries. Never insert your data directly into a query string (even if properly validated and escaped first). I don't care what your teacher or textbook says. Use parameterized queries. The only exception to this rule is when you know the risks and make an informed decision to go another route.

Saturday, January 12, 2013

Playing a Pitch in an Android App with AudioTrack

 I've been working on an android app lately that can be used to tune instruments. I've run into a road block with the pitch detection, so I'm starting over and making the code nicer. While I'm at it, I figure I may as well share some of what I've learned!

Sunday, January 6, 2013

AVL vs. Red-Black: the conclusion

    I accidentally deleted my original blog post about this, and I can't recover it despite trying all sorts of different tricks. So, here it is reposted. Sorry for any broken links.

Thursday, January 3, 2013

Year of Change

December 21, 2012 came and went, and the world is still here.  I didn't look a lot into the Mayan Calendar, but I think I remember reading that every time one of their circle calendar thingeys rolls over, it's a new era that brings change.

Every year, on December 31, people decide to make new years resolutions.  This year, why not assume the Mayans had something going right and make a resolution to see change?

People have been chanting about "change" a lot lately, but what has really changed?  And why are we still living relatively ordinary lives?  Myself, I am terrified of change!  I like my comfort zone, and I'm sure most of you prefer to not take risks, too.

We all have opportunities.  Why not take them?  You have a lot to lose, but you have even more to gain!

I stopped spending so much time programming a while back.  I got a guitar and played it until my fingers blistered and cracked.  On the 31st, I bought a new car.  Yesterday, someone called me and asked me to come work at some company.

Change.  I have an option to refuse it and continue to live in comfort.  I think I'll make a change in my habits and seek to change.  I'm taking the risk, making a gamble, and I think the odds are in my favor.  Will you join me?

I got my first blow yesterday.  I'm apparently not as great at singing and playing the guitar as I thought.  I knew I wasn't great, but am I not at least a little good?  Nope!  But I also found some encouragement in an unlikely place.  I found a youtube video of one of my favorite singers when she was younger.  I love her to death, but she just could not sing!  Now, she's going on world tours!  If she can do it, why can't I?  And if I can do it, why the hell are you still reading this?  Get out there and live a little!

Tuesday, December 11, 2012

Resistance Accomplished With Penguin Jokes!

My heart is saddened and worried as I resist the urge to dive into my old computer habits to avenge my newest love.  "That would be so cruel!"  Or, "I should get caught!"

I'm reminded of the last guy who thought he was clever with his 1337 5k*11z...  I sometimes miss his crazy ideas of how to annoy me.  And the guy before him who avoids me like the plague?  So sad...  so sad.

My evil side is hurt that I abandoned my computer world even though it was out of evil anger.  Yes, folks, with the exception of little scripts here and there to help me accomplish a task, I quit programming.  It's caused more harm than good, so I am looking for a new talent.

My newest talent is joke telling!  No, not really, but I am looking for good penguin jokes to make me feel better.  They must be penguin jokes!  Here are a few I've found so far (and 2 I knew):

FAT PENGUIN!!!!!!!!!!!!!!!!!!!!!!!!!!!
First joke, to break the ice!!!

Q: What do you call a penguin in the desert?
A: Lost!

Q: Why don't you ever see penguins in Britain?
A: Because they're afraid of Wales!

Two penguins are standing on an ice floe. The first penguin says, "you look like you're wearing a tuxedo." The second penguin says, "what makes you think I'm not?"

Penguins are black and white.  Old TV shows are black and white.  Therefore, penguins are old TV shows.

Q: And, lastly, how do you express your delight at a penguin joke?
A: Cool!

Any more?  Please share them below!

P.S. Don't take anything you read above seriously except for the request for more penguin jokes.  Most of it is silliness.  It's sad that I feel like I have to say this, but that's the world for you!  So, no hate mail from penguins!  Thanks!

Wednesday, November 28, 2012

Dreams of Fortune

    I won't tell what inspired this right now.  I want to know if you, dear reader, can take any meaning from my first attempt at poetry.  Please, be critical.  Be insulting if you need to be.  I have no confidence in my poetic skills, so you won't hurt my feelings in the least bit.  Shall I keep things like this in my notebook hidden away, or should I share?

Dreams of Fortune
What dreams of fortune to come our way are worth the time to contemplate?
Yet stuck we are obsessed with fame, but all we want is them to know our name.
How long will it take to rise to power? And will it be worth the wasted hours?
We will fail, and we will fall.  Then, we will cry and take our fate.
Giving up is the hardest necessity, but someone has to work for the clock.
All the while, we have that dream that someday we will live the life where we can be the idol who sleeps worry-free.
But someday soon, our fire will die, and we will be left an empty shell wondering the streets doing our duties and admiring those who got that lucky break.

Friday, November 23, 2012


Idols. Those amazing individuals who can do no wrong. Those unerring, sinless role models. I am so sorry to burst your bubble, but such people do not exist.

I think it's wonderful to admire people. Take note of what impresses you, and try to better yourself with the quality as a goal.

I think it's unhealthy to idolize people. It hurts. As a matter of fact, it can hurt both sides.

I have been unfortunate enough to have idolized and to have been idolized. It will be easy to share the experiences in the later-mentioned situation, but it will be a struggle to admit my wrongs in the former. Nevertheless, this blog post will do no good if I cannot be honest.

I honestly don't know what they see in me.

Sure, I'm smart and have a talent or two, but the people I have in mind don't even really know me. We all love compliments, and we all love being appreciated, but it's just awkward when such things are unfounded or exaggerated yet sincere.

I'm no celebrity, so maybe it's just that I am not used to having a fan or two. I just don't have a clue about how I am supposed to respond. Do I sit down and explain things to them and risk insulting them? Do I joke about it and thus encourage it? Do I thank them for their compliments and implicitly admit superiority that doesn't exist? And what if I mess up?

I don't know what to do, but maybe I can find wisdom in my past mistakes.

In a time of struggle, hope can make the difference. When the world seems to be trying to knock you down, you need a solid rock on which to stand. If only there were at least one thing in this world that is perfect. God is hard to see, but what if there were a tangible physical something or someone on which you could rely?

Usually, it's someone in a position of power. Occassionally, it only takes someone who paid attention. Sometimes, an admirable quality starts the ball rolling. A combination of these things is perfect.

You try to better yourself, you try new things, and you do lots of stuff to get their attention. If it isn't good enough, why not try some self-destructive behavior just to see what happens? That doesn't work, so you go to more extremes. This isn't at all healthy!

Why did I stop? Was it the inability to carry out my foolish plans? Hah... no. Was it someone new? If it were, I wouldn't be stopping; I would be moving to someone new. No, dear reader, my self esteem improved.

The point of such an obsession is to have a reason to keep going in a world that resembles Hell. When you are able to take care of yourself in this world that, in my opinion, does sometimes resemble an evil place of torture -- only when you can take care of yourself in this world can you escape from the dependance on the idea of a perfect person.

So what should I do in the position of the one being idolized?

To be perfectly honest... I have no idea! Maybe trying to improve the self esteem of the idolizers? That probably won't work for the many of you more awesome people. Time just won't allow this approach! I'll keep a lookout and maybe ask a few more well-known people how they handle their fans.

I bid my regular readers a wonderful stalker-less life until next time. I do have one more thing to say on this topic to someone not currently a regular reader, though.

At the risk of being misunderstood as insincere, I must refrain from delivering this apology in a manner that ensures receipt. I must instead rely upon fate to bring you to this message.

I would like to apologize for my foolishness. It wasn't until I was in your situation that I realized why you made the decisions that you made. At the time, I was hurt and angry. Now I know that there simply were no good choices that you could make. Looking back, I know exactly why I did what I did. But even knowing the mind of the fan (I say fan because I don't want to label my past self with a harsher word), I know not what to do if one is the target of such admiration. You are blameless.