How SQL Injection hacks work and how to stop them

SQL injection is a common hack used to run statements on your database server. If a hacker gains access to your SQL database, he can steal, delete or insert information. SQL injection hacks are tedious and complex to fix, so it’s best if a website owner takes precautions before becoming a victim. Before you can protect a website, you need to know how SQL injection works.

Understanding SQL

Before you can understand how SQL injection works, you need some background on the SQL language. SQL is the language for MySQL, SQL Server, and Oracle. These three database engines have slightly different syntax, but all of them use four basic functions: INSERT, DELETE, SELECT, and UPDATE. Hackers can use any one of these functions to manipulate your data, so you must protect against any type of SQL injection.

Let’s take a basic SQL example. Take a look at the following code:

select * from customer

The above statement queries all records in the customer table and returns them all including every column in the table. Basically, this query returns all of your customer information. For most queries, you don’t want to return all records and columns. You use the SQL WHERE clause to filter records with only a specific set of values. For instance, the following SQL code returns all records where the customer’s first name is “Joe”:

select * from customer where first_name=’joe’

The above code returns only records where the first name is “joe,” and this type of statement is where hackers take advantage.

SQL Injection Syntax

SQL injection can be very complex, but let’s take a look at some simple examples. Once you understand simple concepts, you can dive deeper into more complex SQL injection attacks.

SQL injection usually happens through your form submissions. The following code is an example of a form that asks the user to enter a first name.

</form>

Typically, you’d expect the user to enter a first name and submit the form. However, this is where hackers take advantage of poorly constructed website code. Suppose this form inserts a new record into your tables. The following SQL code is an example of an INSERT statement:

insert into customer (first_name) values (‘ + $_POST[“first_name”] + ‘);

The above code builds a SQL statement from the form’s first_name variable. Incidentally, the above syntax used is PHP, but this method works with any language. The semicolon terminates the statement and lets you place multiple SQL statements within the same line of code.

The trick to SQL injection

The apostrophe character opens and closes string values in SQL. Hackers prematurely terminate strings and run their own SQL statements. Suppose the hacker entered the following value into the form’s first_name input text box:

‘ ); select * from customer; —

This example would then build the following SQL statement and run it on your database:

insert into customer (first_name) values (” ) ; select * from customer; —

‘);

If you’re new to SQL, this can be difficult to understand. Notice that there are two apostrophe characters built in the INSERT statement. The hacker’s SQL statement inserts a blank value into the first_name column, but then the statement is terminated with a SELECT statement appended to the end. The SELECT statement then retrieves all the data in your customer table. The goal with this SQL injection hack is to steal your data.

Hackers can also delete data, which means you need to recover data from backups. The hacker can replace the SELECT statement with the following SQL code:

drop table customer;

Building the SQL statement, you now have the following code:

insert into customer (first_name) values (” ) ; drop table customer; —

‘);

The “drop table” statement completely wipes the table from your database. The only way to recover the data is to pull it from your backups. If your backup is a week old, you’ve just lost a week’s worth of data. This issue can be devastating to your business, especially if you work with orders.

How Do You Defend from SQL Injection Attacks?

Even developers who think they’ve protected a website from SQL injection can be surprised when a hacker finds a hole in security. The best way to defend against SQL injection is to use stored procedures. These are called “prepared statements” in PHP. Stored procedures are pre-designed SQL functions that you can reuse throughout your entire website code. They make it faster to create websites that use backend databases, because you don’t need to recreate SQL code for each page. Stored procedures take more time to set up initially, so most coders choose the faster way of development and build dynamic statements such as the one used in the SQL injection examples.

When a hacker attempts to use SQL injection and you use stored procedures, the apostrophe characters are sent as literals. This means that instead of terminating a string and executing malicious SQL, the database inserts the apostrophe as part of the data’s value.

Each programming language has its own form of stripping or escaping special characters in SQL statements. You must use these functions when you don’t use stored procedures in dynamically built SQL code. For instance, most WordPress developers use dynamically built SQL statements in their plugins. Poorly formed SQL statements are why WordPress is constantly a target of attack from hackers.

If you own a WordPress site

Update your WordPress installation often. New versions patch any current security issues. You should also update any plugins. Plugins are written by seasoned as well as new developers, so be wary of the plugin you install on your site. Even big plugin developers have found security flaws in their software. If the plugin developer does not keep up with the latest WordPress versions, you should probably choose a different plugin for website functionality.

You can also use penetration tools to help identify security holes such as SQL injection in a website. Most hackers use automated scripts that run through your forms and identify where you are susceptible. Decent PenTesters however, will do quite a bit more. These penetration tools perform the same methods. Just make sure the penetration tools have several SQL scripts that identify simple and complex SQL injection vulnerabilities.

SQL injection is a serious security flaw on the web, so always make sure your site is secured. If you think you’re not a target, think again. Hackers just need to run scripts to automate a search on the web to find vulnerable sites. It doesn’t take much to protect from SQL injection, but it can take days or even weeks to recover if you are successfully hacked.