Sweet, sweet databases

This past week has been dedicated to learning some of the many wonders of databases and database management (SQL - Structured Query Language - in particular). I know I know, "databases" and "database management" are seminally unsexy terms, but give me a chance to explain why they're awesome.

Databases save your account information so that you can log in to Facebook. They store your online banking password. They contain your address, email, phone number, social security number, and a whole host of other personal information you probably want to be kept private. You can picture them as digital Excel spreadsheets, with information stored in cells that are organized in rows and columns. The good news about all of this stored info? Well-coded web applications will keep your information safe and protected. The bad news? Even well-coded web apps, and especially poorly coded ones, can be susceptible to an SQL injection (*cue ominous music*). Before I can say another word about this nasty guy, let's introduce ourselves to SQL.

What is SQL?

If databases store information, then SQL manages it. SQL can add a column for your date of birth. It can delete your social security number from the database. It can give us a list of every Facebook user whose first name is "Joseph" or even give us every piece of stored information for every Facebook user. SQL is both very powerful, and very easy to use. This bodes very well for me, a new programmer learning to work with databases, and very bad for those that want to protect them. After all, if it's easy to use, it's easy for everyone to use, including those that want to steal your information. And speaking of stealing information, I present to you our nemesis, SQL injection.

Now for the injection

If databases store information and SQL manages it, then an SQL injection is when a hacker takes advantage of improper coding to a web application and "injects" an SQL command into a form - let's say a Facebook login form - and gains access to your private data within the database. How does this happen?

Let's stick with our Facebook login form example. When legitimate users submit their email and password, an SQL query is generated from these details and submitted to the database for verification. If valid, these users are allowed access into their Facebook pages. However, non-legitimate users may use an SQL injection comprised of specifically crated SQL commands to bypass the login form barrier and access users' account information. So if a web application hasn't been coded properly, i.e. without security provisions in mind, it will certainly be more susceptible to injections.

While the example above focuses on a login page, SQL injections are not limited to just that type of application. Support and product request forms, feedback forms, search pages, and online shopping carts are all at risk because the fields available for user input allow SQL statements to pass through and query the database directly, just as web applications are supposed to do. So what can be done?

Potential solutions

Fortunately, there are a few techniques that can be implemented to reduce the risk of an SQL injection and they are as follows:

• Prepared Statements
• Stored Procedures
• Escaping All User Supplied Input
• Least Privilege
• White List Input Validation

For a new programmer like myself, prepared statements are the first and best line of defense for preventing SQL injections. Prepared statements, or parameterized queries, force the developer to first define all the SQL code and then pass in each parameter to the query later. This technique allows the database to distinguish between code and data, regardless of the user input. Such prepared statements ensure that a hacker is not able to change the intent of a query, even if SQL commands are inserted by a hacker

As I mentioned, coding your web applications with security in mind and using prepared statements is a plus, but certainly not a complete fix. Patching your servers, databases, programming language and operating systems is also critical but again, not a cure-all. After all, since your website needs to be public, security mechanisms will allow public web traffic to communicate with your databases servers through web applications, which is exactly what you want.