What are SQL injections and how can you prevent such attacks?

The danger of SQL injections has been known for 14 years. And yet, such attacks are still one of the greatest dangers to dynamic websites, which can cause serious damage, including the destruction of the data in your database. But what exactly are they? And how can you prevent them?

1. What is an SQL injection?

What is an SQL injection?On the modern web, you can find lots of dynamic sites, which are generated on the fly. A scripting language such as PHP retrieves content from a database and puts it together based on instructions or statements. Such instructions contain the code written by the developer, as well as the data the visitor enters via a web form, e. g. when he enters his login and password.

If a developer is not careful enough when writing his statements, ill-intentioned people will have the opportunity to slip malicious code into the data they enter, allowing them to execute other instructions.

In short, hackers use certain characters or formulas in the input field of a form, as a result of which their input is no longer seen as a simple string, but rather as a function.

 

These signs can e.g. be a danger:

  • * (asterisk) is an instruction for the SQL database to show all columns for the selected row in the database
  • = (equals) is an instruction for the SQL database to show only the value(s) that match(es) the searched string
  • ' (simple quote) is used to tell the SQL database where the search string begins or ends
  • ; (semicolon) is used to tell the SQL parser that the current statement is terminated
  • -- (double dash) is used to tell the SQL parser that the rest of the row is a comment and should not be executed.

You can certainly imagine what hackers may do using these characters!

2. What are the dangers of an SQL injection?

As previously stated, an SQL injection is an attack in which extra code is added to the input. If no precautions are taken in the script to anticipate such situations, the code will be executed, leading to dangerous situations.

The attacker can:

  • Add, edit or read the database content
  • Read the source code of files on the database server
  • Write files to the database server.

 

Here is an example that should clearly illustrate this:

txtUserId = getRequestString("UserId");

txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;

This code is used to create an SQL statement whose purpose is to select a user with a specific ID. But what if the user enters the code below as his UserID in the form:

105 OR 1=1

The statement then becomes:

SELECT * FROM Users WHERE UserId = 105 OR 1=1;

The consequence of this is that, although the initial purpose was to show a single row (that of the UserID entered as the input), ALL rows are now shown, because the OR condition is always true (1=1). And when the Users table does not only contain user names but also passwords, you are in big trouble!

 SQL injection cartoon

3. How can you protect yourself from an SQL injection?

The three key methods to protect your website from such injections are:

  • Data type casting (determining in advance which data type you expect for the variable). Is it an integer, a string or a boolean (true/false)? In PHP, for an integer, you can e.g. do this as follows:

$id = (int)$_GET['id'];

  • Escape strings (escaping special characters such as '). In order to do this in PHP, you can use the following function:

mysqli_real_escape_string()

  • Prepared Statements (determining in advance the structure of a query). In PHP, you can create a prepared statement as follows:

mysqli_stmt_init

Then, you can execute the prepared statement using

mysqli_stmt_prepare

 

For further information, please read data type casting, prepared statements and W3Schools about SQL injections.

By the way, did you know that picking Combell as your hosting provider allows you to benefit from the best security on the market? And that Combell even offers automatic patching that also protects your CMS hosting (WordPress, Magento, Joomla and Drupal) from newly discovered vulnerabilities?

 

Discover the ultimate protection that comes with Combell’s hosting services