SQL Injection Prevention

Original author: Joanna
  • Transfer

SQL injection (also known as "Violation of the integrity of the structure of the SQL-query") are one of the most common and most dangerous vulnerabilities in the security issue. SQL injections are very dangerous because they open the door to hackers in your system via the web interface, and allow unlimited access: for example, delete tables, modify the database, and even gain access to the internal corporate network. SQL injection is a purely software bug, and has nothing to do with the host provider. So, you were looking for safe JSP hosting, PHP hosting, or any other, you should know that only developers, not the host provider, are responsible for the prevention of SQL injections.

Why do SQL injections happen?

SQL injections are a very common problem, but ironically, they are also easy to prevent. SQL injections are so widespread, since there are so many places where the vulnerability can be present, and if the injection is successful, the hacker can get a good reward (for example, full access to the data in the database).

The risk of SQL injection occurs whenever a programmer creates a dynamic query to the database containing user input. This means that there are two ways to prevent SQL injection:

• Do not use dynamic database queries.
• Do not use user data in queries.

Everything seems to be simple, but these are theories; in practice, it is impossible to refuse dynamic queries, as well as to exclude user data input. But this does not mean that it is impossible to avoid injections. There are some tricks and technical features of programming languages ​​that will help prevent SQL injections.

What can be done to prevent SQL injection

Although the decision is highly dependent on the particular programming language, the general principles for preventing SQL injection are similar. Here are a few examples of how this can be done:

• Use dynamic SQL only when absolutely necessary.

A dynamic query can almost always be replaced with prepared statements, parameterized queries, or stored procedures. For example, instead of dynamic SQL, in Java you can use PreparedStatement () with bound parameters, in .NET you can use parameterized queries like SqlCommand () or OleDbCommand () with bound parameters, and in PHP you can use strongly typed PDO parameterized queries (using bindParam ()).

In addition to prepared statements, you can use stored procedures. Unlike prepared statements, stored procedures are stored in the database, but in both cases, the SQL query is first determined and parameters are passed to it.

• Validation of entered data in queries.

Validation of data entry is less efficient than parameterized queries and stored procedures, but if there is no way to use parameterized queries and stored procedures, then it is better to check the entered data - this is better than nothing. The exact syntax for using input validation is highly dependent on the database; read the docks on your specific database.

• Do not rely on Magic Quotes.

Enabling the magic_quotes_gpc parameter can prevent some (but not all) SQL injections. Magic quotes is not the last defense, and even worse, sometimes they are turned off and you do not know about it, or do not have the ability to turn it on. That is why it is necessary to use code that will escape quotation marks. Here is a piece of code, proposed by John Lee: • Regular and timely installation of patches. Even when your code does not have vulnerabilities, there is a database server, server operating system, or developer utilities that may have vulnerabilities. That is why always install patches immediately after they appear, especially if this is a fix for SQL injections. • Remove all functionality that you are not using.
$username = $_POST['username'];
$password = $_POST['password'];
if (!get_magic_quotes_gpc()) {
$username = addslashes($username);
$password = addslashes($password);

The database server is a complex creation and has much more functionality than you require. And as far as security is concerned, here the principle “the more the better” does not work. For example, the extended system procedure xp_cmdshell in MS SQL gives access to the operating system, and this is just a dream for a hacker. That is why this function needs to be disabled, like any others, which make it easy to abuse the functionality.

• Using automated tools for finding SQL injections.

Even if the developers followed all the above rules in order to avoid dynamic queries with the substitution of unverified user data, you still need to confirm this with tests and checks. There are automated testing tools for detecting SQL injections, and there is no excuse for those who do not use these tools to verify procedures and queries.
One of the simple tools (and one of the more or less reliable) for detecting SQL injection is an extension for Firefox called SQL Inject ME . After installing this extension, the tool is available by right-clicking in the context menu, or from the menu Tools → Options. The SQL Inject ME workspace is shown in the following screenshot, and you can see how many kinds of tests you can do:

You can choose which test to run and with what parameters. At the end of the test, you will see a report on the test results.

There are many parameters that you can set for the SQL Inject ME extension, which are shown in the following two screenshots: As you can see, there are many solutions (and above all, all simple ones) that you can take to clear the code of potential vulnerabilities to SQL injections. Do not neglect these simple things, as you endanger not only your security, but all the sites hosted on your host provider.

Also popular now: