SQL Injection LIKE Query: Understanding and Preventing Risks

In the realm of web security, the way an application interacts with its database can either be its strongest shield or its weakest link. One of the most persistent threats remains SQL injection (SQLi), a vulnerability that occurs when an attacker can interfere with the queries that an application makes to its database. While many developers are familiar with basic union-based or error-based injections, a more subtle risk exists within the use of the LIKE operator in SQL queries.

The LIKE operator is fundamental for implementing search functionality, allowing users to find records that match a specific pattern. However, when user input is concatenated directly into a LIKE clause without proper sanitization or parameterization, it opens a door for attackers to manipulate the query logic. This can lead to unauthorized data exposure, performance degradation, or even full database compromise depending on the environment's configuration.

The Mechanics of the SQL LIKE Operator

Before diving into the vulnerabilities, it is essential to understand how the LIKE operator works. In standard SQL, LIKE is used in a WHERE clause to search for a specified pattern in a column. To make these searches flexible, SQL employs two primary wildcards:

• The Percent Sign (%): This represents zero, one, or multiple characters. For example, 'a%' matches any string starting with 'a'.
• The Underscore (_): This represents a single character. For example, '_at' matches 'cat', 'hat', or 'bat'.

When a developer implements a search bar, they often write a query similar to: SELECT * FROM products WHERE product_name LIKE '%" + userInput + "%';. If a user searches for 'phone', the resulting query is SELECT * FROM products WHERE product_name LIKE '%phone%';. This works as intended for legitimate users, but it creates a significant loophole for those with malicious intent.

How SQL Injection Occurs in LIKE Clauses

The vulnerability arises when the application treats user-supplied wildcards as part of the command rather than as literal text. Because the percent sign and underscore have special meanings to the database engine, an attacker can provide these characters to alter the scope of the search.

For instance, if an attacker enters a single percent sign (%) into the search field, the query becomes SELECT * FROM products WHERE product_name LIKE '%%%';. In most databases, this effectively matches every single record in the table. While this might seem harmless in a public product list, imagine this happening on a sensitive table, such as a user directory or an internal logs table. By using wildcards, an attacker can bypass intended filters to dump large amounts of data that should have been restricted.

