SQL injection

SQL injection is a major concern when developing a Web application. It occurs when the application accepts a malicious user input and then uses it as a part of SQL statement to query a backend database.

An attacker can inject SQL control characters and command keywords (e.g., single quote (‘), double quote (“), equal (=), comment (- -), etc.) to change the query structure. Using these control characters with common SQL commands (e.g., SELECT, FROM, DELETE, etc.) enables access or retrieval of data elements from a backend database server.

A successful attack requires a Web application to include malicious code from an attacker in a SQL statement. The malicious code usually comes from an untrusted source. In some cases, internal system databases can also be the source of malicious data. When malicious SQL statements execute against a backend database, an attacker can modify or access the database.

The best way to find out if an application is vulnerable to injection is to verify that all use of interpreters clearly separates untrusted data from the command or query. For SQL calls, this means using bind variables in all prepared statements and stored procedures, and avoiding dynamic queries.

What is prepared statement and Stored Procedures?

Prepared statements, also known as parameterized statements or parameterized SQL, can be thought of as a template for SQL statements.

Stored procedures are the SQL statements defined and stored in the database itself and then called from the application.

 

What exactly happens when SQL is “prepared”?

Example of a prepared statement in Java using JDBC:

java.sql.PreparedStatement stmt = 
connection.prepareStatement(
"SELECT * FROM table WHERE EMAIL = ?");

/* The statement below sets "?" to an actual value that
is stored in the email variable, we are also assuming
that the email variable is set beforehand: */

stmt.setString(1, email);

Prepared SQL is created by calling the respective prepare method in each language, as you can see in the examples above. The prepared SQL template is sent to the DBMS (whether it’s MySQL, DB2, or whatever) with the placeholder values (the “?”) left blank. Then, the DBMS will parse, compile, and perform query optimization on the template. After that, the DBMS will store the result, but it can not execute the result because it, of course, does not have any values to execute with since there is no data in the placeholders/parameters. The SQL is only executed once the respective execute function is called and data is passed in for the parameters.

 

What are the advantages of using prepared statements?

Prepared statements provide 2 primary benefits. The first is that they provide better performance. Even though a prepared statement can be executed many times, it is is compiled and optimized only once by the database engine. Because of the fact that a prepared statement does not have to be compiled and optimized each and every time the values in the query change, it offers a distinct performance advantage. But, keep in mind that not all query optimization can occur when a prepared statement is compiled. This is because the best query plan may also depend on the specific values of the parameters being passed in. The best query plan may also change over time, because of the fact that the database tables and indices also change over time.

 

Why are prepared statements so effective against SQL injection?

The second advantage of using prepared statements is that they are the best solution to preventing SQL injection attacks the reason that prepared statements help so much in preventing SQL injection is because of the fact that the values that will be inserted into a SQL query are sent to the SQL server after the actual query is sent to the server. In other words, the data input by a potential hacker is sent separately from the prepared query statement. This means that there is absolutely no way that the data input by a hacker can be interpreted as SQL, and there’s no way that the hacker could run his own SQL on your application. Any input that comes in is only interpreted as data, and can not be interpreted as part of your own application’s SQL code – which is exactly why prepared statements prevent SQL injection attacks..

 

What is Dynamic and Static Query? When to use one?

A dynamic query is automatically refreshed each time it is used. When you use a dynamic query, the program searches the database for any new records meeting the criteria you specify and adds them to the results. Use dynamic queries whenever you need the query to remain current (automatically refresh).

For example, you have a query of volunteers. Each time you use that query for a report or mailing, you want to be sure that it includes everyone who is currently marked as a volunteer and does not include anyone who is no longer marked as a volunteer. Because a dynamic query automatically updates itself (refreshes), you know that it is current. Most queries that you run will be dynamic queries.

• A dynamic query is updated every time it is run or used in other modules such as Mail, Reports, and Export.
• The results of a dynamic query always reflect changes made since the last time the query was run
• Because dynamic queries consist of an ever-changing group of records, an exact number of records does not appear on the query information screen. Instead, the number appears as N/A or ## (approx.).

A static query is like taking a snapshot of your database at the time the query is created. Only the records selected when the query is first created will be included when the query is used in the program. Use static queries when doing global changes. The same constituents are still in the static query so you can easily find them again if the need arises. Output queries and merged queries are static.

• A static query contains only those records which met the specified criteria at the time the query was created
• Static keys are pointers to each record in your query. The number of static keys equals the number of records in your query
• A static query is not updated when used in other modules such as Mail or Reports
• Static queries can be re-run through Query and will be updated with new records meeting the criteria in the query

 

How to mitigate SQL Injection?

Use of prepared statements with parameterized queries is a strong control to mitigate an attack. Instead of writing dynamic queries—which fails to differentiate between application code and data—prepared statements force developers to use static SQL query and then pass in the external input as a parameter to query. This approach ensures the SQL interpreter always differentiates between code and data.

Stored procedures are the SQL statements defined and stored in the database itself and then called from the application. Developers are usually only required to build SQL statements with parameters that are automatically parameterized. However, it’s possible for a developer to generate dynamic SQL queries inside stored procedures. Implement stored procedures safely by avoiding dynamic SQL generation inside.

Input validation. A common source of SQL injection is maliciously crafted external input. As such, it’s always a good practice to only accept approved input—an approach known as input validation. To protect against it, there are two variants of input validation: blacklist validation and whitelist validation.

Blacklist validation tests the external input against a set of known malicious inputs. An application compiles a list of all malicious inputs, and then verifies the external input against the compiled list. Therefore, it’s easy for an attacker to bypass blacklist validation since they can come up with multiple variants of malicious input that may not be part of the complied blacklist.

Whitelisting is a much better approach to mitigate the risk of SQL injection. Whitelist validation tests an external input against a set of known, approved input. With whitelist input validation, the application knows exactly what’s desired and rejects other input values that fail a test against the known, approved input.

Principle of least privilege. This is a standard security control that helps minimize the potential damage of a successful SQL injection attack. Application accounts shouldn’t assign DBA or admin type access onto the database server. Additionally, depending on access requirements, they should be restricted to least privileged access. For example, accounts that only require read access are only granted read access to the table they need to access. This ensures that if an application is compromised, an attacker won’t have the rights to the database through the compromised application.

For more information and mitigation methods click this link.