Effective Prevention Methods for SQL-injection

SQL injection

SQL injection in WordPress plugins is a vulnerability in which an attacker can inject and execute malicious SQL code in the site database. This usually happens if user input (such as form data or URL parameters) is not sufficiently checked or filtered before being used in SQL queries.

WordPress plugins often add user-defined functionality that interacts with the database, and if the plugin developers do not follow the rules of secure programming, an attacker can take advantage of this vulnerability. An example would be vulnerable code that uses raw data from user input directly in SQL queries.

WordPress plugins can have several types of SQL injections that occur due to insufficient processing of user input before using it in SQL queries. Here are the main types of SQL injections that can occur in WordPress plugins:

Classic SQL injection:

This type of injection allows an attacker to combine the results of several queries into one, thereby gaining access to data from other database tables that are not related to the current query.

global $wpdb;

$user_id = $_GET['user_id'];

$query = "SELECT * FROM wp_users WHERE id = $user_id";

$results = $wpdb->get_results($query);

The above code is an example of an SQL Injection (Sql) vulnerability.  The data entered by the user in $_GET[‘id’] is sent directly to the database without clearing or escaping. This allows an attacker to send commands directly to the database.

SQL injection payload:

1 UNION SELECT username, password FROM wp_users--

Due to incorrect cleaning and lack of escaping in the database, the query will be entered as follows:

SELECT * FROM wp_users WHERE id = 1 UNION SELECT username, password FROM wp_users--;

To prevent this vulnerability, you can replace the code as follows:

global $wpdb;

$user_id = $_GET['user_id'];
$escaped_user_id = esc_sql($user_id);

$query = $wpdb->prepare(
    "SELECT * FROM wp_users WHERE id = %d",
    $escaped_user_id
);

$results = $wpdb->get_results($query);

To protect against SQL injections in WordPress, it is best to use both $wpdb->prepare() and esc_sql().

Vulnerability of Time-based SQL injection

This type of injection uses time delays to determine if there is a vulnerability in the request. An attacker can embed SQL conditions that slow down query execution if the condition is met.

An example of a vulnerability:

global $wpdb;

$email = $_GET['email']; 

$query = "SELECT * FROM wp_users WHERE user_email = '$email'";

$results = $wpdb->get_results($query);

If the user passes a specially generated email address in the request parameter, an attacker can use such an injection to cause a delay (a request with a timeout) in the database and determine by time whether a certain condition exists (for example, whether such a user exists in the database). Time-based SQL Injection In this example, there is a potential vulnerability for SLEEP() in MySQL.

The payload will look like this:

http://example.com/?email=te**@do****.com'; OR IF(1=1, SLEEP(5), 0)—

IF(1=1, SLEEP(5), 0): This condition is always true (because 1=1 is always true), and if the condition is met, MySQL will call the SLEEP(5) command, which will result in a 5-second delay in the response from the server.

To prevent this vulnerability, you can replace the code as follows:

global $wpdb;

$email = sanitize_email($_GET['email']);

$query = $wpdb->prepare("SELECT * FROM wp_users WHERE user_email = %s", $email);

$results = $wpdb->get_results($query);

$wpdb->prepare() Using prepared expressions ensures that the data is safely escaped before executing the SQL query.

