SELECT * FROM Table Where ID

SELECT * FROM Table Where ID

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

Stefan Bogdanescu

Founder & Senior Architect

The Security Gap in Dynamic SQL Queries

Retrieving data based on a user-supplied ID is a fundamental operation in web development, yet the implementation shown in your snippet exposes your application to a critical vulnerability known as SQL Injection. While your syntax for constructing the query appears logically sound—selecting from a table and filtering by an identifier—the method of injecting that value directly into the SQL string creates a fatal flaw.

In your code, you execute:

$query = "SELECT * FROM $usertable WHERE id = $id LIMIT 1";
$result = mysql_query($query, $con);

Here, $id is treated as raw text that gets concatenated directly into the query string. If a user visits www.exampleurl.com/index.php?id=1, they receive User 1. However, if a malicious actor visits www.exampleurl.com/index.php?id=1 OR '1'='1, the resulting SQL becomes:

SELECT * FROM users WHERE id = 1 OR '1'='1' LIMIT 1

Because '1'='1' is always true, the database ignores the intended ID filter and returns every single row in the table. This allows attackers to bypass authentication checks, dump entire databases, or corrupt data structures.

The second snippet you provided works because it does not use user input in the query at all; it simply fetches everything. However, relying on mysql_* functions is another significant issue. These deprecated extensions were removed from PHP 7.0 due to their inherent lack of safety features and poor performance. Modern PHP development requires using either MySQLi or PDO with prepared statements.

Implementing Prepared Statements

The correct approach involves separating the query structure from the data. By using a prepared statement, you send the SQL logic to the database engine first, ensuring that any values filled in later are strictly treated as data, not executable code.

Here is how you should refactor your index.php file using PDO:

<?php
// Database configuration
$host = 'localhost';
$db   = 'your_database_name';
$user = 'your_username';
$pass = 'your_password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // Throw exceptions on errors
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,       // Fetch as associative arrays
    PDO::ATTR_EMULATE_PREPARES   => false,                  // Disable emulated prepared statements
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    
    // Retrieve the ID from the URL
    $id = $_GET['id'] ?? null; // Use null coalescing operator to handle missing params

    if ($id === null) {
        throw new Exception("No ID provided");
    }

    // Prepare the statement with placeholders
    $stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id LIMIT 1');
    
    // Execute the statement, passing the parameter separately
    $stmt->execute([':id' => $id]);
    
    $user = $stmt->fetch();

    if ($user) {
        echo "Hello, " . htmlspecialchars($user['name']);
    } else {
        echo "User with this ID not found.";
    }
} catch (PDOException $e) {
    // In production, log the error rather than echoing it
    die('Database error occurred.');
}

Why This Works

When you execute $stmt->prepare(), the database server parses and validates the SQL structure immediately. The colon syntax (:id) acts as a placeholder. When you call execute(), the driver sends the value of $id to the server in a special binary format that cannot be interpreted as SQL code, regardless of what characters are inside the variable.

Handling Table Names and Input Validation

Your original query also used the table name as a variable: FROM $usertable. This is dangerous if $usertable comes from user input. While you can parameterize values like IDs or names, you cannot parameterize table names or column names in standard SQL. If an attacker provides users UNION SELECT * FROM passwords-- as the table name, your application might execute unintended queries.

To safely handle dynamic table names, you must maintain a whitelist of allowed tables and hardcode them:

$allowed_tables = ['users', 'products', 'orders'];
$userTable = $_GET['table'] ?? 'users'; // Default to 'users' if not specified

if (!in_array($userTable, $allowed_tables)) {
    die("Invalid table name");
}

// Now it is safe to interpolate the table name because we validated it
$query = "SELECT * FROM $userTable WHERE id = :id LIMIT 1";

Conclusion on Best Practices

Transitioning from mysql_* to PDO or MySQLi is not just a version upgrade; it is a mandatory shift toward secure coding. Prepared statements are the industry standard for preventing SQL injection, and input validation remains essential when dealing with non-parameterized elements like table names. By adopting these practices, you ensure that your dynamic URL parameters function correctly without compromising the integrity of your database.

Our network