How Do Parameterized Queries Prevent SQL Injection?

Parameterized Queries: A Shield Against SQL Injection

Understanding Parameterized Queries

A parameterized query, also known as a prepared statement, is a technique for crafting SQL statements. It separates the dynamic data (parameters) from the fixed parts of the query. This allows for efficient parameter setting during execution.

PHP and MySQL: A Practical Demonstration

Let's illustrate with an SQL query that includes a parameter for a user's email address:

<code class="language-sql">SELECT * FROM users WHERE email = 'foo@example.com'</code>

Implementation with mysqli

<code class="language-php"><?php
$mysqli = new mysqli('localhost', 'username', 'password', 'database');

$stmt = $mysqli->prepare("SELECT * FROM users WHERE email = ?");
$stmt->bind_param('s', 'foo@example.com');
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    // Process the retrieved data...
}

$stmt->close();
?></code>

Implementation with PDO

<code class="language-php"><?php
$pdo = new PDO('mysql:host=localhost;dbname=database', 'username', 'password');

$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
$stmt->bindParam(1, 'foo@example.com', PDO::PARAM_STR);
$stmt->execute();
$result = $stmt->fetchAll();

foreach ($result as $row) {
    // Process the retrieved data...
}
?></code>

Key Advantages of Parameterized Queries:

• Performance Enhancement: Pre-compiling the query and reusing it with different parameters significantly improves efficiency compared to compiling a new query for each parameter set.
• Robust Security: The core benefit is its protection against SQL injection vulnerabilities. By separating variables from the query, it prevents malicious users from injecting harmful code.

The above is the detailed content of How Do Parameterized Queries Prevent SQL Injection?. For more information, please follow other related articles on the PHP Chinese website!