Doctrine DBAL Dangerous Query Construction

Critical Risk SQL Injection
phpdoctrinedbalsql-injectiondatabaseparameterization

What it is

The PHP application uses Doctrine DBAL to construct database queries with user-controlled input without proper parameterization or escaping, leading to SQL injection vulnerabilities. This occurs when raw SQL is built using string concatenation or interpolation with untrusted data.

// Vulnerable: String concatenation in DBAL query
use Doctrine\DBAL\Connection;

function getUserData(Connection $conn, $userId, $status) {
    // Dangerous: Direct string interpolation
    $sql = "SELECT * FROM users WHERE id = $userId AND status = '$status'";
    
    $stmt = $conn->prepare($sql);
    $result = $stmt->executeQuery();
    
    return $result->fetchAllAssociative();
}

// Usage with user input
$userId = $_GET['user_id']; // Dangerous
$status = $_POST['status']; // Dangerous
$users = getUserData($connection, $userId, $status);
// Secure: Parameterized queries with DBAL
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\ParameterType;

function getUserData(Connection $conn, int $userId, string $status): array {
    // Validate input
    if ($userId <= 0) {
        throw new InvalidArgumentException('Invalid user ID');
    }
    
    $allowedStatuses = ['active', 'inactive', 'pending'];
    if (!in_array($status, $allowedStatuses, true)) {
        throw new InvalidArgumentException('Invalid status');
    }
    
    // Secure: Parameterized query
    $sql = 'SELECT id, username, email, status FROM users WHERE id = :userId AND status = :status';
    
    $stmt = $conn->prepare($sql);
    $stmt->bindValue('userId', $userId, ParameterType::INTEGER);
    $stmt->bindValue('status', $status, ParameterType::STRING);
    
    $result = $stmt->executeQuery();
    return $result->fetchAllAssociative();
}

// Usage with validated input
$userId = filter_input(INPUT_GET, 'user_id', FILTER_VALIDATE_INT);
$status = filter_input(INPUT_POST, 'status', FILTER_SANITIZE_STRING);

if ($userId && $status) {
    try {
        $users = getUserData($connection, $userId, $status);
    } catch (Exception $e) {
        error_log('Database error: ' . $e->getMessage());
        // Handle error appropriately
    }
}

💡 Why This Fix Works

The vulnerable code was updated to address the security issue.

Why it happens

PHP applications construct SQL queries by concatenating user-controlled input directly into SQL strings using PHP's string interpolation or concatenation operators. Developers write queries like $sql = "SELECT * FROM users WHERE id = $userId AND name = '$userName'"; or $sql = 'DELETE FROM posts WHERE id = ' . $_POST['post_id'];, embedding $_GET, $_POST, or $_REQUEST values directly into SQL command strings without any parameterization. This pattern treats SQL structure and user data as equivalent string content, allowing attackers to inject additional SQL syntax through user input. An attacker providing userId=1 OR 1=1-- or userName=' OR '1'='1 can manipulate the query logic to bypass authentication, extract unauthorized data, or execute administrative database operations. The PHP double-quote string interpolation feature that embeds variable values directly into strings ($variable within "string") makes this vulnerability particularly easy to introduce, as developers naturally write SQL queries as strings without considering the security boundary between trusted SQL syntax and untrusted user data.

Root causes

Using String Concatenation to Build SQL Queries with User Input

PHP applications construct SQL queries by concatenating user-controlled input directly into SQL strings using PHP's string interpolation or concatenation operators. Developers write queries like $sql = "SELECT * FROM users WHERE id = $userId AND name = '$userName'"; or $sql = 'DELETE FROM posts WHERE id = ' . $_POST['post_id'];, embedding $_GET, $_POST, or $_REQUEST values directly into SQL command strings without any parameterization. This pattern treats SQL structure and user data as equivalent string content, allowing attackers to inject additional SQL syntax through user input. An attacker providing userId=1 OR 1=1-- or userName=' OR '1'='1 can manipulate the query logic to bypass authentication, extract unauthorized data, or execute administrative database operations. The PHP double-quote string interpolation feature that embeds variable values directly into strings ($variable within "string") makes this vulnerability particularly easy to introduce, as developers naturally write SQL queries as strings without considering the security boundary between trusted SQL syntax and untrusted user data.

