ct4.us

The {{ tables.sqlselect(”) }} tag requires a separate MySQL user with read-only (SELECT-only) privileges.
This ensures that queries executed via the tag cannot modify your database.

Follow the steps below.

---

Step 1: Log in to MySQL as an administrator #

mysql -u root -p

(Or use another MySQL user with permission to create users and grant privileges.)

---

Step 2: Create a read-only MySQL user #

CREATE USER 'joe_can_read'@'localhost'
IDENTIFIED BY 'strong-password';

Replace:

• joe_can_read with your desired username
• strong-password with a secure password

---

Step 3: Grant SELECT privileges only #

GRANT SELECT ON joomla6.* TO 'joe_can_read'@'localhost';
FLUSH PRIVILEGES;

Replace joomla6 with your Joomla database name.

This grants:

• ✅ SELECT access
• ❌ NO INSERT, UPDATE, DELETE, DROP, or ALTER access

---

Step 4: Verify the permissions #

SHOW GRANTS FOR 'joe_can_read'@'localhost';

Expected output:

GRANT USAGE ON *.* TO 'joe_can_read'@'localhost'
GRANT SELECT ON `joomla6`.* TO 'joe_can_read'@'localhost'

If you see anything else, review the privileges.

---

Step 5: Add credentials to Joomla configuration.php #

Open your Joomla configuration.php file and add the following lines:

// Custom Tables – READ ONLY DB user
public $ct_readonly_db_user = 'joe_can_read';
public $ct_readonly_db_pass = 'strong-password';
public $ct_readonly_db_name = 'joomla6';
public $ct_readonly_db_host = 'localhost';

Save the file.

For security reasons, database passwords are not stored in Custom Tables settings.

---

Step 6: Enable the SQL SELECT Query tag #

1. Go to Plugins → Custom Tables
2. Enable {{ tables.sqlselect }} Tag
3. Save settings

The tag is now ready to use.

---

Example usage in a layout #

{{ tables.sqlquery('SELECT COUNT(id) FROM #__users WHERE block = 0') }}

---

Security notes #

• Always use a dedicated read-only database user
• Do not reuse your Joomla main DB credentials
• Restrict the user to localhost
• Only SELECT queries are supported

---

Troubleshooting #

Error: “Read-only DB user not configured”
→ Check that credentials are correctly defined in configuration.php.

Error: “Access denied for user”
→ Ensure the database name and host match exactly.

---

This setup ensures maximum security while allowing advanced SQL SELECT functionality in Custom Tables.