Please wait

PDO Prepared Statements

A query refers to a SQL statement that's executed to interact with the database. This can be any SQL command, including SELECT, INSERT, UPDATE, DELETE, and others.

With PDO, there are two primary ways to perform queries:

  • Using the query() method of a PDO instance.
  • Using prepared statements for dynamic SQL statements where data may vary, especially if the data is sourced from user inputs or external sources.

The word "query" often implies retrieval of data, but in the database context (and with PDO), it can refer to any SQL command sent to the database server.

Of course, before performing queries, you must have an instance of the PDO class, which can look like this:

$pdo = new PDO(
  "mysql:host=localhost;dbname=example;charset=utf8mb4;port=3306",
  'username',
  'password'
);

Performing Queries

The query() method is a part of the PDO API that allows for the execution of SQL statements directly. This method is designed for straightforward SQL commands without parameters.

It's very useful when the SQL statement is fixed and does not change based on external input, especially when doing simple SELECT operations to retrieve data where there's no user-supplied data involved.

Here's a simple example.

$result = $pdo->query('SELECT * FROM users');

The query() method accepts the query to perform as a string.

Problems with the query() method

When using the query() method with dynamic data, several significant problems can arise:

  • SQL Injection: The most notable risk is SQL injection. This occurs when an attacker can insert or "inject" malicious SQL code into the query. Since query() directly executes the provided SQL, it doesn't offer the inherent protection that comes with parameterized queries in prepared statements.
  • Data Integrity: Without proper sanitization or escaping, dynamic data can break the SQL query, causing syntax errors or unintended behavior. This can lead to data corruption or loss.
  • Performance: While not always noticeable on a small scale, repeatedly constructing and executing non-parameterized SQL can be slower than using prepared statements, especially when performing the same query structure multiple times with different data.

Let's assume you have a simple search functionality on your website where users can search for a username:

$searchTerm = $_GET['username']; // getting user input from a search box
$result = $pdo->query("SELECT * FROM users WHERE username = '$searchTerm'");

Now, if an attacker inputs a value like johnny'; DROP TABLE users; --, the SQL executed becomes:

SELECT * FROM users WHERE username = 'johnny'; DROP TABLE users; --'

This would not only fetch the data for 'johnny' but also delete the entire users table. The -- at the end is an SQL comment, effectively neutralizing the rest of the original SQL statement.

This is a classic example of SQL injection. Using the query() method with unsanitized, dynamic data makes your application vulnerable to such attacks. We can resolve these issues with resolved statements.

Prepared Statements

**Prepared statements in PDO are a feature that allows SQL queries to be "prepared" with placeholders for data values, separating the SQL logic from the actual data. **This ensures that user inputs or external data are treated strictly as data values and not as executable SQL, which provides robust protection against SQL injection and some other related issues.

The benefits of prepared statements are the following:

  • Protection Against SQL Injection: Since data values are bound to placeholders and are not directly interpolated into the SQL string, they can't alter the query's structure. This neutralizes the risk of SQL injection.
  • Consistent Performance: If you're executing the same query multiple times with different data, using prepared statements can be more efficient because the database can optimize for the query structure after the first preparation.
  • Data Integrity: Prepared statements automatically handle data types and escaping, reducing the risk of SQL errors due to incorrect data formatting.

Given the same example where a user searches for a username, it is vulnerable to SQL injection, as previously demonstrated.

Here's how we would use a prepared statement:

$searchTerm = $_GET['username'];
 
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");
$stmt->execute([
  $searchTerm
]);
  1. The SQL statement is prepared using the prepare() method with a placeholder instead of directly embedding the $searchTerm. Placeholders are written with the ? character.
  2. The actual value of $searchTerm is then bound to the ? placeholder during the execute() method. This method accepts an array of values to replace placeholders. This ensures that $searchTerm is always treated as a data value and can't modify the SQL logic.

By using the prepared statement approach, even if the $searchTerm contains potentially malicious SQL snippets, they won't be executed as SQL but will simply be treated as plain data values, thereby preventing SQL injection and preserving data integrity.

Named Placeholders

Placeholders are written with the ? character, but they're not really descriptive. In these instances, we can use named placeholders.

