Skip to content
intermediate Phase 71 · Security Fundamentals

SQL Injection Prevention

45m
2 problems
Topic Progress 0%

Prepared Statements in Magento

PDO Prepared Statements

use Magento\Framework\DB\Adapter\PDO\Adapter;

class SecureQuery
{
    private $connection;

    public function __construct(Adapter $connection)
    {
        $this->connection = $connection;
    }

    public function findUser($email)
    {
        // UNSAFE - never do this
        $sql = "SELECT * FROM customer_entity WHERE email = '" . $email . "'";
        
        // SAFE - use prepared statement
        $sql = 'SELECT * FROM customer_entity WHERE email = ?';
        return $this->connection->fetchRow($sql, [$email]);
    }
}

Magento's fetchRow with Parameters

// Using ? placeholder (positional)
$sql = 'SELECT * FROM catalog_product_entity WHERE sku = ?';
$product = $this->connection->fetchRow($sql, [$sku]);

// Using :named placeholder
$sql = 'SELECT * FROM sales_order WHERE entity_id = :order_id';
$order = $this->connection->fetchRow($sql, ['order_id' => $orderId]);

// Multiple parameters
$sql = 'SELECT * FROM sales_order WHERE store_id = ? AND status = ?';
$orders = $this->connection->fetchAll($sql, [$storeId, $status]);

Key Points

  • Always use parameterized queries, never string concatenation
  • Magento's DB adapter automatically handles escaping
  • Prepared statements prevent SQL injection by separating code from data
  • Use fetchRow, fetchAll, fetchCol for different result types

Parameterized Queries

Complex Parameterized Queries

// Dynamic WHERE conditions
public function getProducts($categoryIds, $priceRange)
{
    $select = $this->connection->select()
        ->from('catalog_product_entity', ['entity_id', 'sku'])
        ->where('entity_id IN (?)', $categoryIds)
        ->where('price BETWEEN ? AND ?', $priceRange);

    return $this->connection->fetchAll($select);
}

// Subquery with parameters
public function getOrdersWithProducts($customerId)
{
    $select = $this->connection->select()
        ->from('sales_order', ['entity_id', 'increment_id'])
        ->where('customer_id = ?', $customerId)
        ->where(
            'entity_id IN (?)',
            $this->connection->select()
                ->from('sales_order_item', ['order_id'])
                ->group('order_id')
        );

    return $this->connection->fetchAll($select);
}

IN Clause Handling

// Safe IN clause with array parameter
$productIds = [1, 2, 3, 4, 5];
$sql = 'SELECT * FROM catalog_product_entity WHERE entity_id IN (?)';
$results = $this->connection->fetchAll($sql, [$productIds]);

// Magento automatically expands array into placeholders
// Generates: WHERE entity_id IN (?, ?, ?, ?, ?)

Key Points

  • Arrays in IN clauses are automatically parameterized
  • Named parameters improve readability in complex queries
  • Always validate input types before parameterization
  • Use type casting for integer parameters when needed

Magento Query Builder

Select Query Builder

use Magento\Framework\DB\Select;

class ProductRepository
{
    public function searchProducts($filters)
    {
        $select = $this->connection->select()
            ->from(
                ['p' => 'catalog_product_entity'],
                ['entity_id', 'sku', 'name']
            )
            ->join(
                ['pv' => 'catalog_product_entity_varchar'],
                'p.entity_id = pv.entity_id AND pv.attribute_id = ?',
                ['value']
            )
            ->where('pv.attribute_id = ?', 71)  // name attribute
            ->where('pv.value LIKE ?', '%' . $filters['name'] . '%')
            ->order('p.entity_id DESC')
            ->limit($filters['limit'] ?? 10);

        return $this->connection->fetchAll($select);
    }
}

Insert/Update with Security

// Safe insert with parameters
$this->connection->insert('customer_entity', [
    'email' => $customerData['email'],
    'firstname' => $customerData['firstname'],
    'lastname' => $customerData['lastname'],
    'password_hash' => $this->encryptor->getHash($password),
]);

