SQL Injection

My last post dealt with the topic of SQL injection, so I think it is a good idea to look at this problem in depth. There are various types of code injection, but SQL injection is the most commonly used, making it the most troublesome. Code injection is a very serious security risk, and often results in personal data being compromised. For companies that keep track of high-risk info such as credit card numbers and social security numbers, SQL injection is a major threat that should be taken very seriously. Before I discuss the various methods that are used to protect against code injection, it will be helpful to describe exactly how these attacks work.

Code injection works by placing various programming commands in fields where they don’t belong. These mainly include user name and password fields, along with other user input fields. Although the “name” input field is expecting a name to be entered by a user, someone could easily insert a small line of code and change the function completely. I’ll give a quick example to show how easily SQL injection can be implemented.

When a website asks a user for their name and password, the values are inserted into a SQL statement. This statement checks with the database to make sure the user’s inputs are valid. If the login is unprotected, the SQL query will look like this:

SELECT id

FROM logins

WHERE username = '$username'

AND password = '$password’

If the values for $username and $password are not protected properly, an attacker can easily supply SQL code into one of the input fields and change the function of the query.

Suppose an attacker were to guess “Bob” as a username, and enter this code into the password field:

pass’ OR ‘x’ = ‘x    (“pass” could be named anything)

Now the SQL query will look like this:

SELECT id

FROM logins

WHERE username = 'Bob'

AND password = 'pass' OR 'x'='x'

By placing a single quotation mark after “pass” in the input field, the original SQL statement is replaced by one that now checks for two different things. First, “pass” will be compared against $password, and most likely remain false. Then, the injection occurs when the OR clause checks to see that ‘x’ = ‘x’. Since this statement will always be true, the attacker is able to bypass the login process and gain entry to the site. 

This is a simple example, but with a little knowledge of SQL commands and database naming styles, an attacker could easily navigate through a website’s database and gain access to confidential data. Even worse, once an attacker has gained access to the database, they could potentially delete all existing data. Many programmers today are aware of these risks, and take preventative measures to protect the user’s data. The first step is to make sure only valid text or number combinations are allowed in the input fields. Since the problem of SQL injection has been known for a while, most programming languages have created a way to validate the data being used with a simple function.

In PHP, for example, the function “mysql_real_escape_string” was created. This function simply takes a string entered by the user and strips off any unwanted characters that may have been added. This function is easy to use, and ensures that all user data is protected against injection attacks. Despite the simple solution to this problem, there are still many sites unprotected from these attacks. This is a serious, and dangerous problem since it is eventually the public’s information that is at risk.