ExecuteRow() vs. QueryBuilder()

PHPMaker User Submitted Tips (PHPMaker)
1

For example, if you want to get a user by username (from user input):

Raw SQL (vulnerable)

$username = $_GET['username'] ?? '';

$sql = "SELECT * FROM users WHERE username = '$username' LIMIT 1";

$user = ExecuteRow($sql);

Using ExecuteRow() is fine if the SQL does not contain user input, but it becomes vulnerable if it accepts user input (e.g. $username) which may contain malicious part, e.g. ' OR 1=1; --, your sql will become:

SELECT * FROM users WHERE username = '' OR 1=1; --'

Problems:

  • Direct concatenation of $username → SQL injection risk.
  • Manual escaping would be needed, e.g. AdjustSql($username).

DBAL QueryBuilder (safe)

use Doctrine\DBAL\Types\Types;

$username = $_GET['username'] ?? '';

$user = QueryBuilder(); // Assume main database
   ->select('*')
   ->from('users')
   ->where('username = :username')
   ->setParameter('username', $username, Types::STRING)
   ->setMaxResults(1)
   ->executeQuery()
   ->fetchAssociative();

Yes, more code, but more benefits:

  1. SQL Injection Safe

    • User input is bound as a parameter, never concatenated into SQL.
    • Even if $username = "' OR 1=1 --", the database treats it as a string literal.

  2. Readable and Fluent

    • Method chaining clearly shows the query structure.

  3. Dynamic and Extendable

    • Adding extra filters is easy:
    if ($status !== null) {
    $qb->andWhere('status = :status')
       ->setParameter('status', $status);
}

  4. Portable

    • Works across different databases without changing SQL syntax.

  5. Type-safe

    • Using Types::STRING ensures the DB interprets it correctly.

What types are available? See source code of \Doctrine\DBAL\Types\Types.

2

This also applies to ExecuteScalar(), e.g.

Raw SQL (vulnerable)

$userId = $_GET['id'] ?? 0;
$sql = "SELECT email FROM users WHERE id = $userId LIMIT 1";
$email = ExecuteScalar($sql); // Vulnerable if $userId is malicious

DBAL QueryBuilder (safe)

use Doctrine\DBAL\Types\Types;

$userId = $_GET['id'] ?? 0;
$qb = QueryBuilder();
$qb->select('email')
   ->from('users')
   ->where('id = :id')
   ->setParameter('id', $userId, Types::INTEGER)
   ->setMaxResults(1);
$email = $qb->executeQuery()->fetchOne(); // Safe, returns first column of first row
3

If you want to get data based on TWO criteria in WHERE clause, then you may check this: How to Load Data from Database into Custom Files (v2026) - #2 by mobhar