PHP向MySQL传特殊字符时mysqli_real_escape_string报错求助

Hey Matt, let's break down and fix the two main issues you're facing: the mysqli_real_escape_string() parameter error, and the broader problem of safely handling special characters in database submissions.

First: Fixing the mysqli_real_escape_string() Warning #

The error you're seeing (Warning: mysqli_real_escape_string() expects exactly 2 parameters, 1 given) happens because this function requires two arguments:

1. Your active database connection object ($conn in your code)
2. The string you want to escape

You forgot to pass the $conn parameter. Here's how to correct that line (and all other fields you need to escape):

$name = mysqli_real_escape_string($conn, strtolower($_POST['name']));
$header = mysqli_real_escape_string($conn, strtolower($_POST['header']));
$address = mysqli_real_escape_string($conn, strtolower($_POST['address']));
// Repeat this pattern for all other POST fields in your code

Better Solution: Use Prepared Statements (Avoid SQL Injection Risks) #

While fixing the escape function will resolve the immediate error, manually escaping strings is error-prone and not the most secure way to handle database inputs. Prepared statements are the industry standard for preventing SQL injection and handling special characters safely—they eliminate the need for manual escaping entirely.

Here's your fully revised code using prepared statements:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "dbname";

// Create and validate connection
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    // Convert all POST data to lowercase first
    $name = strtolower($_POST['name']);
    $header = strtolower($_POST['header']);
    $address = strtolower($_POST['address']);
    $city = strtolower($_POST['city']);
    $county = strtolower($_POST['county']);
    $post = strtolower($_POST['post']);
    $tele = strtolower($_POST['tele']);
    $mob = strtolower($_POST['mob']);
    $email = strtolower($_POST['email']);
    $web = strtolower($_POST['web']);

    // Check for existing business name with a prepared statement
    $checkStmt = $conn->prepare("SELECT * FROM business_dir WHERE `name` = ?");
    $checkStmt->bind_param("s", $name); // "s" denotes a string parameter
    $checkStmt->execute();
    $result = $checkStmt->get_result();
    $matchFound = $result->num_rows > 0;

    if (!$matchFound) {
        // Prepare INSERT statement with placeholders
        $insertStmt = $conn->prepare("INSERT INTO business_dir (`name`, `header`, `address`, `city`, `county`, `post`, `tele`, `mob`, `email`, `web`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        
        // Bind all 10 string parameters (denoted by "ssssssssss")
        $insertStmt->bind_param("ssssssssss", $name, $header, $address, $city, $county, $post, $tele, $mob, $email, $web);
        
        // Execute and handle result
        if ($insertStmt->execute()) {
            echo '<div class="alert alert-success text-center" style="margin:20px;" role="alert">Business Successfully Added!</div>';
        } else {
            echo '<div class="alert alert-danger text-center" style="margin:20px;" role="alert">Error: ' . $insertStmt->error . '</div>';
        }
        
        $insertStmt->close();
    } else {
        echo '<div class="alert alert-danger text-center" style="margin:20px;" role="alert">Business Failed To Be Added, An Entry With The Same Name Already Exists!</div>';
    }

    // Clean up resources
    $checkStmt->close();
    $conn->close();
}
?>

Key Improvements in This Version: #

• No more escape function errors: Prepared statements handle special characters automatically
• Max security: Eliminates all SQL injection risks
• Robust error handling: Added connection validation to catch early issues
• Cleaner logic: Separates SQL structure from user input, making maintenance easier

内容的提问来源于stack exchange，提问作者Matt Hutch