Improper Use of Doctrine DBAL Query Methods

Developers misuse Doctrine DBAL's query execution methods by passing SQL strings with embedded user input to Connection::executeQuery(), Connection::executeStatement(), or Connection::prepare() without utilizing the parameter binding features. Code like $conn->executeQuery("SELECT * FROM users WHERE email = '{$_GET['email']}'") uses the query execution methods intended for prepared statements but defeats their security benefits by pre-building the SQL string with user input. Another common pattern involves using QueryBuilder but calling setParameter() incorrectly or using expr() methods with raw user input: $qb->where('u.name = ' . $userName) instead of $qb->where('u.name = :name')->setParameter('name', $userName). Developers may also use Connection::exec() or Connection::query() methods which don't support parameter binding, encouraging insecure query construction. These misuses occur when developers understand they should use DBAL's query methods but don't understand the parameterization features that make those methods secure, treating them as simple SQL execution wrappers rather than SQL injection protection mechanisms.

Missing Parameterization in Custom SQL Queries

Applications execute complex custom SQL queries that require dynamic construction (JOIN clauses based on relationships, WHERE conditions with variable column names, ORDER BY with user-selected sort fields) but implement this dynamism through string manipulation rather than parameterization. Developers build queries for advanced features like search filters, sorting, pagination, or reporting where query structure varies based on user selections, leading to code like: foreach ($filters as $field => $value) { $whereClauses[] = "$field = '$value'"; } followed by $sql = 'SELECT * FROM products WHERE ' . implode(' AND ', $whereClauses);. While column names and table names legitimately cannot be parameterized in prepared statements (they must be part of the SQL structure), developers extend this pattern to include values, which should always be parameterized. The complexity of building dynamic queries makes developers resort to string building rather than understanding DBAL's parameter types (ParameterType::INTEGER, ParameterType::STRING, ParameterType::BOOLEAN) and array parameter binding for IN clauses: $stmt->executeQuery($sql, ['ids' => $idArray], ['ids' => Connection::PARAM_INT_ARRAY]).

Using User Input Directly in WHERE Clauses or Other SQL Fragments

Database query functions incorporate user-supplied values directly into SQL WHERE clauses, ORDER BY clauses, LIMIT expressions, or column name identifiers without validation or parameterization. Common vulnerable patterns include search functionality: $sql = "SELECT * FROM articles WHERE title LIKE '%{$_GET['search']}%'", allowing attackers to inject SQL through the search parameter. Sorting features where users select sort columns: $sql = "SELECT * FROM products ORDER BY {$_GET['sort']} {$_GET['direction']}" enable attackers to inject UNION SELECT statements through the sort parameter. Pagination using user-controlled values: $sql = "SELECT * FROM items LIMIT {$_GET['page_size']} OFFSET {$_GET['offset']}" allows injection through numeric parameters. Dynamic table or column selection: $sql = "SELECT {$_POST['columns']} FROM {$_POST['table']} WHERE id = 1" grants attackers complete control over query structure. These patterns emerge from developers' desire to create flexible, user-controlled query interfaces without understanding that structural query elements (tables, columns, clauses) cannot be safely handled through parameterization and require strict whitelist validation instead.

Insufficient Input Validation Before Query Construction

Applications fail to implement proper input validation and type enforcement before incorporating user data into database queries, relying solely on incomplete sanitization attempts like addslashes(), mysql_real_escape_string() (in DBAL context), or str_replace() to prevent SQL injection. Developers might attempt to sanitize with code like $userId = str_replace("'", "''", $_GET['id']); then use $sql = "SELECT * FROM users WHERE id = '$userId'", which doesn't prevent injection through other SQL metacharacters or encoding bypasses. Type validation is missing where numeric identifiers come from user input: $userId = $_GET['id']; /* assumed numeric */ followed by $sql = "SELECT * FROM users WHERE id = $userId" without calling intval($userId) or using FILTER_VALIDATE_INT. Character blacklisting approaches attempt to block SQL keywords (SELECT, UNION, DROP) which attackers easily bypass using character encoding, comment syntax, or obfuscation techniques. The absence of whitelist validation for structural query elements allows attackers to control table names, column names, and clause structure. Developers treat sanitization as an alternative to parameterization rather than understanding that parameterization is the only reliable defense against SQL injection for values, while whitelisting is required for structural elements.