An example of a Time-Based SQL injection vulnerability is CVE-2024-4145 ( https://research.cleantalk.org/cve-2024-4145/ )

A vulnerability was found in the plugin that allows you to embed a Time-Based SQL query and get a delay in the response of the website, thereby an attacker can manipulate the time for database responses to certain conditions.

Due to the lack of escaping and filtering of quotes, an attacker could implement an SQL-injection Time-Based attack using the following payload:

select_tables%5B%5D=(SELECT+9255+FROM+(SELECT(SLEEP(1-(IF(44=44,0,5)))))cCQl)

SQL injection protection features:

wpdb function::prepare()

It is a tool for protection against SQL injections and for creating secure SQL queries. It helps to prepare SQL queries with substituted parameters, automatically escaping user data and checking their type, which prevents the possibility of executing malicious SQL commands transmitted through user input.

The function works as follows:

• Uses an SQL query string with placeholders, which will be replaced with values from $args. The query can contain placeholders for various types of data, such as strings, numbers, or floating numbers. Placeholders can be designated as %s for strings, %d for integers, and %f for floating point numbers.
• The function automatically escapes the transmitted values, depending on their type, allowing you to avoid embedding an attacker into the SQL query of the website.

Example:

global $wpdb;

$user_id = 123;

$query = $wpdb->prepare( "SELECT * FROM wp_users WHERE ID = %d", $user_id );

$results = $wpdb->get_results($query);

In this example, %d is a placeholder for an integer (the data type for $user_id). The prepare function automatically converts $user_id to an integer and escapes it.

An example with several parameters:

If multiple parameters are used in the request, then you can pass them as multiple arguments to the prepare() function:

In this example, both values are strings, and they will be safely inserted into the query.

The wpdb::prepare() function is an integral part of security when working with a database in WordPress. It ensures that all data passed to SQL queries is safely escaped and processed, which prevents SQL injections and other threats. Using prepared expressions with the prepare function is a mandatory practice when developing secure plugins and themes for WordPress.

The esc_sql function

The esc_sql() function in WordPress is used to escape SQL queries to prevent SQL injections. It clears the string, preparing it for safe use in SQL queries, removing or escaping potentially dangerous characters such as single quotes and other special characters that can be used to manipulate queries.

Unlike the wpdb::prepare() function, which is used to securely insert parameters into queries, esc_sql() is used to escape data that is already present in the query and can be transmitted via a form, URL, or other sources. It helps to prevent the use of characters such as single quotes (‘) in queries, which can be used to perform SQL injections.

Escaping data with esc_sql() ensures that escaping data with esc_sql() ensures that the string containing user input is safe to use in database queries.An oca containing user input will be safe to use in database queries.

$user_input = $_GET['search'];
$escaped_input = esc_sql($user_input);
$query = $wpdb->prepare(
    "SELECT * FROM wp_posts WHERE post_title LIKE %s",
    '%' . $escaped_input . '%'
);
$results = $wpdb->get_results($query);

In this example, the asc_sql() function escapes the string entered by the user in the search parameter before using it in the query. This prevents SQL injections from being performed, such as using quotation marks or other malicious characters to manipulate the query.

The difference between esc_sql() and wpdb::prepare():

esc_sql() is used to escape user data that is directly inserted into an SQL query. This avoids problems with quotes, comments, and other potentially dangerous characters. However, manually escaping data makes queries less readable and may not protect against all types of SQL injections.

wpdb::prepare() is used to prepare queries with placeholders (for example, %s, %d, %f). It automatically escapes and safely inserts data into the query, and is the preferred method when working with parameterized queries. wpdb::prepare() is more secure, as it guarantees the correct processing of all data types and avoids errors during escaping.

esc_sql() should be used in cases where you want to escape values that will be inserted into the query manually (for example, values obtained from a URL, form, or other sources). However, to protect against SQL injections, it is recommended to use wpdb::prepare() with placeholders, as this is a safer and more readable way to work with queries.

The esc_sql() function is an important tool for escaping data in SQL queries in WordPress. It helps prevent SQL injections by ensuring query security, but it is not as convenient and secure as using prepared expressions via wpdb::prepare(). Ideally, you should use wpdb::prepare() with placeholders to work with user data, and esc_sql() when you need to manually escape strings before inserting them into a query.

Conclusion

To effectively protect against SQL injections in WordPress, you need to use built-in security features such as $wpdb->prepare() to work with prepared queries. These functions automatically shield the data, which prevents the introduction of malicious code into SQL queries. It is also important to shield user data using the $wpdb->esc_sql() function when working with queries to avoid unwanted characters or operations that can be used for attacks. Dynamic SQL queries that directly include user data should be avoided, as this increases the likelihood of vulnerabilities. Using prepared queries always provides a higher level of security when interacting with the database.