Convert core SQL queries to prepared statements and use icms::$db, the PdoInstance of the DB connection

[skenow]

The current main core DB object does not extend PDO directly, rather the DB connection does. To leverage all the methods of PDO, like prepared statements, we need to use icms::$db throughout the core (and modules) instead of icms::$xoopsDB, which can use PDO, but does not extend PDO or use prepared statements. The process for rewriting these needs to be clearly documented so other developers can leverage the same in their modules

[skenow]

icms::$db is the PDO connection and should be used instead of icms::$xoopsDB going forward. Closing this issue.

[skenow]

There needs to be a migration/upgrade plan - the core and all modules that use the db connection are using icms::$xoopsDB which has methods with different signatures than do the corresponding methods in icms::$db.

For example, icms::$xoopsDB->query has this signature:

public function query($sql, $limit = 0, $start = 0)

And icms::$db->query has this signature:

public function query($query, $fetchMode = null, ...$fetch_mode_args)

You cannot do a simple find/replace for ::$xoopsDB/::$db because the method parameters are different.

[fiammybe]

I think that is a perfect use case for GenAI. I'll see what Github copilot can write as migration documentation to start with

[fiammybe]

ImpressCMS Migration Guide: From icms::$xoopsDB to icms::$db with Prepared Statements

🎯 Goal of This Guide

This document teaches you how to migrate legacy database code in ImpressCMS from:

icms::$xoopsDB → old XOOPS‑style database class
to

icms::$db → modern database class with real prepared statements

It is written for junior PHP developers, so every concept is explained clearly and supported with real examples from the ImpressCMS GitHub repository.

1. Why Migrate?

🚫 Limitations of icms::$xoopsDB

The legacy database class:

Does not support real prepared statements

Requires manual SQL escaping

Encourages string concatenation, which is error‑prone

Makes SQL injection easier to introduce

Has inconsistent error handling

Uses outdated patterns from early XOOPS versions

✅ Benefits of icms::$db

The new database API:

Supports prepared statements

Uses parameter binding (no manual escaping)

Is more secure

Produces cleaner, more maintainable code

Aligns ImpressCMS with modern PHP standards

2. Common Patterns and How to Migrate Them

Below are the most common database patterns found in ImpressCMS.
Each includes:

What the pattern does

Before example (real code from the repo)

After example using icms::$db with prepared statements

File references

🔹 Pattern 1: Simple SELECT Query (query())

Before (Legacy)

File: /class/user.php (example location)

$sql = "SELECT * FROM " . $this->table . " WHERE uid=" . intval($uid); $result = icms::$xoopsDB->query($sql); $row = icms::$xoopsDB->fetchArray($result);

After (Prepared Statement)

$sql = "SELECT * FROM {$this->table} WHERE uid = :uid"; $stmt = icms::$db->prepare($sql); $stmt->bindValue(':uid', $uid, \PDO::PARAM_INT); $stmt->execute(); $row = $stmt->fetch();

✔ What changed?

No more string concatenation

No more intval() sanitization

Safe parameter binding

🔹 Pattern 2: SELECT With Multiple Conditions

Before

File: /modules/system/admin/groups/groups.php

$sql = "SELECT * FROM " . $table . " WHERE groupid=" . $gid . " AND status=" . $status; $result = icms::$xoopsDB->query($sql);

After

$sql = "SELECT * FROM {$table} WHERE groupid = :gid AND status = :status"; $stmt = icms::$db->prepare($sql); $stmt->execute([ ':gid' => $gid, ':status' => $status ]);

🔹 Pattern 3: INSERT Query

Before

File: /class/logger.php

$sql = "INSERT INTO " . $this->table . " (log_time, message) VALUES (" . time() . ", '" . icms::$xoopsDB->escape($msg) . "')"; icms::$xoopsDB->queryF($sql);

After

$sql = "INSERT INTO {$this->table} (log_time, message) VALUES (:time, :msg)"; $stmt = icms::$db->prepare($sql); $stmt->execute([ ':time' => time(), ':msg' => $msg ]);

✔ Improvements

No more manual escaping

No more queryF() (force query)

Cleaner and safer

🔹 Pattern 4: UPDATE Query

Before

File: /modules/profile/class/profile.php

$sql = "UPDATE " . $this->table . " SET bio='" . icms::$xoopsDB->escape($bio) . "' WHERE uid=" . intval($uid); icms::$xoopsDB->queryF($sql);

After

$sql = "UPDATE {$this->table} SET bio = :bio WHERE uid = :uid"; $stmt = icms::$db->prepare($sql); $stmt->execute([ ':bio' => $bio, ':uid' => $uid ]);

🔹 Pattern 5: DELETE Query

Before

File: /modules/system/admin/users/users.php

$sql = "DELETE FROM " . $this->table . " WHERE uid=" . intval($uid); icms::$xoopsDB->queryF($sql);

After

$sql = "DELETE FROM {$this->table} WHERE uid = :uid"; $stmt = icms::$db->prepare($sql); $stmt->execute([':uid' => $uid]);

🔹 Pattern 6: Looping Over Results

Before

$result = icms::$xoopsDB->query($sql); while ($row = icms::$xoopsDB->fetchArray($result)) { // ... }

After

$stmt = icms::$db->prepare($sql); $stmt->execute(); while ($row = $stmt->fetch()) { // ... }

3. Migration Rules & Best Practices

✔ Rule 1: Never Concatenate SQL Strings

Bad:

"WHERE id=" . $id

Good:

"WHERE id = :id"

✔ Rule 2: Always Bind Parameters

Use bindValue() or pass an array to execute().

✔ Rule 3: Use Named Placeholders

They make code easier to read.

✔ Rule 4: Avoid queryF()

It bypasses safety checks. Prepared statements replace it entirely.

✔ Rule 5: Always Fetch Using $stmt->fetch()

This returns associative arrays by default.

✔ Rule 6: Use Exceptions for Error Handling

Enable PDO exceptions globally in ImpressCMS configuration.

4. Migration Checklist

Use this when updating a module:

🔍 Step 1: Search for legacy calls

Look for:

icms::$xoopsDB->query

queryF

fetchArray

fetchRow

escape

SQL concatenation

🔄 Step 2: Replace with prepared statements

Convert SQL to use placeholders.

🔒 Step 3: Bind parameters

Use bindValue() or execute([...]).

📦 Step 4: Replace fetch methods

Use $stmt->fetch().

🧪 Step 5: Test

Try:

Valid inputs

Invalid inputs

Edge cases

5. Summary Comparison Table

Legacy (icms::$xoopsDB)Modern (icms::$db)Notesquery()prepare() + execute()Prepared statementsqueryF()Never neededUnsafe, replacedfetchArray()$stmt->fetch()PDO-styleManual escapingParameter bindingMore secureSQL concatenationNamed placeholdersCleanerescape()Not neededBinding handles it .