Fixes

1

Use Prepared Statements with Parameter Binding

Replace all concatenated SQL queries with Doctrine DBAL prepared statements using named parameter placeholders and bindValue() or bindParam() methods: $sql = 'SELECT * FROM users WHERE id = :userId AND email = :email'; $stmt = $conn->prepare($sql); $stmt->bindValue('userId', $userId, ParameterType::INTEGER); $stmt->bindValue('email', $email, ParameterType::STRING); $result = $stmt->executeQuery();. Always use named parameters (prefixed with colon like :paramName) rather than positional parameters (?) to improve code readability and prevent parameter ordering errors. Specify explicit parameter types using Doctrine's ParameterType constants (INTEGER, STRING, BOOLEAN, BINARY, LARGE_OBJECT) to ensure proper data type handling and additional SQL injection protection. For array parameters in IN clauses, use Connection::PARAM_INT_ARRAY or Connection::PARAM_STR_ARRAY: $stmt->executeQuery($sql, ['ids' => [1, 2, 3]], ['ids' => Connection::PARAM_INT_ARRAY]). Never concatenate or interpolate user input into the SQL string itself—all dynamic values must flow through parameter binding. Test parameter binding by attempting SQL injection payloads (e.g., ' OR '1'='1) which should be treated as literal string values rather than SQL syntax when properly parameterized.

2

Employ Doctrine Query Builder for Safe Query Construction

Use Doctrine DBAL's QueryBuilder API for constructing dynamic queries with automatic parameter handling: $qb = $conn->createQueryBuilder(); $qb->select('u.id', 'u.username', 'u.email')->from('users', 'u')->where('u.status = :status')->setParameter('status', $status); $result = $qb->executeQuery();. QueryBuilder methods like where(), andWhere(), orWhere() accept parameter placeholders and automatically handle binding through setParameter(). For dynamic WHERE conditions, build conditionally: if ($searchTerm) { $qb->andWhere('u.name LIKE :search')->setParameter('search', '%' . $searchTerm . '%'); }. Use expr() methods for complex expressions: $qb->where($qb->expr()->in('u.role', ':roles'))->setParameter('roles', ['admin', 'moderator'], Connection::PARAM_STR_ARRAY). For ORDER BY clauses with user-controlled column names, validate against a whitelist first: $allowedColumns = ['id', 'name', 'created_at']; if (in_array($sortColumn, $allowedColumns, true)) { $qb->orderBy($sortColumn, $sortDirection); }. QueryBuilder provides type-safe query construction preventing most SQL injection vectors while maintaining flexibility for dynamic query requirements. Avoid calling getSQL() and manually executing the SQL string as this bypasses QueryBuilder's parameter handling.

3

Validate and Sanitize All User Input Before Database Operations

Implement comprehensive input validation using PHP's filter_input() and filter_var() functions before any database operations: $userId = filter_input(INPUT_GET, 'user_id', FILTER_VALIDATE_INT); if ($userId === false || $userId <= 0) { throw new InvalidArgumentException('Invalid user ID'); }. For string inputs, validate against whitelist patterns using regular expressions: if (!preg_match('/^[a-zA-Z0-9_-]+$/', $username)) { throw new InvalidArgumentException('Invalid username format'); }. Implement allowlist validation for enumerated values: $allowedStatuses = ['active', 'inactive', 'pending']; if (!in_array($status, $allowedStatuses, true)) { throw new InvalidArgumentException('Invalid status value'); }. Never rely on blacklist approaches that attempt to filter SQL keywords or special characters—these are trivially bypassed. For structural SQL elements that cannot be parameterized (table names, column names, ORDER BY directions), use strict whitelists: $allowedTables = ['users', 'posts', 'comments']; if (!in_array($tableName, $allowedTables, true)) { throw new SecurityException('Invalid table name'); }. Combine validation with parameterization—validation protects against malformed input while parameterization prevents SQL injection. Implement validation as early as possible in request handling, ideally in controller input validation or form handling layers before data reaches database query construction code.

4

Use Named Parameters Instead of String Interpolation

