Guide to SQL injection

Many web applications aren't simply made up of the static pages you see, in fact you'll find that the majority connect so some form of database. The use of databases mean web applications can become far more dynamic and so scaling up becomes much more maintainable as a new file is not required for every piece of information. A number of these databases are built around the use of SQL "structured query language", this essentially allows the developer to have their website talk to the database with the use of queries. Here's a few examples of databases that use SQL: MySQL, Postgresql and SQLite.

So, what exactly is SQL injection? Simply put, SQL injection is the process of injecting unsanitized commands into a website and altering the original database query. Lets give a simple example.

Say we have a website that stores information on all the books in the world, that's a lot. Rather than make a new document for every book we can simple create one blank document and have it fetch the book information from a database. To know which book the user is wanting to view the application will often make use of URL parameters, the website is then able to take this parameter, we'll call it book_url_id and pass it to the query, this query is then sent to the database so the information can be passed back. See an example of this query below.

SELECT `book_title`,`book_author`,`book_preview` FROM `books` WHERE `book_id`='125';

Now, in the example above the value 125 is what would appear in the browser URL bar, the URL would read something like - bookstore.com/view_book.php?book_url_id=125. However, as the book_url_id is controlled by the user this becomes a potential point of failure. I'm sure you're starting to see where this is going but lets ask the question anyway... What happens if the user adds information that's not a number to the book_url_id? Well if that information has not been appropriately sanitized then there would be potential for the user to add additional SQL and modify the existing query. This is called SQL Injection, I'll provide a sample query below so you're able to better understand how the website/database would see the injected query.

SELECT `book_title`,`book_author`,`book_preview` FROM `books` WHERE `book_id`='' UNION SELECT `admin_id`, `admin_username`, `admin_password` FROM `restricted_admin_table`--;

So whats going on here? Well, the original query is requesting the book information just like before, however, you'll notice that book_id is now empty. This means no book information is actually being returned as all of the books will have a unique id. Instead, the user has added an additional query which is fetching information from the restricted_admin_table, they would then be able to use this information to access restricted areas of the website and access additional functionality.

How SQL injection can be used to bypass login fields

Another instance in which SQL injection could be used would be login fields. Like the previous example, a login field must compare the submitted information (username and password) to that which is stored in the database. However, as SQL comes with conditional operators these login fields can occasionally be bypassed by simply entering the right condition. I'll give an example below.

SELECT `user_firstname`, `user_secondname` FROM `users` WHERE `username`='kingjerry43' AND `password`='superSecurePassword';

In this example the user is submitting their username and password which are then inserted into the database query, should both the username and password both match up with the information that's stored then it will return the user information. However, like with the book store, if the username or password which has been supplied via the website has not been correctly sanitized then the opportunity for SQL injection could arise. For example, what would happen if the user were to enter the following random_username' OR 1=1-- -? Well, lets take a look at the query.

SELECT `user_firstname`, `user_secondname` FROM `users` WHERE `username`='random_username' OR 1=1-- - AND `password`='wrong-password';

As you can see above, the use of OR means that even if the username provided is incorrect because 1 is equal to 1 the condition would pass, this is then followed by -- - which is commenting out (essentially removing) the remainder of the query. As 1 is equal to 1 the condition is okay and the user would be logged in.

Other scenarios where SQL injection can occur

Aside from the cases previously shown, SQL injection can actually occur anytime data is handed directly to a database query. SQL uses a number of statements to interact with a database and manipulate the information which is stored. Here's some examples of these additional queries;

• UPDATE - Updates information within a database table.

• DELETE - Deletes a row from a selected database table.

• INSERT - Inserts additional data to the specified table.

How to protect against SQL injection

SQL injection can be prevented a number of ways but the easiest and most effective way is to ensure your website code is safely written, see the example above? That can be fixed with a few simple lines or built in functions, one example can be seen below but it is recommend that you take additional precautions.

$id = mysqli_real_escape_string($database_connection, $_GET['id']);
$query = mysqli_query($database_connection, "SELECT * FROM `users` WHERE `id`='".$id."'");

The only sure fire way of protecting again SQL injection would be to correctly sanitize any user controllable information and make use of prepared statements. Should arctil find any SQL injection vulnerabilities it's recommended that you take action and patch it immediately. Below are some examples of how you can best protect yourself from SQL injection vulnerabilities.

• Ensure all web software such as WordPress or MyBB is running the latest version
  
  • Although new software can have vulnerabilities it is far more likely that these will be few an far between as their respective communities will have been hard at work patching and resolving any pre-existing issues.

• Make use of a Web Application Firewall (WAF)
  
  • Web application firewalls help monitor the traffic on your website using a number of techniques.

• Never trust user input
  
  • Although it sounds very cynical, we live in a world where people will try and steal your information and so it is recommended that all information is correctly sanitized before being used to query the database.

• Conduct regular website scans using arctil
  
  • Arctil exists to help give web admins and security testers a free, simple and convenient way to check for security bugs. Why not make use of this by downloading your free copy today.

Still need help?

Are you having trouble using arctil? Why not try reaching out to our Community Forum.

Alternatively, you can try contacting us through the Contact page.