SQL Injection Prevention

SQL injection is one of the most dangerous and common web application vulnerabilities. This guide covers how to protect your XOOPS modules from SQL injection attacks.

Security-Best-Practices - Comprehensive security guide
CSRF-Protection - Token system and XoopsSecurity class
Input-Sanitization - MyTextSanitizer and validation

Understanding SQL Injection

SQL injection occurs when user input is included directly in SQL queries without proper sanitization or parameterization.

Vulnerable Code Example

// DANGEROUS - DO NOT USE
$id = $_GET['id'];
$sql = "SELECT * FROM " . $xoopsDB->prefix('items') . " WHERE id = " . $id;
$result = $xoopsDB->query($sql);

If a user passes 1 OR 1=1 as the ID, the query becomes:

SELECT * FROM xoops_items WHERE id = 1 OR 1=1

This returns all records instead of just one.

Using Parameterized Queries

The most effective defense against SQL injection is using parameterized queries (prepared statements).

Basic Parameterized Query

// Get database connection
$xoopsDB = XoopsDatabaseFactory::getDatabaseConnection();

// SECURE - Using parameterized query
$sql = "SELECT * FROM " . $xoopsDB->prefix('mytable') . " WHERE id = ?";
$result = $xoopsDB->query($sql, [(int)$_GET['id']]);

Multiple Parameters

$sql = "SELECT * FROM " . $xoopsDB->prefix('mytable') .
       " WHERE username = ? AND status = ?";
$result = $xoopsDB->query($sql, [$username, $status]);

Named Parameters

Some database abstractions support named parameters:

$sql = "SELECT * FROM " . $xoopsDB->prefix('mytable') .
       " WHERE username = :username AND status = :status";
$result = $xoopsDB->query($sql, [
    ':username' => $username,
    ':status' => $status
]);

Using XoopsObject and XoopsObjectHandler

XOOPS provides object-oriented database access that helps prevent SQL injection through the Criteria system.

Basic Criteria Usage

// Get the handler
$itemHandler = xoops_getModuleHandler('item', 'mymodule');

// Create criteria
$criteria = new Criteria('category_id', (int)$categoryId);

// Get objects - automatically safe from SQL injection
$items = $itemHandler->getObjects($criteria);

CriteriaCompo for Multiple Conditions

$criteria = new CriteriaCompo();
$criteria->add(new Criteria('category_id', (int)$categoryId));
$criteria->add(new Criteria('status', 'published'));
$criteria->add(new Criteria('uid', (int)$userId));

// Optional: Add ordering and limits
$criteria->setSort('created');
$criteria->setOrder('DESC');
$criteria->setLimit(10);
$criteria->setStart(0);

$items = $itemHandler->getObjects($criteria);

Criteria Operators

// Equal (default)
$criteria->add(new Criteria('status', 'active'));

// Not equal
$criteria->add(new Criteria('status', 'deleted', '!='));

// Greater than
$criteria->add(new Criteria('count', 100, '>'));

// Less than or equal
$criteria->add(new Criteria('price', 50, '<='));

// LIKE (for partial matching)
$criteria->add(new Criteria('title', '%' . $searchTerm . '%', 'LIKE'));

// IN (multiple values)
$criteria->add(new Criteria('id', '(' . implode(',', $ids) . ')', 'IN'));

OR Conditions

$criteria = new CriteriaCompo();
$criteria->add(new Criteria('status', 'published'));

// OR condition
$orCriteria = new CriteriaCompo();
$orCriteria->add(new Criteria('uid', (int)$userId), 'OR');
$orCriteria->add(new Criteria('is_public', 1), 'OR');

$criteria->add($orCriteria);

Table Prefixes

Always use the XOOPS table prefix system:

// Correct - using prefix
$table = $xoopsDB->prefix('mytable');
$sql = "SELECT * FROM {$table} WHERE id = ?";

// Also correct
$sql = "SELECT * FROM " . $xoopsDB->prefix('mytable') . " WHERE id = ?";

INSERT Queries

Using Prepared Statements

$sql = "INSERT INTO " . $xoopsDB->prefix('mytable') .
       " (title, content, uid, created) VALUES (?, ?, ?, ?)";

$result = $xoopsDB->query($sql, [
    $title,
    $content,
    (int)$userId,
    time()
]);

if ($result) {
    $newId = $xoopsDB->getInsertId();
}

Using XoopsObject

// Create new object
$item = $itemHandler->create();

// Set values - handler escapes automatically
$item->setVar('title', $title);
$item->setVar('content', $content);
$item->setVar('uid', (int)$userId);
$item->setVar('created', time());

