Secure String Concatenation for MySQL LIKE Queries in PHP and SQL Injection Prevention

Problem Context and Common Errors

In PHP and MySQL integration, dynamically constructing SQL queries is a frequent task. A typical scenario involves using the LIKE operator for pattern matching queries. Developers often need to generate query conditions based on variable values. However, incorrect string concatenation methods can lead to query failures or security vulnerabilities.

Error Case Analysis

Consider the following query requirement: count all records in a database that start with a specific prefix. The initial hard-coded query works correctly:

$query = mysql_query("SELECT * FROM table WHERE the_number LIKE '123%'");
$count = mysql_num_rows($query);

When attempting to replace the prefix '123' with a variable $prefix, developers might incorrectly use concatenation operators:

$prefix = "123";
$query = mysql_query("SELECT * FROM table WHERE the_number LIKE $prefix.'%'");

This approach uses concatenation operators inside double-quoted strings, causing syntax errors. Even removing the concatenation operator and directly appending:

$query = mysql_query("SELECT * FROM table WHERE the_number LIKE $prefix'%'");

still fails to parse correctly because the variable $prefix and the string '%' lack necessary quote separation.

Correct Solution

The proper method is to embed the variable directly within the double-quoted string while ensuring the pattern string's integrity:

$query = mysql_query("SELECT * FROM table WHERE the_number LIKE '$prefix%'");

This approach leverages PHP's double-quoted string variable interpolation feature, replacing $prefix with its value directly in the SQL string to form a valid LIKE pattern.

SQL Injection Risks and Protection

Although the above method resolves syntax issues, directly embedding user input or variables into SQL queries poses significant security risks. Attackers might execute SQL injection attacks through carefully crafted inputs, for example:

$prefix = "123'; DROP TABLE table; --";

This would transform the query into:

SELECT * FROM table WHERE the_number LIKE '123'; DROP TABLE table; --%'

executing malicious operations.

Secure Query Construction

To prevent SQL injection, variables must be properly escaped. Using the mysql_real_escape_string function escapes special characters in strings:

$escaped_prefix = mysql_real_escape_string($prefix);
$query = mysql_query("SELECT * FROM table WHERE the_number LIKE '$escaped_prefix%'");

To further enhance code readability and security, it is recommended to use the sprintf function for string formatting:

$sql = sprintf("SELECT * FROM table WHERE the_number LIKE '%s%%'",
               mysql_real_escape_string($prefix));
$query = mysql_query($sql);

Note that in the sprintf format string, the percentage sign must be doubled (%%) to output a single percentage sign, as the percentage sign has special meaning in sprintf (format specifier).

In-Depth Understanding and Best Practices

Constructing secure SQL queries involves not only correct string concatenation but also consideration of the following aspects:

1. Input Validation: Validate the format and range of data before using it in queries.
2. Principle of Least Privilege: Database users should have only the minimum permissions necessary to perform required operations.
3. Prepared Statements: For modern PHP development, prepared statements with PDO or MySQLi are recommended as they more effectively prevent SQL injection.
4. Error Handling: Avoid displaying detailed database error messages to users to prevent leakage of sensitive information.

Code Examples and Explanation

The following is a complete example demonstrating best practices for securely constructing LIKE queries:

<?php
// Assume database connection is established
$prefix = "123";

// Escape user input
$escaped_prefix = mysql_real_escape_string($prefix);

// Construct SQL query
$sql = "SELECT * FROM table WHERE the_number LIKE '" . $escaped_prefix . "%'";

// Execute query
$result = mysql_query($sql);
if (!$result) {
    die("Query failed: " . mysql_error());
}

// Process results
$count = mysql_num_rows($result);
echo "Number of matching records: " . $count;
?>

By adhering to these principles, developers can construct database queries that are both secure and efficient, ensuring application stability and security.