PHP Variable in Select Statement

PHP Variable in Select Statement

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

Stefan Bogdanescu

Founder & Senior Architect

Why You Can't Inject Variables Directly into SQL Queries

You are encountering a classic misunderstanding of how relational databases and programming languages interact. Your current script fetches a value from the database successfully, but you want to use that specific name as part of the query itself—essentially trying to make the database treat $retval as an identifier or keyword rather than a string value.

The core issue is that SQL parsers do not understand PHP variables. When you execute SELECT name FROM contacts WHERE ..., the database engine expects literal identifiers (like column names) or values enclosed in quotes. If you attempt to concatenate a variable directly into the query string without proper context, the resulting SQL might look like this:

SELECT name FROM contacts WHERE contact_id='42' and user_id='1'; -- Correct
SELECT 42 AS name FROM contacts ... -- Using the value as a column alias (valid)
SELECT '42' AS name FROM contacts ... -- Using the value as a literal string (valid)

However, if your goal is to select a specific column dynamically based on user input—for example, SELECT username FROM contacts where username comes from a variable—you cannot simply paste that variable into the SQL string and expect it to work as an identifier. While you can use prepared statements for values, using them for identifiers (like column names) requires a different approach because prepared statements treat placeholders strictly as data, not as schema references.

The Problem with Direct Concatenation

In your original snippet, you are fetching a name:

$retval = mysql_fetch_object($query)->name;

If you then try to run a second query where $retval is supposed to act as the column name, simply wrapping it in quotes (e.g., "SELECT '$retval' AS something") will force the database to treat it as a string literal. The database will not recognize JohnDoe or 12345 as a valid column name unless those specific strings exist in your schema.

Furthermore, your code relies on the deprecated and insecure mysql_* functions. These extensions have been removed in PHP 7.0 and lack prepared statement support for identifiers entirely. Using them exposes your application to SQL injection vulnerabilities, especially if you are trying to construct queries based on dynamic inputs.

The Correct Approach: Dynamic Column Selection

If your intention is to select a column whose name is stored in a variable, you must separate the logic of constructing the identifier from the binding of values. Since standard PDO prepared statements do not support placeholders for column names, you have two safe paths forward depending on your specific use case.

1. Whitelisting Identifiers

If the possible column names are known and limited (e.g., only first_name, last_name, or email), the safest pattern is to maintain a whitelist array in PHP and check the variable against it before constructing the query. This prevents arbitrary table or column enumeration attacks.

// Using PDO instead of deprecated mysql_*
$allowedColumns = ['name', 'username', 'display_name'];
$userInputColumn = trim($_GET['col'] ?? '');

if (in_array($userInputColumn, $allowedColumns)) {
    $stmt = $pdo->prepare("SELECT :column FROM contacts WHERE contact_id = :id AND user_id = :user");
    
    // Note: In PDO, you cannot bind an identifier via placeholder. 
    // You must substitute the safe, validated string directly into the query string.
    // However, doing so inside a prepared statement context is tricky.
    // Often, it is safer to build the query string manually after validation.
}

Correction: Actually, mixing dynamic identifiers with prepared statements requires building the SQL string manually but ensuring the identifier part has passed strict validation (like the whitelist above). The values ($id, $user) should still be bound via placeholders to protect against injection in those specific fields.

2. Using EXECUTE with PDO (Advanced)

PDO provides a method to execute a query where parts of it are dynamic, but this is typically used for stored procedures or complex scenarios. For standard SQL, manual string construction after rigorous validation remains the standard practice for identifiers.

Security Implications

The source code in your example uses mysql_real_escape_string. While this function escapes special characters like quotes and backslashes, it does not validate whether the resulting string is a valid database identifier. If an attacker inputs ' OR '1'='1 into a field expecting a column name, simple escaping often fails to prevent logic errors or unauthorized access if the application blindly trusts the input as a schema reference.

The fundamental rule remains: never trust external input as a SQL identifier. Always validate it against a strict list of allowed values. If the variable represents a row value (which seems to be your case based on SELECT name), ensure that value is treated as a string, not an identifier, unless you have explicitly verified that the database contains a column with that exact name.

For modern PHP applications, migrating from the mysql_* functions to PDO or MySQLi is essential. These extensions provide better error handling and, when combined with strict input validation for identifiers, offer a much stronger defense against injection attacks. If you are maintaining legacy code, at minimum, implement a whitelist check before ever concatenating that variable into your SQL statement.

Our network