PHP MySQLi mysqli_real_escape_string()

mysqli_real_escape_string()

The mysqli_real_escape_string() function is a crucial tool for building secure queries. Its task is to escape special characters in a string before inserting them into an SQL statement.

EXAMPLE

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "mysqli_database";

$connection = mysqli_connect($server, $username, $password, $database);

if (mysqli_connect_errno()) {
    echo "Connection failed: " . mysqli_connect_error();
    exit();
}

// User input containing a dangerous single quote (')
$input_fruit = "O'Brian Figs";
$input_vegetable = "Zucchini";

// Escape the strings before inserting into the database
// NOTE: This function examines the string and adds a protective backslash (\) to dangerous characters. 
// Since the function requires information about the currently set character set (charset) on the database, 
// we must pass the $connection object as the first parameter.
$fruit = mysqli_real_escape_string($connection, $input_fruit);
$vegetable = mysqli_real_escape_string($connection, $input_vegetable);

$sql = "INSERT INTO mysqli_table (fruit, vegetable) VALUES ('$fruit', '$vegetable')";

if (mysqli_query($connection, $sql)) {
    // affected_rows returns 1 (one successfully inserted row)
    printf("Successfully inserted rows: %d<br>", mysqli_affected_rows($connection));
    // Since the table already has 4 rows, the next automatic ID will be 5
    printf("Last record has ID: %d", mysqli_insert_id($connection));
}

mysqli_close($connection);
?>

RESULT

The result will be displayed here...

This prevents SQL injection attacks. If a user enters a quotation mark into a form, for example, the function converts it to \', ensuring that the database treats it as regular text and not as part of a command.

Mandatory use for all text data entered by users.

Requires an active database connection to take the current character set (charset) into account.

It does not protect against attacks on numeric fields (use intval() there).