What potential pitfalls should be considered when using mysqli_real_escape_string in PHP?

When using mysqli_real_escape_string in PHP, it's important to remember that it only escapes special characters in a string for use in an SQL statement. It does not protect against all types of SQL injection attacks, such as second-order SQL injection. Additionally, it can be easy to forget to use mysqli_real_escape_string on all user input, leaving some vulnerable to SQL injection. It's also important to properly sanitize and validate user input before using mysqli_real_escape_string to further protect against SQL injection attacks.

// Example of using mysqli_real_escape_string with proper validation and sanitization
$input = $_POST['user_input'];

// Validate and sanitize user input
if (filter_var($input, FILTER_VALIDATE_INT)) {
    $safe_input = mysqli_real_escape_string($connection, $input);
    
    // Use $safe_input in your SQL query
    $query = "SELECT * FROM users WHERE id = '$safe_input'";
    $result = mysqli_query($connection, $query);
} else {
    // Handle invalid input
    echo "Invalid input";
}