The mysqli_real_escape_string() function in PHP is used to escape special characters in a string for use in an SQL query, ensuring that the input is safe to be inserted into the query. This function helps prevent SQL injection attacks, which occur when malicious users try to manipulate the SQL queries by injecting harmful SQL code.

Syntax:

mysqli_real_escape_string(connection, string);

• connection: The connection to the MySQL database. This is a valid MySQLi connection resource.
• string: The input string that you want to escape.

The function returns a string with special characters escaped, making it safe to use in an SQL query.

Example:

1. Basic Usage with Insert Query

<?php
// Connect to the database
$conn = mysqli_connect("localhost", "username", "password", "database_name");

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// User input (for example, from a form)
$user_input = "O'Reilly"; // This contains a special character (apostrophe)

// Escape the user input to prevent SQL injection
$safe_input = mysqli_real_escape_string($conn, $user_input);

// Use the escaped string in an SQL query
$query = "INSERT INTO users (username) VALUES ('$safe_input')";

// Execute the query
if (mysqli_query($conn, $query)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $query . "<br>" . mysqli_error($conn);
}

// Close the connection
mysqli_close($conn);
?>

2. Using it with Select Query

<?php
// Connect to the database
$conn = mysqli_connect("localhost", "username", "password", "database_name");

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// User input
$user_input = "John O'Reilly";

// Escape the user input
$safe_input = mysqli_real_escape_string($conn, $user_input);

// Use the escaped string in an SQL query
$query = "SELECT * FROM users WHERE username = '$safe_input'";

// Execute the query
$result = mysqli_query($conn, $query);

if (mysqli_num_rows($result) > 0) {
    // Output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["username"]. "<br>";
    }
} else {
    echo "0 results";
}

// Close the connection
mysqli_close($conn);
?>

Explanation:

• In both examples, mysqli_real_escape_string() ensures that any special characters like ' (apostrophe) are properly escaped so that they don't break the SQL query.
• Without escaping, the string "O'Reilly" would cause an error in the SQL query because the apostrophe would prematurely end the string literal.

Important Points:

1. Always use mysqli_real_escape_string() when inserting user input into SQL queries.
2. It's crucial to use this function after establishing a database connection and not before.
3. Alternatively, you can use prepared statements with bound parameters (which is safer and easier) to avoid SQL injection entirely, but mysqli_real_escape_string() is useful for escaping input manually.

Example with Prepared Statements (Recommended):

<?php
// Connect to the database
$conn = mysqli_connect("localhost", "username", "password", "database_name");

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// User input
$user_input = "O'Reilly";

// Prepared statement to avoid SQL injection
$stmt = $conn->prepare("INSERT INTO users (username) VALUES (?)");
$stmt->bind_param("s", $user_input); // "s" means the input is a string

// Execute the statement
$stmt->execute();

// Close the statement and connection
$stmt->close();
$conn->close();
?>