When building a database driven website, there are lots of security concerns that you have to take into consideration. SQL Injection is probably the most common security vulnerabilities on the web with cross-site scripting being the second. I’ll explain in detail this kind of risk with examples of bugs in PHP and how to fix them.
If you were not a computers science major in college, or you don’t have experience in software development or programming languages or web technologies you may be wondering what SQL stands for. Well, it stands for Structured Query Language (pronounced “sequel”). It’s the standard language to access and manipulate data in databases that are common to the web, like Microsoft SQL Server or Oracles’ MySQL (the 2 most common databases on the web today).
Nowadays most websites rely on a database (usually MySQL) to store and display the content for their websites. Having a database allows for things to not be hard-coded and for data to be quickly updated, searched for and accessed.
Our example will be login script. WordPress uses databases to store pages, posts, configurations and authentication methods in this fashion. Internet users see login forms everyday… you put in your username and password and then the web server checks the credentials you supplied against values stored in the database. That sounds too simple doesn’t it? Well you’re right, what happens exactly on the server when it checks your credentials?
The client (or user’s web browser) sends to the server two pieces of information, a username and a password.
The server will have a database with a table where all user information is stored. This table has at least two columns, one to store the username and one for the password. When the server receives the username and password data and it will search the database to see if the supplied credentials are valid. It will use an SQL statement for that that may look like this:
SELECT * FROM users WHERE username=’SUPPLIED_USER’ AND password=’SUPPLIED_PASS’
Now in reality, the password should be encrypted, or more specifically “salted and hashed.” No need to discuss the technical details fo that in this blog post, so for simplicity we’ll work with unencrypted passwords. But bear in mind, any system that Secure Digital Technology Group builds, we will use encrypted passwords. For more information about that, please contact us today.
For any of you who are not familiar with any of the different variations fo the SQL language, in SQL the ‘ character is used as a delimiter for data variables. Here we use it to delimit the username and password information supplied by the client.
In this example we see that the username and password given are inserted into the search above between the ‘ and the entire query is then executed by the database engine. If the query returns any rows of data, then the supplied credentials are correct and valid (that username exists in the database and has the password that was supplied by the client).
So, what happens if a user enters a ‘ character into the username or password field? Well, by putting only a ‘ into the username field and living the password field blank, the query would become:
SELECT * FROM users WHERE username=”’ AND password=”
This would trigger an error, since the database engine would consider the end of the string at the second ‘ and then it would trigger a parsing error at the third ‘ character. Let’s now what would happen if we would send this input data:
Username: ‘ OR ‘a’=’a</p> <p>Password: ‘ OR ‘a’=’a
The query would become
SELECT * FROM users WHERE username=” OR ‘a’=’a’ AND password=” OR ‘a’=’a’
Since a is always equal to a, this query will return all the rows from the table users and the server will “think” we supplied him with valid credentials and let as in – the SQL injection was successful :).
Now we are going to see some more advanced techniques.. My example will be based on a PHP and MySQL platform. In my MySQL database I created the following table:
CREATE TABLE users (</p> <p>username VARCHAR(128),</p> <p>password VARCHAR(128),</p> <p>email VARCHAR(128))</p> <p>
There’s a single row in that table with data:
To validate the credentials we made the following query in the PHP code:
$query=”select username, password from users where username='”.$user.”‘ and password='”.$pass.”‘”;
The web server is also configured to output errors triggered by MySQL (this is useful for debugging, while you developing and testing the software but should be turned off when the code goes live).
Here we’re going to make some more complex queries and explain how to use the MySQL error messages to get more information about the database structure. This is another reason it should be turned off on a production server when the site goes live… there are security concerns on showing how your database is structured.
So, lets get started! If we put just an ‘ character in the username field we get an error message like. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”” and password=”’ at line 1
That’s because the query statement changed to become:
select username, password from users where username=”’ and password=”
What happens if we try to put something like ‘ or user=’abc the username field a string?
The query becomes
select username, password from users where username=” or user=’abc ‘ and password=”
And we get an error message like:
Unknown column ‘user’ in ‘where clause’
Using the error messages that get output we can figure out the columns in the table. We now can put in the username field ‘ or email=’ and since we get no error message, we know that the email column exists in that table. If we know the email address of a user, we can now enter ‘ or email=’email@example.com in both the username and password fields and our query becomes:
select username, password from users where username=” or email=’firstname.lastname@example.org’ and password=” or email=’email@example.com’
Which is a valid query and if that email address exists in the table we will successfully be logged into the site.
We can also use the error messages to figure out the table name. Since in SQL we can use the table.column notation, we can put in the username field ‘ or user.test=’ and you will see an error message like:
Unknown table ‘user’ in where clause
Okay. Let’s test with ‘ or users.test=’ and we have
Unknown column ‘users.test’ in ‘where clause’
So logically there is a table named users.
Basically, if the web server is configured to give out the error messages, we can use them to enumerate the database structure and then we will be able to use these informations in an attack. This is why we should turn off error reporting on our web servers. A simple fix for SQL Injection is to validate your input with your PHP code to prevent any delimiters or escape characters being entered. Another fix is to use stored procedures in your database to perform functions that interact with PHP code that users enter. I hope this article helps you understand SQL injection. If you have any questions, don’t hesitate to ask.