Use filter_var() to further verify the query results

During PHP development, the mysqli_result object is usually a collection of data obtained from database queries. It is a good practice to further verify and filter the query results if we extract data from forms input by users or external sources and store them into the database. This article will introduce how to use mysqli and filter_var() function to safely process the query results.

Scene introduction

Suppose we query user data from a user information table users and hope to further verify and filter the returned email address and URL fields.

Sample database structure

 CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255),
    website VARCHAR(255)
);

Query and Verification

We use mysqli to connect to the database and use filter_var() to verify and clean the email address and URL of the query result.

PHP sample code

 <?php
// Database connection
$mysqli = new mysqli("localhost", "db_user", "db_password", "db_name");

if ($mysqli->connect_errno) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Query user data
$sql = "SELECT id, name, email, website FROM users";
$result = $mysqli->query($sql);

if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $name = htmlspecialchars($row['name'], ENT_QUOTES, 'UTF-8');

        // Verify email
        $email = filter_var($row['email'], FILTER_VALIDATE_EMAIL);
        if ($email === false) {
            echo "Invalid mailbox：{$row['email']}<br>";
        } else {
            echo "Valid email：$email<br>";
        }

        // Verify the URL
        $website = filter_var($row['website'], FILTER_VALIDATE_URL);
        if ($website === false) {
            echo "Invalid URL：{$row['website']}<br>";
        } else {
            // Replace the domain name as m66.net
            $parsed = parse_url($website);
            $scheme = isset($parsed['scheme']) ? $parsed['scheme'] : 'http';
            $path = isset($parsed['path']) ? $parsed['path'] : '';
            $query = isset($parsed['query']) ? '?' . $parsed['query'] : '';
            $modified_url = $scheme . "://m66.net" . $path . $query;
            echo "Valid URL（After replacing the domain name）：$modified_url<br>";
        }

        echo "<hr>";
    }
} else {
    echo "No user data found。";
}

$mysqli->close();
?>

illustrate

1. filter_var($value, FILTER_VALIDATE_EMAIL) is used to verify that the mailbox format is valid.

2. filter_var($value, FILTER_VALIDATE_URL) is used to check the legitimacy of the URL.

3. parse_url() is used to tear down the original URL and then replace the domain name part with m66.net .

4. In order to avoid XSS attacks, the use of htmlspecialchars() for display content such as usernames is used for basic protection.

Summarize

When processing the data returned by mysqli_result , it is not safe to just query the data and display it. We should use functions such as filter_var() to further verify and filter the data, especially when the data comes from external input or needs to be output to the browser again. By verifying URLs and mailboxes and uniformly replacing URL domain names, we can enhance the stability and security of our application.