Why Do PHP Variables Cause Errors When Used in MySQL's VALUES Clause?

How to Include a PHP Variable inside a MySQL Statement

Problem:

In PHP, using a variable inside a MySQL statement enclosed within VALUES is causing issues.

Question:

Why is including a variable within VALUES resulting in errors?

Answer:

There are two distinct approaches to include PHP variables in MySQL statements properly:

1. Use Prepared Statements:

   For variables representing SQL data literals (strings, numbers), prepared statements must be used. This involves:

   • Defining placeholders in the SQL statement
   • Preparing the query
   • Binding variables to placeholders
   • Executing the query

   Example (mysqli):

   $type = 'testing';
   $sql = "INSERT INTO contents (type, reporter, description) VALUES ('whatever', ?, ?)";
   $mysqli->execute_query($sql, [$type, $reporter, $description]);

2. Use Whitelist Filtering:

   For any other variable representing a query part (e.g., keyword, identifier), they must be filtered through a white list, ensuring only allowed values are used. Example (checking orderby):

   $orderby = $_GET['orderby'] ?: 'name';
   $allowed = ['name', 'price', 'qty'];
   $key = array_search($orderby, $allowed);
   if ($key === false) throw new InvalidArgumentException("Invalid field name");

Conclusion:

Adhering to these principles protects against SQL injection and ensures the correct execution of queries when PHP variables are involved. Prepared statements should be used for data literals, while whitelisting ensures only allowed values are inserted in other parts of the query.

The above is the detailed content of Why Do PHP Variables Cause Errors When Used in MySQL's VALUES Clause?. For more information, please follow other related articles on the PHP Chinese website!