How to Escape User Input When Migrating from mysql_* to PDO?

PDO Equivalent of mysql_real_escape_string

Question:

When migrating from mysql_* to PDO, what is the equivalent of the mysql_real_escape_string function?

Answer:

Unlike mysql_real_escape_string, PDO performs automatic escaping through the use of prepared statements. Prepared statements employ placeholders (e.g., ?) instead of directly inserting user input into SQL queries, preventing SQL injection vulnerabilities.

Example:

<code class="php">try {
    $db = new PDO(...);
} catch (PDOException $e) {
    echo "Error connecting to mysql: " . $e->getMessage();
}

if (isset($_POST['color'])) {
    $stmt = $db->prepare("SELECT id, name, color FROM Cars WHERE color = ?");
    $stmt->execute([$_POST['color']]);

    $cars = $stmt->fetchAll(\PDO::FETCH_ASSOC);
    var_dump($cars);
}</code>

In this example, $_POST['color'] is passed as a parameter in the prepared statement, protecting the query from injection.

Additional Notes:

• Always include charset=utf8 in the PDO connection DSN for security.
• Enable PDO error mode to exceptions (PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION) to handle database errors gracefully.
• Be cautious with outdated MySQL versions (MySQL < 5.3.6), where extra precautions may be necessary.
• Refer to the resources below for more information on PDO and SQL injection prevention.

Further Reading:

• PDO Tutorial for MySQL Developers

The above is the detailed content of How to Escape User Input When Migrating from mysql_* to PDO?. For more information, please follow other related articles on the PHP Chinese website!