SQL Injection

SQL injection is still quite common, though it has been known for many years. Unfortunately, not
enough web developers take the appropriate steps to remediate the vulnerabilities that make this attack possible. Given the prevalence of this attack, it warrants a bit more detailed description.
Consider one of the simplest forms of SQL injection, used to bypass login screens. The website was
developed in some web programming language, such as PHP or ASP.NET. The database is most likely a basic relational database such as Oracle, SQL Server, MySQL, or PostGres. SQL is used to commu- nicate with the database, so we need to put SQL statements into the web page that was written into some programming language. That will allow us to query the database and see if the username and password are valid.

SQL is relatively easy to understand; in fact, it looks a lot like English. There are commands like
SELECT to get data, INSERT to put data in, and UPDATE to change data. In order to log in to a website, the web page has to query a database table to see if that username and password are correct.

The general structure of SQL is like this:

select column1, column2 from tablename

or

select * from tablename;

Conditions:

select columns from tablename where condition;

For example:

SELECT * FROM tblUsers WHERE USERNAME = 'jsmith'

This statement retrieves all the columns or fields from a table named tblUsers where the username
is jsmith.
The problem arises when we try to put SQL statements into our web page. Recall that the web page
was written in some web language such as PHP or ASP.net. If you just place SQL statements directly
in the web page code, an error will be generated. The SQL statements in the programming code for the website have to use quotation marks to separate the SQL code from the programming code. A typical SQL statement might look something like this:

"SELECT * FROM tblUsers WHERE USERNAME = '" + txtUsername.Text +' AND PASSWORD = '" +
txtPassword.Text +"'" .

If you enter username 'jdoe' and the password 'password', this code produces this SQL command:

SELECT * FROM tblUsers WHERE USERNAME = 'jdoe' AND PASSWORD = 'password

This is fairly easy to understand even for nonprogrammers. And it is effective. If there is a match in the database, that means the username and password match. If no records are returned from the database, that means there was no match, and this is not a valid login.
The most basic form of SQL injection seeks to subvert this process. The idea is to create a statement
that will always be true. For example, instead of putting an actual username and password into the
appropriate text fields, the attacker will enter ' or '1' = '1 into the username and password boxes.

This will cause the program to create this query: 

SELECT * FROM tblUsers WHERE USERNAME = '' or '1' = '1' AND PASSWORD = '' or '1' = '1'.

So you are telling the database and application to return all records where username and password are
blank or if 1 = 1. It is highly unlikely that the username and password are blank. But I am certain that 1 

=1 always. Any true statement can be substituted. Examples are a = a and bob = bob.
The tragedy of this attack is that it is so easy to prevent. If the web programmer would simply filter all input prior to processing it, then this type of SQL injection would be impossible. That means that before any user input is processed, the web page programming code looks through that code for common SQL injection symbols, scripting symbols, and similar items. It is true that each year fewer and fewer websites are susceptible to this. However, while writing this chapter there was a report that the Joomla Content Management System, used by many web developers, was susceptible to SQL injection.3