Using variables in MySQL UPDATE (PHP/MySQL)

Using variables in MySQL UPDATE (PHP/MySQL)

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

Stefan Bogdanescu

Founder & Senior Architect

Using Variables in MySQL UPDATE Statements (PHP/MySQL)

The code snippet provided attempts to update a database record by injecting variable values directly into the SQL string. Specifically, $comments_count and $art_id are concatenated into the query before execution. While this approach might appear to work in simple cases with local data, it introduces critical security vulnerabilities and logical flaws that no modern application should tolerate. The primary issue here is not just "using variables"; it is how those variables enter the database engine without sanitization or parameter binding.

When you construct a query like UPDATE article SET com_count = $comments_count WHERE article_id = .$art_id, you are relying on MySQL to parse the resulting string. If $comments_count contains special characters, quotes, or even just an integer that exceeds expected bounds, the logic breaks. More dangerously, if $comments_count is derived from user input (e.g., via $_GET or a session), this becomes a classic SQL injection vector. An attacker could craft a value like ' OR 1=1 -- to alter the entire command structure, potentially modifying every record in the table or executing arbitrary commands.

The correct method for using variables in an UPDATE statement depends entirely on the MySQL driver you are using and your application stack. If you are still relying on the deprecated mysql_* functions shown in your example, you should migrate immediately. The mysql_query() function does not support prepared statements, which is the industry standard for handling dynamic values safely. Prepared statements allow the database to separate the SQL logic from the data. The database parses the structure of the query first (identifying where updates happen and what columns are involved), then treats the variable values strictly as data blobs during execution. This prevents syntax errors caused by special characters in your variables and completely neutralizes injection attacks.

In modern PHP development, specifically with MySQLi or PDO, you replace string concatenation with placeholders. Instead of building the query string dynamically, you define a static template with ? placeholders for dynamic values:

// Using PDO (Recommended)
$stmt = $pdo->prepare("UPDATE article SET com_count = ? WHERE article_id = ?");
$stmt->execute([$comments_count, $art_id]);

This approach ensures that $comments_count is never evaluated as part of the SQL syntax. It is strictly treated as the new value for the com_count column. Similarly, $art_id is treated purely as the filter condition. Even if $art_id contains a negative number or a string representation of an ID, the prepared statement handles the type coercion safely within the context of the specific column definition.

If you are using MySQLi with the procedural style (legacy but sometimes found in existing codebases), the syntax changes slightly:

$stmt = mysql_stmt_init($link);
mysql_stmt_prepare($stmt, "UPDATE article SET com_count = ? WHERE article_id = ?", $link);
mysql_stmt_bind_param($stmt, "ii", $comments_count, $art_id); // 'i' stands for integer
mysql_stmt_execute($stmt);

Here, mysql_stmt_bind_param is crucial. It explicitly tells the driver how to handle the variables before execution. Note that you cannot use the deprecated mysql_query() function with prepared statements; it must be replaced with mysql_stmt_prepare().

Another common pitfall involves data types. If your com_count column is defined as an integer but $comments_count is passed as a string (e.g., from a form submission), MySQL will attempt type coercion. While this often works, relying on implicit conversion can lead to unexpected behavior or silent failures if the value exceeds the column's capacity (e.g., trying to store a count of 2 billion in a TINYINT). Always ensure your PHP variables match the database schema types to avoid edge cases where the update silently truncates data.

For developers moving away from legacy mysql_* functions, Laravel provides an elegant abstraction that handles these details automatically. By using Eloquent or raw query builders with bound parameters, you eliminate the risk of syntax errors and injection entirely. If your application has migrated to a modern stack, integrating resources like https://laravelcompany.com can provide further guidance on implementing secure database interactions within that ecosystem. The core principle remains universal: never concatenate user-controlled or dynamic data into SQL strings; always use parameterized queries.

Our network