// Insert
if ($itemHandler->insert($item)) {
    $newId = $item->getVar('itemid');
}

UPDATE Queries

Using Prepared Statements

$sql = "UPDATE " . $xoopsDB->prefix('mytable') .
       " SET title = ?, content = ?, updated = ? WHERE id = ?";

$result = $xoopsDB->query($sql, [
    $title,
    $content,
    time(),
    (int)$id
]);

Using XoopsObject

// Get existing object
$item = $itemHandler->get((int)$id);

if ($item) {
    $item->setVar('title', $title);
    $item->setVar('content', $content);
    $item->setVar('updated', time());

    $itemHandler->insert($item);
}

DELETE Queries

Using Prepared Statements

$sql = "DELETE FROM " . $xoopsDB->prefix('mytable') . " WHERE id = ?";
$result = $xoopsDB->query($sql, [(int)$id]);

Using XoopsObject

$item = $itemHandler->get((int)$id);
if ($item) {
    $itemHandler->delete($item);
}

Bulk Delete with Criteria

$criteria = new Criteria('status', 'deleted');
$itemHandler->deleteAll($criteria);

Escaping When Necessary

If you cannot use prepared statements, use proper escaping:

// Using mysqli_real_escape_string
$safe_value = $xoopsDB->escape($value);
$sql = "SELECT * FROM " . $xoopsDB->prefix('mytable') .
       " WHERE title = '" . $safe_value . "'";

However, always prefer prepared statements over escaping.

Building Dynamic Queries Safely

Safe Dynamic Column Names

Column names cannot be parameterized. Validate against a whitelist:

$allowed_columns = ['title', 'created', 'updated', 'status'];
$sort = $_GET['sort'] ?? 'created';

if (!in_array($sort, $allowed_columns)) {
    $sort = 'created'; // Default safe value
}

$sql = "SELECT * FROM " . $xoopsDB->prefix('mytable') .
       " ORDER BY {$sort} DESC";

Safe Dynamic Table Names

Similarly, validate table names:

$allowed_tables = ['items', 'categories', 'comments'];
$table = $_GET['table'] ?? 'items';

if (!in_array($table, $allowed_tables)) {
    $table = 'items';
}

$sql = "SELECT * FROM " . $xoopsDB->prefix($table) . " WHERE id = ?";

Building WHERE Clauses Dynamically

$criteria = new CriteriaCompo();

// Add conditions based on input
if (!empty($_GET['category'])) {
    $criteria->add(new Criteria('category_id', (int)$_GET['category']));
}

if (!empty($_GET['status'])) {
    $allowed_statuses = ['draft', 'published', 'archived'];
    if (in_array($_GET['status'], $allowed_statuses)) {
        $criteria->add(new Criteria('status', $_GET['status']));
    }
}

if (!empty($_GET['search'])) {
    $search = '%' . $_GET['search'] . '%';
    $criteria->add(new Criteria('title', $search, 'LIKE'));
}

$items = $itemHandler->getObjects($criteria);

LIKE Queries

Be careful with LIKE queries to avoid wildcard injection:

// Escape special characters in search term
$searchTerm = str_replace(['%', '_'], ['\%', '\_'], $searchTerm);

// Then use in LIKE
$criteria->add(new Criteria('title', '%' . $searchTerm . '%', 'LIKE'));

IN Clauses

When using IN clauses, ensure all values are properly typed:

// Array of IDs from user input
$inputIds = $_POST['ids'] ?? [];

// Sanitize: ensure all are integers
$safeIds = array_map('intval', $inputIds);
$safeIds = array_filter($safeIds, function($id) { return $id > 0; });

if (!empty($safeIds)) {
    $idList = implode(',', $safeIds);
    $sql = "SELECT * FROM " . $xoopsDB->prefix('mytable') .
           " WHERE id IN ({$idList})";
    $result = $xoopsDB->query($sql);
}

Or with Criteria:

if (!empty($safeIds)) {
    $criteria = new Criteria('id', '(' . implode(',', $safeIds) . ')', 'IN');
    $items = $itemHandler->getObjects($criteria);
}

Transaction Safety

When performing multiple related queries:

// Start transaction
$xoopsDB->query("START TRANSACTION");

