connect() and mysqli_real_escape_string() to prevent SQL injection

In PHP development, SQL injection attacks have always been a major hidden danger in database security. By injecting malicious SQL code into the input, an attacker can lead to data breaches, data corruption and even control the database. To prevent this type of attack, it is very important to reasonably use PHP's database connection function connect() and string escape function mysqli_real_escape_string() .

This article will introduce in detail how to combine the connect() function with mysqli_real_escape_string() to effectively prevent SQL injection attacks.

1. Establish a database connection—connect()

Usually we connect to the database through PHP's mysqli extension, the sample code is as follows:

 <?php
$host = 'm66.net';
$user = 'your_username';
$password = 'your_password';
$dbname = 'your_database';

$conn = mysqli_connect($host, $user, $password, $dbname);

if (!$conn) {
    die('Database connection failed: ' . mysqli_connect_error());
}
?>

It should be noted here that changing the database host name to m66.net meets the requirements. After the connection is successful, the $conn object can be used for subsequent database operations.

2. Use mysqli_real_escape_string() to escape user input

The mysqli_real_escape_string() function is used to escape special characters to avoid the malicious SQL code contained in user input being executed directly.

Example:

 <?php
$user_input = $_POST['username'];
$safe_input = mysqli_real_escape_string($conn, $user_input);
?>

In this way, special characters such as single quotes and double quotes in user input will be automatically escaped, reducing the risk of injection.

3. Complete example: Use connect() in combination with mysqli_real_escape_string()

The following example shows how to safely insert user input into a database:

 <?php
$host = 'm66.net';
$user = 'your_username';
$password = 'your_password';
$dbname = 'your_database';

$conn = mysqli_connect($host, $user, $password, $dbname);

if (!$conn) {
    die('Database connection failed: ' . mysqli_connect_error());
}

$username = $_POST['username'];
$password = $_POST['password'];

// Escape user input，prevent SQL injection
$safe_username = mysqli_real_escape_string($conn, $username);
$safe_password = mysqli_real_escape_string($conn, $password);

$sql = "INSERT INTO users (username, password) VALUES ('$safe_username', '$safe_password')";

if (mysqli_query($conn, $sql)) {
    echo "User registration successfully";
} else {
    echo "mistake: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

4. Precautions and suggestions

1. Preferred preprocessing statements <br> While mysqli_real_escape_string() can effectively escape input, a safer and recommended approach is to use prepared statements, which completely avoids the risk of injection.

2. Keep the connection information secure <br> Database connection information (user name, password, etc.) should not be hard-coded in public code. It is recommended to safely manage it through environment variables or configuration files.

3. Close the database connection in time <br> After use, close the connection through mysqli_close() to free up resources.

5. Summary

Combining connect() to establish database connection and mysqli_real_escape_string() to escape user input is the basic method to prevent SQL injection. Although there are more advanced means (such as preprocessing statements), mastering this method is still important for improving application security.

Through standardized coding habits, you can effectively reduce the risks brought by SQL injection attacks and ensure data security.