php mysqli insert variables query

php mysqli insert variables query

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

Stefan Bogdanescu

Founder & Senior Architect

PHP MySQLi Insert Variables Query

When constructing an INSERT statement in PHP, it is easy to fall into the trap of assuming that string concatenation behaves identically regardless of whether the value being inserted comes from a static string or a variable. The code snippet provided demonstrates a scenario where a developer defines $username = "test" and attempts to inject it directly into the SQL string using VALUES ('".$username."', .... While this specific example works because the variable contains only safe characters, relying on this pattern creates significant vulnerabilities when dealing with user input.

The fundamental issue here is not that variables are inherently broken within MySQLi queries; rather, it is how those variables are introduced into the query string. When you manually concatenate a variable into a query like "INSERT INTO ... VALUES ('".$username."', ...) , you are executing concatenated SQL. This means the final string sent to the database looks something like this:

INSERT INTO users (username, password, email...) VALUES ('test', 'test', ...)

If $username were set by a user to admin' OR '1'='1, the resulting query would become:

INSERT INTO users ... VALUES ('admin' OR '1'='1', ...)

This alters the SQL logic entirely, potentially causing data corruption or security breaches. The fact that your specific code works with "test" is purely coincidental to the safety of the approach. The real problem arises when you switch from hardcoded values to dynamic ones without a strategy for sanitization.

The Proper Approach: Prepared Statements

The industry-standard solution for inserting variables into MySQL queries is using prepared statements. This technique separates the SQL logic from the data, ensuring that any value passed to the query is treated strictly as data, never as executable code.

Here is how you should refactor the original example:

$connection = new mysqli('localhost', 'user', 'pass', 'db');

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

// Prepare the statement with placeholders
$sql = "INSERT INTO users (username, password, email, firstName, lastName, createDate) VALUES (?, ?, ?, ?, ?, ?)";

if ($stmt = $connection->prepare($sql)) {
    // Bind parameters. 's' stands for string, 'i' for integer, etc.
    $stmt->bind_param("ssssss", 
        $username, 
        "test", 
        "test", 
        "test", 
        "test", 
        "test"
    );

    // Execute the prepared statement
    if ($stmt->execute()) {
        echo "success";
    } else {
        echo "error: " . $stmt->error;
    }

    $stmt->close();
}

$connection->close();

In this implementation, the ? placeholders act as slots where data will be filled in after the SQL structure is parsed by the MySQLi extension. The bind_param method tells PHP exactly what type of data goes into each slot and ensures the data is escaped automatically before being sent to the database server.

Why Concatenation Fails with Variables

It is important to understand why the original code "worked" but should be avoided. In the original snippet, the variable $username was defined as a static string "test". Since this string contains no special SQL characters (like quotes or semicolons), the concatenation did not break the query syntax. However, this approach is brittle.

If you were to change $username to be populated from $_GET['user'], the script would immediately become vulnerable to SQL injection attacks. Prepared statements eliminate this risk by design. The database driver interprets the placeholder and the bound value as separate entities in memory before constructing the final packet sent over the network wire.

Trade-offs and Limitations

While prepared statements are safer, they do introduce a slight performance overhead if used excessively without caching the statement object. If you are running thousands of identical INSERT operations with different values but the same column structure, executing the prepare() call repeatedly can become a bottleneck. In high-throughput scenarios, it is best practice to prepare the statement once and reuse the $stmt object for multiple executions, only calling $stmt->bind_param and $stmt->execute in a loop.

Additionally, if you are working within the Laravel ecosystem, the underlying connection handling often abstracts these details away. If your application relies on Eloquent ORM or raw queries managed by the framework, ensure that you are utilizing the query builder's parameter binding methods rather than manual mysqli_* functions to maintain consistency and security standards across your stack. For more details on secure database practices in modern PHP applications, resources like https://laravelcompany.com often highlight best practices for integrating these concepts into larger frameworks.

Ultimately, the decision to move from concatenation to prepared statements is not about fixing a bug in your current code; it is about future-proofing your application against security threats that will appear as soon as you introduce any dynamic user data.

Our network