How to Safely Include PHP Variables in MySQL INSERT Statements?

Including PHP Variables in MySQL Statements

In this scenario, you're encountering issues while inserting values into the "contents" table, specifically when using the PHP variable "$type" in the "VALUES" section of the MySQL statement. Let's delve into the appropriate approach.

1. Employ Prepared Statements (Recommended)

This method addresses 99% of queries, including yours. Any variable representing an SQL data literal (string or number) must be incorporated through prepared statements, without exception. Static values, however, can be inserted as is.

The preparation process entails four stages:

• Designate placeholders for variables in the SQL statement.
• Prepare the modified query.
• Bind variables to respective placeholders.
• Execute the query.

Here's how it works in different database drivers:

mysqli with PHP 8.2 :

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

mysqli with Earlier PHP Versions:

$stmt = $mysqli->prepare("INSERT INTO contents (type, reporter, description) VALUES ('whatever', ?, ?)");
$stmt->bind_param("ss", $reporter, $description);
$stmt->execute();

PDO:

$sql = "INSERT INTO contents (type, reporter, description) VALUES ('whatever', ?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute([$reporter, $description]);

2. Implement Whitelist Filtering for Query Parts

If you need to include variables representing specific parts of the SQL query, like keywords, table or field names, or operators, use a "whitelist" to ensure their validity.

For example, if a variable represents an order by field:

$allowed = ["name", "price", "qty"];
$key = array_search($orderby, $allowed, true);
if ($key === false) { throw new InvalidArgumentException("Invalid field name"); }

Similarly, check for valid ordering directions:

$allowed = ["ASC", "DESC"];
$key = array_search($direction, $allowed, true);
if ($key === false) { throw new InvalidArgumentException("Invalid ORDER BY direction"); }

Once validated, prepare the query String, and remember to properly escape identifiers according to MySQL syntax:

$query = "SELECT * FROM `table` ORDER BY `$orderby` $direction";

The above is the detailed content of How to Safely Include PHP Variables in MySQL INSERT Statements?. For more information, please follow other related articles on the PHP Chinese website!