// Safe update with conditions
$this->connection->update(
    'customer_entity',
    ['group_id' => $newGroupId],
    ['entity_id = ?' => $customerId]
);

Key Points

  • Query builder automatically handles escaping
  • Never use raw SQL with user input
  • Use table aliases for complex joins
  • Bind parameters are safer than string interpolation

Common SQL Injection Attacks

Attack Vectors

// 1. Authentication bypass
// Input: admin' OR '1'='1' --
// Result: SELECT * FROM admin_user WHERE username='admin' OR '1'='1' --

// 2. UNION-based injection
// Input: 1 UNION SELECT username, password FROM admin_user --
// Result: Returns admin credentials

// 3. Blind SQL injection
// Input: 1 AND (SELECT LENGTH(password) FROM admin_user WHERE username='admin') > 10
// Result: Boolean response reveals data

// 4. Time-based blind injection
// Input: 1 AND IF(SUBSTRING(password,1,1)='a', SLEEP(5), 0)
// Result: Response delay indicates correct character

Detection Signs

// Signs of SQL injection attempts in logs:
// - Unusual characters in input: ' " ; -- /* */
// - Error messages revealing SQL structure
// - Unexpected database errors
// - Abnormal query patterns

// Log suspicious activity
$this->logger->warning('Possible SQL injection attempt', [
    'ip' => $request->getServerValue('REMOTE_ADDR'),
    'input' => $input,
    'uri' => $request->getRequestUri(),
]);

Key Points

  • SQL injection is the most common web vulnerability
  • Always validate and sanitize user input
  • Use least privilege database accounts
  • Monitor logs for suspicious query patterns

Practice Problems

0 / 2 solved
Secure Search Query

Implement a product search that safely handles user input without SQL injection vulnerabilities.

Solution 
// Secure implementation
public function search($query) {
    $sanitized = trim($query);
    $sanitized = preg_replace('/[^a-zA-Z0-9\s]/', '', $sanitized);
    
    $select = $this->connection->select()
        ->from('catalog_product_entity', ['entity_id', 'sku', 'name'])
        ->where('name LIKE ?', '%' . $sanitized . '%');
    
    return $this->connection->fetchAll($select);
}
Dynamic Query Builder

Build a dynamic filtering system that safely handles multiple user-provided filter conditions.

Solution 
public function filterProducts($filters) {
    $allowedFields = ['status', 'visibility', 'type_id'];
    $select = $this->connection->select()
        ->from('catalog_product_entity', ['entity_id', 'sku']);
    
    foreach ($filters as $field => $value) {
        if (in_array($field, $allowedFields)) {
            $select->where($field . ' = ?', $value);
        }
    }
    
    return $this->connection->fetchAll($select);
}

Quiz

1. Which is the SAFE way to query in Magento?

Question 1 options

2. How does Magento handle array parameters in IN clauses?

Question 2 options

3. What is a sign of SQL injection in logs?

Question 3 options

4. Why use query builder over raw SQL?

Question 4 options

Flashcards

Question

Safe query method?

Click to reveal answer

Answer

Parameterized queries: fetchRow($sql, [$param])

Question

SQL injection prevention?

Click to reveal answer

Answer

Use prepared statements, never concatenate user input

Question

Magento query builder benefit?

Click to reveal answer

Answer

Automatic escaping and parameterization

Question

Blind SQL injection?

Click to reveal answer

Answer

Boolean or time-based responses reveal data without direct output

Revision Notes

Key Takeaways

  • 1. Never concatenate user input into SQL queries
  • 2. Use parameterized queries with ? placeholders
  • 3. Magento query builder handles escaping automatically
  • 4. Monitor logs for SQL injection attempts

Interview Tips

  • • Explain how prepared statements prevent SQL injection
  • • Discuss different types of SQL injection attacks
  • • Know Magento's DB adapter methods

Cheat Sheet

SQL Injection Prevention

  • Use: fetchRow($sql, [$params])
  • Never: string concatenation in SQL
  • Query builder: automatic escaping
  • Monitor: logs for suspicious input