Proper Usage of Variables in SQL Queries with PHP and Security Best Practices

Problem Background and Analysis

In PHP and MySQL integration development, beginners often encounter issues where variables fail to properly evaluate within SQL queries. As shown in the original code:

$sql = 'SELECT * 
    FROM $usertable 
    WHERE PartNumber = $partid';

This code exhibits two fundamental problems: first, PHP variables are not parsed within single-quoted strings, causing $usertable and $partid to be treated as literal text; second, directly concatenating user input into SQL statements creates serious security vulnerabilities.

Solution: Prepared Statements

Prepared statements represent the optimal solution to these issues. Through parameterized queries, variable values are safely passed to the SQL engine:

$sql = 'SELECT * 
    FROM usertable 
    WHERE PartNumber = ?';

$stmt = $con->prepare($sql);
$stmt->bind_param("s", $partid);
$stmt->execute();
$result = $stmt->get_result();
$row = mysqli_fetch_assoc($result);

This approach offers multiple advantages: the query structure remains fixed with only value portions using placeholders; the bind_param method handles automatic data type conversion and special character escaping; and it effectively prevents SQL injection attacks.

Security Considerations and Performance Optimization

Directly concatenating user input into SQL statements creates SQL injection vulnerabilities. Attackers can construct special inputs to execute malicious SQL commands. Prepared statements fundamentally resolve this issue by separating parameter values from query structure.

From a performance perspective, prepared statements allow databases to cache query plans, improving execution efficiency for repeated queries. The referenced article's discussion of MySQL optimizer improvements for variable handling further supports this—in MySQL 5.7 and later versions, the optimizer includes specific enhancements for queries containing variables.

Complete Implementation Example

Below is a complete secure query implementation:

<?php
// Database connection
$con = mysqli_connect($hostname, $username, $password);
if (!$con) {
    die("Connection failed: " . mysqli_connect_error());
}
mysqli_select_db($con, $dbname);

// Security check and query execution
if (isset($_GET['part'])) {
    $partid = $_GET['part'];
    
    // Use prepared statements
    $sql = 'SELECT * FROM usertable WHERE PartNumber = ?';
    $stmt = $con->prepare($sql);
    
    if ($stmt) {
        $stmt->bind_param("s", $partid);
        $stmt->execute();
        $result = $stmt->get_result();
        
        if ($row = mysqli_fetch_assoc($result)) {
            $partnumber = $partid;
            $nsn = $row["NSN"];
            $description = $row["Description"];
            $quantity = $row["Quantity"];
            $condition = $row["Conditio"];
            
            // Process query results
            echo "Part found: " . htmlspecialchars($description);
        } else {
            echo "No matching part found";
        }
        
        $stmt->close();
    } else {
        echo "Query preparation failed";
    }
}

mysqli_close($con);
?>

Best Practices Summary

In PHP database development, always adhere to these principles: use prepared statements for all user inputs; avoid direct variable interpolation in SQL; employ hardcoding or whitelist validation for table and column names; promptly close database connections and statement objects. These practices not only ensure application security but also enhance code maintainability and performance.