Why is this built-in Database Tool in n-gen.net CMS?

The SQL Inspector tool provides n-gen.net CMS Owners (User Level 4 admins) with a convenient way to:

  • Inspect database content - View and analyze table data directly,
  • Execute custom SQL queries - Run SELECT, INSERT, UPDATE, DELETE operations,
  • Edit records - Modify database content through an intuitive interface,
  • Debug issues - Quickly identify and resolve database-related problems,
  • Perform maintenance - Update, delete, or clean up data as needed.

While third-party tools like MySQL Workbench or phpMyAdmin offer more advanced features, the SQL Inspector provides integrated, immediate access without requiring external tools or additional authentication.



Note:
All the operations that can be performed by the DB tool can also be performed with 3rd party database tools with more advanced features, i.e. MySql Workbench or phpMyAdmin.

Database n-gineer's SQL Inspector Tool

Overview

The n-gineer's SQL Inspector is a powerful built-in database management tool for the n-gen.net CMS that provides administrators with direct SQL access to their database. This tool serves as a surgical instrument for database inspection, query execution, and record management - all through a user-friendly web interface.

⚠️ WARNING: This is the sharpest tool in the n-gen.net CMS drawer. It provides direct database access with no safety net. You can make irreversible changes to your system. Only proceed if you understand the SQL queries you are about to execute.

Features

1. Visual Query Builder

  1. Drag-and-drop simplicity - Build SQL queries without writing code,
  2. Support for all operations - SELECT, INSERT, UPDATE, DELETE, DESCRIBE
  3. Column selection - Choose specific columns or use * for all
  4. WHERE clause builder - Add multiple conditions with AND/OR logic
  5. Real-time SQL preview - See the generated query before execution

2. Advanced SQL Mode

  1. Custom query execution - Write and execute any supported SQL statement
  2. Syntax highlighting - Color-coded SQL for better readability
  3. Query history - Save and re-execute recent queries (last 20)
  4. Editable queries - Modify queries before execution

3. Results Management

  1. Tabular results - Clean, paginated display of query results
  2. Inline record editing - Click any cell to modify its value
  3. Record deletion - Remove unwanted records with confirmation
  4. Export functionality - Download results as CSV or JSON
  5. Column sorting - Click column headers to sort results
  6. Result pagination - Navigate through large result sets

4. Record Editor

  1. Attribute selection - Choose which field to edit
  2. Raw HTML editing - View and edit HTML source code directly
  3. Large content support - Edit long text fields with scrollable textarea
  4. Confirmation dialog - Review changes before committing

5. Security Features

  1. Authentication required - Only level 4 (Site Owner) users can access
  2. CSRF protection - Prevents cross-site request forgery attacks
  3. SQL injection prevention - Parameterized queries where possible
  4. Operation confirmation - Confirm destructive operations (DELETE, UPDATE)
  5. Audit logging - All operations logged with user context
  6. Session validation - IP and user-agent validation
  7. Session timeout - Automatic logout after 30 minutes of inactivity

6. User Experience

  1. Three-column layout - Navigation, main content, and tools
  2. Responsive design - Works on desktop, tablet, and mobile devices
  3. Status indicators - Clear success/error/warning messages
  4. Table statistics - View row count, size, and last modified date
  5. Query history - Quick access to recent queries
  6. Intuitive navigation - Clear flow from selection to execution to results


Technical Details

Supported Operations

Operation Description Example
SELECT Retrieve data from tables SELECT * FROM users
INSERT Add new records INSERT INTO users (name) VALUES ('John')
UPDATE Modify existing records UPDATE users SET name='Jane' WHERE id=1
DELETE Remove records DELETE FROM users WHERE id=1
DESCRIBE View table structure DESCRIBE users
SHOW View database information SHOW TABLES

Query Building Process

  1. Select Table - Choose a table from the dropdown

  2. Choose Action - SELECT, INSERT, UPDATE, DELETE, or DESCRIBE

  3. Configure Query - Select columns, set WHERE conditions, provide values

  4. Build Query - Generate the SQL statement

  5. Review & Execute - Edit the SQL if needed, then execute

  6. View Results - Review the data or confirmation message

WHERE Clause Builder

The visual WHERE clause builder supports:

  • Operators: =, !=, >, >=, <, <=, LIKE, IN

  • Logic: AND, OR between conditions

  • Multiple conditions: Add unlimited WHERE conditions

  • Automatic formatting: Properly escapes values for SQL safety

Record Editing Flow

  1. Click EDIT on any row in the results grid

  2. Select attribute from the dropdown (all non-primary key columns)

  3. View current value - Displays the raw database value

  4. Edit content - Modify the value in the textarea

  5. Save record - Click the save icon to confirm

  6. Review changes - Confirmation dialog shows old vs. new values

  7. Commit or cancel - Confirm or abort the update

Raw HTML Source Display

When editing HTML content, the tool displays the raw HTML source code, not the interpreted HTML. This allows administrators to:

  • See exactly what is stored in the database

  • Edit HTML markup directly

  • Preserve formatting and special characters

  • Work with complex HTML structures

