Breadcrumbs

SQL Injections

Looking for fun online learning for your children? Need help writing business and personal letters? Visit Learning Haven (external link) for educational resources!

Since learning database manipulation is usually one of the first topics programmers learn, it makes sense to learn how to properly secure a database against SQL injections. SQL injections are when a malicious user extends your SQL query by inputting unexpected SQL code into your application. Where they should enter their name or email address, instead, they enter extra information that allows them to manipulate your database using a form you provide or variables you gather from the URL.

For the purpose of this article, there is not much need to dive deeply into using SQL itself, but you should be familiar with it. For those who do not know, SQL is the interface applications use to communicate with a SQL server, such as MySQL (external link). This article will focus on the vulnerabilities of MySQL, due to its popularity and wide spread usage.

What Are SQL Injections?

The basis for SQL injections is running unescaped queries, which leaves yourself open to attacks that could potentially cripple your database.

Below is a query that will instruct a SQL server to change someone's name to "Don":

Code: SQL

UPDATE members SET name = 'Don' WHERE id = '1'

This query will update one record - one row of information in a database - to change the name column to "Don", which does not make the application very dynamic. To allow people to enter a varied array of different information we will use variables within our SQL query.

Code: SQL

UPDATE members set name= '".$name."' where id = '1'

This is our SQL query when we use a variable for the value of the new name. The variable will typically hold information that comes from a submitted form by the user. If someone were to submit the name "Don" the SQL query would look identical to our first query; however, they could also submit the names Jody, Fred, or Wallace.

This is where SQL injections come into play. What if the user does not enter just their name? What if they enter code to adjust their member privileges? Or worse, to dump the entire table?

You probably noticed the single quotes (') around the variable and the number one. These quotes tell the server where the information to enter into the column is located; anything between the quotes is information to enter into the database.

Code: SQL

UPDATE members set name = 'Don', userlevel = 'Administrator' WHERE id = '1'

This SQL query will update the table named members, setting the particular member's name to "Don" and their userlevel to "Administrator". This query can be created with a SQL injection. For example, instead of entering their name, a user could also enter extended code to grant themselves administrative privileges:

Code: SQL

', Userlevel = 'Administrator'

Therefore, the user's final input would look like this:

Code: SQL

Don', Userlevel = 'Administrator'

Fairly easy to do, eh? Remember that to update the name column, the server is only looking for information between the two single quotes. By entering their name as Don', Userlevel = 'Administrator', the malicious user not only sets their name to "Don", but they also extend the query - adding the single quotation and the additional update command - to give themselves administrative privileges.

Preventing SQL Injections

The majority of SQL injections will occur when you trust user input. Never trust user input. Most people will use your website as you expect; however, there will always be people who get bored easily and who will try to crack your code. This is why you need to make sure you have escaped all user input.

When you escape user input you are adding a backslash before special characters, such as single quotes. This will tell the SQL server that the specific character is part of the information to enter into the database and not SQL code.

Various programming languages provide a method for adding backslashes to a string. For instance, PHP has addslashes() (external link).

Note: Some languages, such as PHP (external link), will automatically add a backslash before specific characters, making them query ready. Therefore in the case of PHP, there is no need to use addslashes(), unless you have previously removed the slashes or have turned off that particular PHP setting.

Remember that in order to perform a SQL injection, the malicious user must first be able to determine your database layout. With this in mind, simply keeping member privileges in a separate table from member information can dramatically reduce your chances of a SQL injection that would manipulate member privileges.

Summary

SQL injections are a dangerous vulnerability to any application using the popular database query language. Allowing malicious users to input unescaped information into your application could lead to drastic casualties. Properly validating and escaping user input can help to ensure that the nightmare of a SQL injection will never happen to you.

Last, remember never to trust user input.