PHP PDO::quote Method
last modified April 19, 2025
The PDO::quote method in PHP is used to escape and quote a string for use in SQL queries. It helps prevent SQL injection when not using prepared statements.
Basic Definition
PDO::quote places quotes around a string and escapes special characters within the input string. It returns a quoted string that is theoretically safe to include in an SQL statement.
Syntax: public PDO::quote(string $string, int $parameter_type = PDO::PARAM_STR): string|false.
The method returns the quoted string or false on failure.
Basic Usage Example
This demonstrates the simplest use of PDO::quote to escape a string.
<?php
declare(strict_types=1);
try {
$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
$string = "O'Reilly";
$quoted = $pdo->quote($string);
echo "Original: $string\n";
echo "Quoted: $quoted";
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
This escapes the apostrophe in "O'Reilly" by adding a backslash. The output will show the properly quoted string that can be safely used in SQL queries.
Using quote in SQL Query
This shows how to use PDO::quote in a direct SQL query execution.
<?php
declare(strict_types=1);
try {
$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
$name = "John O'Connor";
$quotedName = $pdo->quote($name);
$sql = "SELECT * FROM users WHERE name = $quotedName";
$stmt = $pdo->query($sql);
$user = $stmt->fetch();
if ($user) {
echo "User found: {$user['name']}";
}
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
This safely includes user input in an SQL query by quoting it first. Note that prepared statements are generally preferred over this approach for security.
Quote with Different Parameter Types
PDO::quote can handle different parameter types like strings and integers.
<?php
declare(strict_types=1);
try {
$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
// String parameter
$string = "It's a string";
$quotedString = $pdo->quote($string, PDO::PARAM_STR);
// Integer parameter
$number = 42;
$quotedNumber = $pdo->quote($number, PDO::PARAM_INT);
echo "Quoted string: $quotedString\n";
echo "Quoted number: $quotedNumber";
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
This demonstrates quoting different data types. Note that PDO::PARAM_INT still returns a string with quotes, which may not be suitable for all database operations.
Quote for LIKE Clause
Special handling is needed when using PDO::quote with LIKE clauses.
<?php
declare(strict_types=1);
try {
$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
$search = "%test%";
$quotedSearch = $pdo->quote($search);
// Remove outer quotes added by quote()
$quotedSearch = trim($quotedSearch, "'");
$sql = "SELECT * FROM products WHERE name LIKE '%$quotedSearch%'";
$stmt = $pdo->query($sql);
while ($row = $stmt->fetch()) {
echo "Product: {$row['name']}\n";
}
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
This shows how to handle LIKE wildcards with PDO::quote. We remove the outer quotes to properly include the wildcard characters in the SQL pattern.
Quote for Multiple Values
This demonstrates quoting multiple values for use in an IN clause.
<?php
declare(strict_types=1);
try {
$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
$values = ["apple", "banana", "cherry"];
$quotedValues = array_map([$pdo, 'quote'], $values);
$inClause = implode(',', $quotedValues);
$sql = "SELECT * FROM fruits WHERE name IN ($inClause)";
$stmt = $pdo->query($sql);
while ($row = $stmt->fetch()) {
echo "Fruit: {$row['name']}\n";
}
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
This creates a safe IN clause by quoting each array element. The array_map function applies quote to each value, and implode combines them with commas.
Quote vs Prepared Statements
This compares using PDO::quote with prepared statements for security.
<?php
declare(strict_types=1);
try {
$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
$input = "O'Reilly";
// Using quote
$quoted = $pdo->quote($input);
$sql1 = "SELECT * FROM books WHERE author = $quoted";
// Using prepared statement
$sql2 = "SELECT * FROM books WHERE author = ?";
$stmt = $pdo->prepare($sql2);
$stmt->execute([$input]);
echo "Query with quote: $sql1\n";
echo "Prepared statement is generally preferred for security";
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
While both methods protect against SQL injection, prepared statements are generally preferred. They're more secure and often more performant for repeated queries.
Database-Specific Behavior
PDO::quote behavior can vary between database drivers as shown here.
<?php
declare(strict_types=1);
$string = "It's a test";
// MySQL
$mysql = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
echo "MySQL: " . $mysql->quote($string) . "\n";
// SQLite
$sqlite = new PDO('sqlite:test.db');
echo "SQLite: " . $sqlite->quote($string) . "\n";
// PostgreSQL
$pgsql = new PDO('pgsql:host=localhost;dbname=testdb', 'user', 'password');
echo "PostgreSQL: " . $pgsql->quote($string) . "\n";
Different database drivers may implement quote differently. Always test with your specific database to understand the exact behavior of PDO::quote.
Best Practices
- Prepared Statements First: Use them instead of quote when possible.
- Driver Awareness: Know your database's quote behavior.
- Type Safety: Specify parameter types when quoting.
- Error Handling: Always check for quote failures.
- Performance: Avoid quote in loops; use prepared statements.
Source
This tutorial covered the PDO::quote method with practical examples showing its usage in different database scenarios.
Author
List all PHP PDO Functions.