Named placeholders are essentially symbolic placeholders in the SQL query that will be replaced with actual data values during execution. They are prefixed with a colon (:) and provide a more descriptive way to bind parameters compared to using ? as a placeholder. Named placeholders make SQL queries more readable and self-explanatory, especially when dealing with multiple parameters.

Given the same example of searching for a username, here's how you'd use named placeholders:

$searchTerm = $_GET['username'];
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username");
 
$stmt->execute([':username' => $searchTerm]);

Here, :username is the named placeholder that will be replaced by the actual value of $searchTerm upon execution.

The SQL statement is still prepared using a named placeholder (:username). The execute() method is called with an associative array. The key :username matches the named placeholder in the SQL, and its value ($searchTerm) is the actual data you want to bind.

Binding Values

In the past few examples, we've been passing in values during the execute() method. However, it's completely possible to do so before executing the query with the bindValue() method.

The bindValue() method is used to bind a value to a parameter, either a named placeholder or a positional placeholder (?).

Let's take the search functionality where users can search for a username:

$searchTerm = $_GET['username'];
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username");
 
// Using bindValue to bind the value of $searchTerm to the named placeholder :username
$stmt->bindValue(':username', $searchTerm);
 
$stmt->execute();

In this approach:

  1. The SQL statement is prepared with a named placeholder (:username).
  2. The bindValue() method is used to bind the value of $searchTerm to the placeholder.
  3. The statement is executed.

Binding Parameters

There's another method for binding parameters called bindParam(). The bindParam() method is used to bind a parameter by reference. This means that it binds a variable, not just its current value, to a placeholder in a SQL statement. As a result, if the variable's value changes after it's been bound but before the statement is executed, the SQL statement will use the updated value.

Using bindParam() in our example:

$searchTerm = $_GET['username'];
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username");
 
// Using bindParam to bind the $searchTerm variable to the named placeholder :username
$stmt->bindParam(':username', $searchTerm);
 
$stmt->execute();

Of course, you may be wondering how this is different from the bindValue() method. Let's dissect the differences.

  • Binding Reference vs. Value:
    • bindParam(): Binds the variable by reference. Any change to the variable's value after binding will be reflected when the statement is executed.
    • bindValue(): Binds the current value of the variable. Any subsequent change to the variable's value will not affect the prepared statement.
  • Use Cases:
    • bindParam(): Useful in scenarios like loops where the same statement is executed multiple times with different values.
    • bindValue(): Useful when you only need to bind a value once and ensure it doesn't change accidentally before execution.

Take the following example with the bindValue() method:

$stmt = $pdo->prepare("INSERT INTO numbers (value) VALUES (:value)");
 
$value = 1;
$stmt->bindValue(':value', $value);
 
$value = 2;
$stmt->execute();  // This will insert 1, not 2, because the value was bound, not the variable itself.

In the above example, the value 1 will be inserted into a table even though the $value variable was updated to 2 before the query was executed.

Let's look at the same example with the bindParam() method:

 
$stmt = $pdo->prepare("INSERT INTO numbers (value) VALUES (:value)");
 
$value = 1;
$stmt->bindParam(':value', $value);
 
$value = 2;
$stmt->execute();  // This will insert 2 because the variable (by reference) was bound, so any changes to the variable are reflected.

In the second example, since we're binding the variable by reference with bindParam(), the value that gets inserted is the latest value of the $value variable at the time of the execute() call.

Key Takeaways

  • Prepared statements separate SQL logic from data, ensuring that user input is treated strictly as data and not as executable SQL.
  • One of the primary benefits of using prepared statements is robust protection against SQL injection attacks since data values can't alter the query's structure.
  • Prepared statements can use named placeholders (e.g., :username) or positional placeholders (?). Named placeholders provide a more descriptive way to bind parameters.
  • bindParam(): Binds a variable by reference to a placeholder. If the variable's value changes after binding but before execution, the updated value is used.
  • bindValue(): Binds a specific value to a placeholder. Subsequent changes to the variable's value won't affect the bound value.
  • Parameters can also be bound directly during the execute() call by passing an associative array.
  • Using the query() method with dynamic data is risky due to potential SQL injections, data integrity issues, and performance overheads. Prepared statements mitigate these issues.
Previous Tutorial:
Connecting to a Database
Next Tutorial:
CRUD Actions with PDO

Comments

Please read this before commenting