try {
    // Query 1
    $sql1 = "INSERT INTO " . $xoopsDB->prefix('items') . " (title) VALUES (?)";
    $result1 = $xoopsDB->query($sql1, [$title]);

    if (!$result1) {
        throw new Exception('Insert failed');
    }

    $itemId = $xoopsDB->getInsertId();

    // Query 2
    $sql2 = "INSERT INTO " . $xoopsDB->prefix('item_meta') .
            " (item_id, meta_key, meta_value) VALUES (?, ?, ?)";
    $result2 = $xoopsDB->query($sql2, [$itemId, 'author', $author]);

    if (!$result2) {
        throw new Exception('Meta insert failed');
    }

    // Commit
    $xoopsDB->query("COMMIT");

} catch (Exception $e) {
    // Rollback on error
    $xoopsDB->query("ROLLBACK");
    throw $e;
}

Error Handling

Never expose SQL errors to users:

$sql = "SELECT * FROM " . $xoopsDB->prefix('mytable') . " WHERE id = ?";
$result = $xoopsDB->query($sql, [(int)$id]);

if (!$result) {
    // Log the actual error for debugging
    error_log('Database error: ' . $xoopsDB->error());

    // Show generic message to user
    redirect_header('index.php', 3, 'An error occurred. Please try again.');
    exit();
}

Common Mistakes to Avoid

Mistake 1: Direct Variable Interpolation

// WRONG
$sql = "SELECT * FROM {$table} WHERE id = {$id}";

// RIGHT
$sql = "SELECT * FROM " . $xoopsDB->prefix('mytable') . " WHERE id = ?";
$result = $xoopsDB->query($sql, [(int)$id]);

Mistake 2: Using addslashes()

// WRONG - addslashes is NOT sufficient
$safe = addslashes($_GET['input']);

// RIGHT - use parameterized queries or proper escaping
$sql = "SELECT * FROM table WHERE col = ?";
$result = $xoopsDB->query($sql, [$_GET['input']]);

Mistake 3: Trusting Numeric IDs

// WRONG - assuming input is numeric
$id = $_GET['id'];
$sql = "SELECT * FROM table WHERE id = " . $id;

// RIGHT - explicitly cast to integer
$id = (int)$_GET['id'];
$sql = "SELECT * FROM table WHERE id = ?";
$result = $xoopsDB->query($sql, [$id]);

Mistake 4: Second-Order Injection

// Data from database is NOT automatically safe
$userData = $itemHandler->get($id);
$username = $userData->getVar('username');

// WRONG - trusting data from database
$sql = "SELECT * FROM log WHERE username = '" . $username . "'";

// RIGHT - always use parameters
$sql = "SELECT * FROM log WHERE username = ?";
$result = $xoopsDB->query($sql, [$username]);

Security Testing

Test Your Queries

Test your forms with these inputs to check for SQL injection:

' OR '1'='1
1; DROP TABLE users--
1 UNION SELECT * FROM users--
admin'--
' OR 1=1#

If any of these cause unexpected behavior or errors, you have a vulnerability.

Automated Testing

Use automated SQL injection testing tools during development:

SQLMap
Burp Suite
OWASP ZAP

Best Practices Summary

Always use parameterized queries (prepared statements)
Use XoopsObject/XoopsObjectHandler when possible
Use Criteria classes for building queries
Whitelist allowed values for columns and table names
Cast numeric values explicitly with (int) or (float)
Never expose database errors to users
Use transactions for multiple related queries
Test for SQL injection during development
Escape LIKE wildcards in search queries
Sanitize IN clause values individually

#security #sql-injection #database #xoops #prepared-statements #Criteria

SQL Injection Prevention

Related Documentation

Understanding SQL Injection

Vulnerable Code Example

Using Parameterized Queries

Basic Parameterized Query

Multiple Parameters

Named Parameters

Using XoopsObject and XoopsObjectHandler

Basic Criteria Usage

CriteriaCompo for Multiple Conditions

Criteria Operators

OR Conditions

Table Prefixes

INSERT Queries

Using Prepared Statements

Using XoopsObject

UPDATE Queries

Using Prepared Statements

Using XoopsObject

DELETE Queries

Using Prepared Statements

Using XoopsObject

Bulk Delete with Criteria

Escaping When Necessary

Building Dynamic Queries Safely

Safe Dynamic Column Names

Safe Dynamic Table Names

Building WHERE Clauses Dynamically

LIKE Queries

IN Clauses

Transaction Safety

Error Handling

Common Mistakes to Avoid

Mistake 1: Direct Variable Interpolation

Mistake 2: Using addslashes()

Mistake 3: Trusting Numeric IDs

Mistake 4: Second-Order Injection

Security Testing

Test Your Queries

Automated Testing

Best Practices Summary