Table Statistics

When a table is selected, the tool displays:

  • Rows - Total number of records in the table

  • Size - Total data size (data + indexes)

  • Modified - Last modification timestamp

This information helps administrators understand table size and activity patterns.

Query History

The tool maintains a history of the last 20 queries executed in the current session:

  • Timestamp - When the query was executed

  • Query preview - First 60 characters of the SQL

  • Row count - Number of rows affected/returned

  • Quick re-execution - Select from dropdown to re-run

Security Considerations

Access Control

  • Level 4 required - Only Site Owner level users can access

  • Authentication - Must be logged in with valid credentials

  • Session validation - IP and user-agent verification

  • Timeout - Automatic logout after 30 minutes of inactivity

Data Protection

  • SQL injection prevention - Parameterized queries for all dynamic values

  • CSRF tokens - Protects against cross-site request forgery

  • Validation - Table and column names validated against schema

  • Request validation - Prevents malicious HTML/script injection

Audit Trail

All operations are logged to /App_Data/pro_db.log with:

  • Timestamp - When the operation occurred

  • User - Who performed the operation

  • IP address - Where the request originated

  • Operation type - SELECT, INSERT, UPDATE, DELETE, etc.

  • SQL query - The exact query executed (with sensitive data masked)

  • Rows affected - Number of rows impacted

Dangerous Operation Protection

  • DELETE and UPDATE without WHERE - Blocked to prevent mass data loss

  • Confirmation dialogs - Required for all DELETE and UPDATE operations

  • DROP TABLE protection - Table prefix validation prevents dropping system tables

  • Rollback prevention warning - Clear warnings about irreversible operations

What the Tool Does Not Do

  1. Modify table structure or access control - No visual biulder for Data Definition Language, DDL, (ALTER TABLE, CREATE TABLE, or DROP TABLE) operations or Data Control Language, DCL (GRANT, REVOKE, etc.)

  2. Change column types - Data types remain as defined in the schema

  3. Execute arbitrary commands - Limited to SQL SELECT, INSERT, UPDATE, DELETE, DESCRIBE, SHOW

  4. Access binary data - BLOB columns display as raw data, not interpreted content

  5. >Perform backups - Separate tools exist for database backup operations

  6. Automated operations - No scheduled or batch operations

Troubleshooting

Error: "Unable to connect to any of the specified MySQL hosts"

  • Cause: MySQL server not running or connection string misconfigured

  • Solution: Check MySQL service status and verify connection string in web.config

Error: "A potentially dangerous Request.Form value was detected"

  • Cause: Attempting to save HTML content in a text field

  • Solution: The tool uses ValidateRequest="false" to allow HTML content

Error: "Specified key was too long"

  • Cause: Index key length exceeds MySQL limits

  • Solution: Use the visual query builder or modify the query to select fewer columns

Error: "Table doesn't exist"

  • Cause: Table name typed incorrectly or table does not exist

  • Solution: Select table from the dropdown instead of typing manually

Record not saving

  • Cause: HTML content may trigger validation, or the UPDATE query may be blocked

  • Solution: Ensure you've acknowledged the confirmation dialog and the content is valid HTML

Best Practices

  1. Always backup before destructive operations - Database backups are essential

  2. Use WHERE clauses - Never execute DELETE or UPDATE without WHERE conditions

  3. Test with SELECT first - Verify the query returns the expected records before DELETE/UPDATE

  4. Review queries before execution - Check the SQL in the execution view

  5. Use the visual builder - Reduces the risk of syntax errors

  6. Monitor the audit log - Review /App_Data/pro_db.log regularly

  7. Keep session active - Refresh the page periodically to maintain authentication

  8. Export when needed - Use CSV/JSON export for data analysis.
    For backup of tables or entire database use:

    • Database Import & Export Tool,

    • Complete Database Backup Tool.

Tips & Trick

Always Remember:
With great power comes great responsibility. This tool provides direct access to your database.
You should always verify your queries before execution and maintain regular backups of your data.

Working with HTML Content

  • HTML content appears as raw source code in the editor

  • The tool displays exact database content, no interpretation

  • Editing HTML directly to modify page content is not adviced!
    Use the other n-gen.net CMS editing tools (Page Management, Header Management, etc)

  • Special characters and entities are preserved

Quick Navigation

  • Use the Query History dropdown to re-run recent queries

  • Click the Back button to return to the query builder

  • Use the Home icon to start a fresh query

Performance Tips

  • Add LIMIT clauses to SELECT queries for large tables

  • Use specific columns instead of * for better performance

  • Index columns used in WHERE conditions

  • Export large result sets instead of viewing in the browser

Remark

Note: The tool is only directed towards the MySql database family (MySql, Percona and MariaDB) and will not run on a Microsoft SQL server installation.

This tool is part of the n-gen.net CMS admin suite, version 1.3 or higher.

Last updated 28-06-2026 13:41:57