Working in SQL databases is so simple. It’s all about inserting information, selecting it, and…securing it against injections?

SQL databases are great for storing information. That makes them high value targets for malicious code. I’ve heard of SQL injections before, but I had no idea what it was. I finally get the xkcd Bobby Tables joke!

SQL Injection Attacks involve manipulating a database by inserting plain text information containing malicious code. In the Udacity Relational Databases course, we wrote a Python script that could accept user input on a web page and add it to a database. Unfortunately, the Insert statement was flawed and any inputs containing apostrophes caused an internal server error. Somehow, this was the least of my worries.

If a user wanted to perform an injection attack, they only had to input ‘); and follow it with an SQL query to manipulate the database. For example:

‘); delete from posts; —

This input caused the entire database to be deleted. A few keystrokes, and all that data, POOF! Gone. Thankfully there’s a simple fix to this issue. The problem was the formatting of the Insert statement.

“insert into posts values (‘%s’)” % content

The single quotes around the %s made it so anytime a single quote is input, it would end the SQL query. This is why the SQL Injection Attacks were possible. To sanitize inputs, I used a Python resource called a Tuple. Tuples are values substituted into a query by a database library. In the code below, the content column is put into parentheses and the statement is edited to safeguard against SQL Injection Attacks.

“insert into posts values (%s)”, (content,)

I always imagined applications needed some level of security, but not the databases. Now I see how even the database code needs to be secured to protect the information stored. I’ll keep this in mind when building applications in the future.