Furthermore, if the input is not properly escaped, an attacker can use the single quote (') to break out of the string literal entirely. This allows them to append entirely new commands to the query. By integrating security best practices into the development lifecycle, teams can identify these patterns before they reach production.

Data Harvesting via Pattern Guessing

One of the more dangerous applications of LIKE-based injection is the ability to extract data character by character. This is often a component of 'Blind SQL Injection'. If an attacker wants to discover a secret token or a password stored in the database, and the application only tells them whether a result was found or not, they can use the LIKE operator to guess the value.

The attacker might send a series of requests:

• 'admin_token LIKE 'a%'
• 'admin_token LIKE 'b%'
• 'admin_token LIKE 'c%'

If the application returns a 'result found' message for 'a%', the attacker knows the token starts with 'a'. They then move to the second character: 'aa%', 'ab%', 'ac%', and so on. While tedious, this process is easily automated with scripts, allowing an attacker to exfiltrate sensitive strings without ever seeing a direct database error.

Performance Impacts and Denial of Service

Beyond data theft, SQL injection via LIKE queries can be used to launch Denial of Service (DoS) attacks against a database. The way database engines process LIKE patterns can be computationally expensive, especially when wildcards are placed at the beginning of the search string (leading wildcards).

When a query starts with a wildcard (e.g., LIKE '%keyword'), the database cannot use traditional indexes. Instead, it must perform a full table scan, reading every single row to see if it matches the pattern. If an attacker submits a query with multiple complex wildcards or combines this with other heavy operations, they can spike the CPU and memory usage of the database server. In a high-traffic environment, a few such requests can slow the system to a crawl, effectively locking out legitimate users.

The Danger of 'Expensive' Patterns

Some database engines may struggle with specific combinations of wildcards and long strings. By crafting a query that forces the engine into a catastrophic backtracking scenario or an incredibly inefficient scan, an attacker can create a bottleneck. This is particularly effective in systems where the search functionality is exposed to the public and doesn't have strict rate limiting or input length restrictions.

Effective Prevention Strategies

Preventing SQL injection in LIKE queries requires a multi-layered approach. It is not enough to simply filter out a few keywords; the architecture of the query itself must be secure.

1. Use Parameterized Queries (Prepared Statements)

The most effective defense against all forms of SQL injection is the use of parameterized queries. Instead of concatenating user input into the query string, parameters act as placeholders. The database treats the parameter value strictly as data, not as executable code.

For a LIKE query, you should define the pattern within the application logic and then pass the entire pattern as a single parameter. For example, in a language like Java or C#, you would create a string "%" + userInput + "%" and pass that as the value for the parameter in the prepared statement. Because the database engine knows that the parameter is a value, any quotes or semicolons provided by the user are treated as literal characters and cannot break the query structure.

2. Escaping Wildcard Characters

While parameterized queries prevent the attacker from breaking out of the string, they do not prevent the attacker from using the % and _ characters to return more data than intended. To solve this, you must escape these special characters if you want them to be treated literally.

Most SQL dialects provide an ESCAPE clause. By specifying an escape character (e.g., a backslash or a pipe), you can tell the database to ignore the special meaning of the following character. For example: SELECT * FROM users WHERE username LIKE '%\_%' ESCAPE '\';. In this case, the underscore is treated as a literal character rather than a wildcard.

Developers should implement a helper function that automatically escapes % and _ in any user input destined for a LIKE clause. This ensures that if a user searches for "100%_off", the database looks for that exact string instead of returning every record in the system.

3. Input Validation and Sanitization

Input validation should be the first line of defense. Define a strict policy for what constitutes valid input for your search fields. If a search field should only contain alphanumeric characters, reject any input containing symbols. While validation is not a replacement for parameterized queries, it reduces the attack surface significantly.

• Length Limits: Set a reasonable maximum length for search queries to prevent DoS attacks involving massive strings.
• Type Checking: Ensure the input matches the expected data type.
• Allow-listing: If the search is limited to a few categories, use a dropdown menu instead of a free-text field.

4. Implementing Web Application Firewalls (WAF)

A WAF can provide an additional layer of protection by detecting common SQLi patterns in incoming HTTP requests. Modern WAFs can identify attempts to use common SQL keywords (like UNION, SELECT, or DROP) or suspicious patterns of wildcards in query strings. While a WAF can be bypassed by a sophisticated attacker, it serves as an excellent deterrent and provides early warning signs of an ongoing attack.

Conclusion

The LIKE operator is an invaluable tool for creating user-friendly search experiences, but it carries inherent risks if handled carelessly. From the simple leakage of records through wildcard abuse to the sophisticated extraction of data via blind injection and the potential for database-level denial of service, the vulnerabilities are diverse. However, these risks are entirely manageable.

By prioritizing parameterized queries, implementing rigorous wildcard escaping, and adhering to strict input validation, developers can build robust applications that provide powerful search capabilities without sacrificing security. The key is to never trust user input and to always maintain a clear separation between the data being processed and the commands being executed by the database engine.

Frequently Asked Questions

How can I stop users from using % in search boxes?

The best way to handle this is to escape the percent sign in the user's input before passing it to the database. You can use the SQL ESCAPE clause to designate a character (like a backslash) that tells the database to treat the following '%' as a literal character rather than a wildcard. Additionally, you can use a replacement function in your application code to prepend an escape character to any '%' or '_' characters provided by the user.

Does using a prepared statement prevent wildcard injection?

A prepared statement prevents 'structural' SQL injection, meaning the attacker cannot break out of the string to execute new commands. However, it does not stop 'logical' injection via wildcards. If you pass '%userInput%' as a parameter, the database still interprets the '%' characters as wildcards. To prevent a user from returning all records by entering a '%', you must still manually escape the wildcard characters within the parameter value.

What is the performance difference between 'keyword%' and '%keyword'?

Searching with a trailing wildcard ('keyword%') allows the database to use an index, making the query very fast. However, a leading wildcard ('%keyword') forces the database to perform a full table scan because it cannot know where the pattern starts. This makes leading wildcards significantly slower and more resource-intensive, which is why they are often targeted in Denial of Service attacks.

Can a LIKE query be used for blind SQL injection?

Yes, it is a common technique. Attackers can use the LIKE operator to guess values one character at a time. By observing whether the application returns a result or an error (or a different page load time), they can determine if a specific character matches the start of a hidden string. For example, checking if a password starts with 'a%', then 'b%', and so on, until the correct character is identified.

Is it better to use REGEXP instead of LIKE?

REGEXP (Regular Expressions) provides much more power and flexibility than LIKE, but it can also be more dangerous. Regular expressions are often more computationally expensive and can be susceptible to 'Regular Expression Denial of Service' (ReDoS) if user input is allowed to define the pattern. For simple pattern matching, LIKE is generally safer and more performant, provided that input is properly escaped and parameterized.