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¶

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