PDO's Approach to Preventing SQL Injection

If you've transitioned from the mysql library to PDO, you may wonder how to replace the real_escape_string function for escaping single quotes in strings destined for your database. While adding slashes to every string may seem cumbersome, PDO provides a more efficient alternative.

The Power of PDO Prepare

To protect against SQL injection, PDO recommends using its prepare() method. This method optimizes your application's performance by enabling caching of query plans and meta information. Additionally, it eliminates the need for manual parameter quoting, thus preventing SQL injection attacks.

How PDO Prepare Works

When you execute PDO::prepare(), PDO establishes a prepared statement. This statement is then compiled and cached, improving execution efficiency. When you're ready to execute the query with parameters, you call PDOStatement::execute(), which injects the parameters into the prepared statement without the need for manual quoting.

Example Usage

Here's an example of using PDO prepare() and execute():

$pdo = new PDO("...");
$sql = "INSERT INTO users (username, email) VALUES (?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute([$username, $email]);

By using PDO prepare() and execute(), you can securely execute parameterized queries without the need for manual string escaping. This simplifies your code and enhances security by preventing SQL injection attacks.