What is SQL injection and how to prevent SQL injection?

A Structured Query Language (SQL) is the command-and-control language for relational databases such as Microsoft SQL Server, Oracle and MySQL. In modern web development, these databases are used as back end of web applications and content management systems. That means, both content and behavior of many websites is built on data in a database server.

SQL injection is a type of security vulnerability that occurs when an attacker injects malicious SQL statements into an application's input fields. This can lead to unauthorized access to sensitive data or even the deletion of data.

SQL injection attacks are typically carried out by inserting SQL code into an application's input fields. For example, an attacker might insert SQL code into a login form's username field in order to bypass authentication and gain access to the system. Or they might insert SQL code into a search form's input field in order to retrieve sensitive data from the database.

SQL injection attacks can be especially damaging if an attacker is able to gain administrative access to the database. In this case, they may be able to modify or delete data, create new users with elevated privileges, or even gain control of the entire system.

There are two main types of SQL injection attacks:

• First - when the attacker receives the desired result immediately, either by direct response from the application they are interacting with or some other response mechanism, such as email.
• Second - when the attacker injects some data that will reside in the database, but the payload will not be immediately activated.

Recently, SQL injection has become a popular choice for hackers, used in attacks against Sony Pictures, PBS, Microsoft, Yahoo, LinkedIn, and the CIA.

The primary defenses that are used to prevent SQL injection attacks:

1. Prepared Statements

Parameterized queries are a type of prepared statement that allow you to define SQL queries with placeholders for user input. When you execute the query, you pass in the user input as parameters, which are then bound to the placeholders. This prevents attackers from injecting SQL code into the query, because the user input is treated as data and not as part of the SQL statement.

2. Sanitize user input

In addition to using parameterized queries, you should also sanitize user input to remove any characters that could be used in SQL injection attacks. This includes characters such as quotes, semicolons, and dashes. You can use libraries such as the sqlstring library in Node.js to sanitize user input.

3. Use a web application firewall (WAF)

A WAF can help protect against SQL injection attacks by monitoring incoming requests and blocking those that contain suspicious or malicious SQL statements.

The popular example is ModSecurity, which is a open source module and available for Apache, Microsoft IIS, and nginx web servers. ModSecurity provides a sophisticated and ever-evolving set of rules to filter potentially dangerous web requests. Its SQL injection defenses can catch most attempts to sneak SQL through web channels.

4. Stored Procedures

A stored procedure is defined and stored in the database itself, and then called from the application rather than something that a user is allowed to enter.

5. Limit database permissions

Limit the permissions for database users to only what is needed for their role. For example, a user who only needs to read data from the database should not be given write or delete permissions.

6. Use least privilege principle

Users should be given only the minimum privileges necessary to perform their job functions. This minimizes the impact of a successful attack, should one occur.

7. Suppress error messages

Error messages are an important reconnaissance tool for attackers, so keep them local if possible. If external messages are necessary, keep them generic.

8. Keep your software up to date

Make sure you keep your software, including your database software, up to date with the latest security patches and updates.

Any one of these defenses significantly reduces the chances of a SQL injection attack. Implementing all of them will provide a high degree of SQL injection prevention. So, have you ever experienced an SQL Injection attack at your organization? How did your organization combat the attack - please share in the comment section