SQL Rant, GOOO!

Obviously, I’m not making huge piles of money at this “music” thing. (What with the bit where almost no one is.) This means that I have to have a day job. As it stands, my day job is programming computers. In fact, that what I went for college for, and it could arguably be called a “career.”

My first job out of college was at a network security place (shouts to my hackers at DDI!) This means that I get to claim that I have a “security background” whenever I talk about computers.

Being an expert and all, I’m gonna talk about SQL injections. If you already know about that, you may just want to jump down to where it says “OK. IT’S NOT BORING FOR PROGRAMMERS ANYMORE.” For the uninitiated, SQL is the language which is used to talk to databases. (Databases being basically glorified spreadsheets where all the information is stored.) When talking to a database, you communicate in queries. Queries are kind of like individual sentences. They tend to look something like this:

SELECT * FROM users WHERE last_name=”smith”;

That essentially translates to “give me all the details about users with the last_name smith.”
What usually happens is that programmers will leave blanks in those queries where users fill in their own data. For example, the above query could be rewritten as .

SELECT * FROM users WHERE last_name=”$_GET[‘user’]”;

Which says “give me everything from the list of users where the last name is the same as the ‘user’ supplied by the person visiting the page.” So if the visitor supplied a user value of “johnson”, you would get everything about the users named Johnson.

The problem here, is that hackers can manipulate these values to do unexpected things. For example, what if our attacker instead of supplying a name, wrote his own SQL query? Say he asked for the username

smith”; SELECT * FROM passwords; --

Then, the SQL server would interpret that as

SELECT * FROM users WHERE last_name=”smith”; SELECT * FROM passwords; --

Meaning “give me everything about the user(s) named smith AND give me all the passwords!”

Now there are solutions to this problem. Good coders will write code that stops attackers from doing this. Unfortunately, a lot of bad programmers are out there, and even good programmers sometimes slip up.

OK IT’S NOT BORING FOR PROGRAMMERS ANYMORE!

However, I think we should address it on a lower level. The programs that allow coders to access the servers should include functions that prevent multiple simultaneous queries. The vast majority of the time, you only want to execute 1 query at a time. You want the list of users, or you want the list of passwords. So the coder just asks for whichever one he needs when he needs it. Even bad programmers know to do this.

The malformed query up there is two separate queries. Why do our DB drivers allow this? Why not build your SQL functions so that if they detect multiple queries in a single request, they error out. “Sorry Dave, I can only execute one query at a time.” Kind of thing.

Sure, it won’t stop all SQL injections, and you’ll need a workaround for those instances when you DO want to execute multiple queries at once.  However, I feel like the Internet would be a much safer place if we just made it the default to refuse multiple queries.

OK. I’m done talking about computers. You can wake up now, Mom.

I believe the reason is similar to the root cause of C++ existing. It give job security.

Syndicate content