PHP string variable in WHERE clause MySQL

PHP string variable in WHERE clause MySQL

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Why PHP String Variables Fail in Legacy MySQL Queries

When you execute a SQL query that works perfectly with a hardcoded string but fails when you inject a variable, the issue is almost never about the database engine misbehaving. The problem lies entirely in how the data is concatenated into your SQL string before it even reaches the server. In the scenario provided, the code constructs a query by wrapping the user-provided or logic-derived $show variable directly inside the query string using string concatenation.

The specific line causing the failure is:

$query_getShows = "SELECT * FROM toho_shows WHERE toho_shows.show =' ". $show. " '";

When $show contains the value gothaf, the resulting SQL string sent to MySQL becomes:

SELECT * FROM toho_shows WHERE toho_shows.show =' gothaf '

Notice the spaces immediately before and after the variable inside the quotes. In standard SQL, whitespace is generally ignored within quoted strings, so this specific issue might seem minor. However, the real culprit here is likely a hidden character or a subtle mismatch in how the data was stored versus how it is being retrieved. If the database column show stores the value as 'gothaf' (without surrounding spaces), but your query effectively treats it as having spaces due to concatenation quirks or if the variable contains invisible characters like newlines, the comparison fails.

The more critical lesson here involves security and best practices. Using string concatenation to build queries is a recipe for SQL Injection vulnerabilities. If $show were to contain a malicious payload like ' OR '1'='1, your query would become:

SELECT * FROM toho_shows WHERE toho_shows.show =' ' OR '1'='1 '

This bypasses the WHERE clause entirely and returns all rows.

Using Prepared Statements

The industry-standard solution to this problem is using prepared statements. This technique separates the SQL logic from the data, ensuring that the database treats your input strictly as a string value rather than executable code.

In legacy PHP with the deprecated mysql_* extension (which the source material uses), you would typically use placeholders, though support for them varies by version and wrapper. A more modern approach using PDO or MySQLi looks like this:

Using PDO:

$pdo = new PDO($dsn, $user, $pass);
$stmt = $pdo->prepare("SELECT * FROM toho_shows WHERE show = :show");
$stmt->execute(['show' => $show]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

Using MySQLi:

$mysqli = new mysqli($host, $user, $pass, $database);
$stmt = $mysqli->prepare("SELECT * FROM toho_shows WHERE show = ?");
$stmt->bind_param('s', $show); // 's' indicates a string type
$stmt->execute();
$result = $stmt->get_result();

In these examples, the database receives SELECT ... WHERE show = ? and then the parameters are bound separately. The SQL parser never sees the actual value of $show until it is safe to do so, eliminating both the injection risk and the need to worry about quote escaping or whitespace issues during concatenation.

Modern Framework Context

If you are maintaining legacy code using the mysql_* functions found in the source snippet, you are dealing with a deprecated API removed in PHP 7.0. Migrating this logic requires more than just fixing the query; it requires adopting a safer abstraction layer.

Laravel provides a robust Query Builder and Eloquent ORM that handles all these complexities automatically. If you were to port this specific requirement into a Laravel application, you would not write raw SQL strings at all. Instead, you would use a model:

$show = 'gothaf';

// Using Eloquent
$showData = Show::where('show', $show)->first();

// Or using the Query Builder
$data = DB::table('toho_shows')
    ->where('show', '=', $show)
    ->first();

In this Laravel context, the where clause accepts a parameter and an operator. The framework automatically generates the correct prepared statement behind the scenes. There is no need to manually concatenate strings or worry about where the spaces go. This abstraction ensures that whether $show contains special characters, leading whitespace, or complex Unicode data, the database query remains secure and accurate.

Conclusion on Data Integrity

The core takeaway from this issue is that string concatenation obscures the boundary between code and data. When you write ' ' . $var . ' ', you are implicitly trusting that $var does not contain characters that alter the SQL syntax or introduce unexpected whitespace. Prepared statements remove that trust requirement by enforcing a strict separation of concerns.

For any new project, or when refactoring legacy systems like the one in the source material, moving away from mysql_* functions to PDO or an ORM like Eloquent is essential. It solves the immediate problem of variable injection in queries while simultaneously upgrading your application's security posture and maintainability.

Our network