SQL injection prevention

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

Why does SQL injection occur?

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

The risk of SQL injections occurs whenever a programmer creates a dynamic database query that contains user input. This means 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 input. But this does not mean that it is impossible to avoid injections. There are some techniques and technical features of programming languages ​​that will help prevent SQL injection.

What can be done to prevent SQL injection?

Although the solution largely depends on the specific programming language, the general principles for preventing SQL injection are similar. Here are some examples of how this can be done:

• Use dynamic queries only when absolutely necessary.

A dynamic query can almost always be replaced with prepared statements (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, such as SqlCommand () or OleDbCommand () with bound parameters, and in PHP you can use PDO with strong typing parameterized queries (using bindParam ()).

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

• Validation of entered data in queries.

Verification of data entry is less effective than parameterized queries and stored procedures, but if it is not possible to use parameterized queries and stored procedures, then it is better to check the entered data anyway - this is better than nothing. The exact syntax for using validation of input data is highly dependent on the database; read the docks on your particular 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 protection, and even worse, sometimes they are turned off and you don’t know about it, or you don’t have the ability to turn it on. That is why you need to use code that will escape quotes. Here is a piece of code proposed by John Lee:
$username = $_POST['username'];
$password = $_POST['password'];
if (!get_magic_quotes_gpc()) {
$username = addslashes($username);
$password = addslashes($password);
}

• Regular and timely patching.

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 fixes immediately after they appear, especially if this is a SQL injection fix.

• Remove all functionality that you do not use.

The database server is a complex creation and has much more functionality than you need. And as far as safety is concerned, the principle “the more, the better”, does not work. For example, the xp_cmdshell extended system procedure in MS SQL gives access to the operating system, and this is just a dream for a hacker. That is why this function should be disabled, as well as any other functions that allow easy functionality misuse.

• Use of automated tools for finding SQL injection.

Even if the developers followed all the above rules, 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 identifying SQL injections, and there is no excuse for those who do not use these tools to validate procedures and queries.
One of the simplest tools (and one of the more or less reliable ones) for detecting SQL injections is an extension for Firefox called the SQL Inject ME . After installing this extension, the tool is available by right-clicking in the context menu, or from the Tools → Options menu. The SQL Inject ME workspace is shown in the following screenshot, and you can see how many types of tests you can conduct:





You can choose which test to run and with which 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 clean code from potential vulnerabilities in SQL injections. Do not neglect these simple things, because you put at risk not only your security, but also all the sites that are hosted on your host provider.