Replace all instances of PHP string interpolation and concatenation in SQL queries with named parameter placeholders: change "SELECT * FROM users WHERE email = '$email'" to 'SELECT * FROM users WHERE email = :email' with subsequent setParameter('email', $email). Named parameters provide clear separation between SQL syntax (trusted) and user data (untrusted), making code review easier and preventing accidental SQL injection through formatting mistakes. Use consistent parameter naming conventions matching variable names for clarity: $qb->where('u.username = :username')->setParameter('username', $username). For complex queries with many parameters, consider using associative arrays: $params = ['userId' => $userId, 'status' => $status, 'minAge' => $minAge]; $stmt->executeQuery($sql, $params). Never use sprintf() or string formatting functions to build SQL queries: sprintf("SELECT * FROM users WHERE id = %d", $userId) still allows injection if $userId contains non-numeric characters. Never use the alternative double-colon syntax (::parameter) as it's not supported by DBAL parameter binding. Document all query parameters with PHPDoc annotations: /** @param int $userId User identifier */ to ensure type safety through static analysis tools like PHPStan or Psalm. Configure development environments to show warnings for SQL strings containing variable interpolation as code smell indicators.

5

Implement Proper Input Validation and Type Checking

Enforce strict type validation using PHP 7.4+ typed properties and function parameter types to prevent type confusion attacks: function getUserById(Connection $conn, int $userId): ?array { /* implementation */ } ensures $userId is always integer type. Use strict type checking: declare(strict_types=1); at the top of PHP files to prevent automatic type coercion that could allow string '1 OR 1=1' to pass integer type hints. Implement custom validation classes for complex inputs: class EmailAddress { private string $value; public function __construct(string $email) { if (!filter_var($email, FILTER_VALIDATE_EMAIL)) { throw new InvalidArgumentException('Invalid email'); } $this->value = $email; } public function toString(): string { return $this->value; } }. Use value objects to encapsulate validation logic and prevent invalid data from reaching database layers. Implement assertion libraries like webmozart/assert for runtime validation: Assert::integer($userId); Assert::range($pageSize, 1, 100); Assert::email($emailAddress);. For complex validation scenarios, use validation libraries like Symfony Validator or Respect\Validation with declarative validation rules. Implement input validation at application boundaries (controllers, API endpoints) and domain model constructors to prevent invalid data from propagating through the application. Log validation failures for security monitoring to detect potential attack attempts.

6

Use Doctrine ORM Entities Instead of Raw SQL When Possible

Refactor database operations to use Doctrine ORM's Entity Manager and Repositories which provide automatic query parameterization and SQL injection protection: $user = $entityManager->getRepository(User::class)->findOneBy(['email' => $email, 'status' => 'active']); instead of raw SQL queries. ORM's DQL (Doctrine Query Language) provides SQL-like syntax with automatic parameterization: $query = $entityManager->createQuery('SELECT u FROM App\Entity\User u WHERE u.email = :email'); $query->setParameter('email', $email); $users = $query->getResult();. Use QueryBuilder with entities for complex queries: $qb = $entityManager->createQueryBuilder(); $qb->select('u')->from(User::class, 'u')->where('u.createdAt > :date')->setParameter('date', $date); $users = $qb->getQuery()->getResult();. Doctrine ORM handles all parameter binding automatically, preventing SQL injection by design. For bulk operations where raw SQL provides performance benefits, use DBAL prepared statements with explicit parameterization as secondary option. Define entity relationships using annotations/attributes (@OneToMany, @ManyToOne) to leverage ORM's automatic JOIN generation instead of manual JOIN construction. Implement custom repository methods that encapsulate query logic: class UserRepository extends EntityRepository { public function findActiveByEmail(string $email): ?User { return $this->createQueryBuilder('u')->where('u.email = :email')->andWhere('u.status = :status')->setParameter('email', $email)->setParameter('status', 'active')->getQuery()->getOneOrNullResult(); } }. This approach centralizes query logic, ensures consistent parameterization, and provides type-safe database operations through entity objects.

Detect This Vulnerability in Your Code

Sourcery automatically identifies doctrine dbal dangerous query construction and many other security issues in your codebase.

Scan Your Code for Free Explore More Vulnerabilities