# Run SQL File Controller Documentation

**File**: `/controllers/runsqlfile.php`  
**Purpose**: Executes SQL commands and scripts for database administration and maintenance tasks  
**Last Updated**: December 21, 2024  
**Total Functions**: 1  
**Lines of Code**: ~110

---

## 📋 Overview

The Run SQL File Controller provides database administration capabilities by allowing direct execution of SQL commands and scripts. It serves as a utility controller for:
- Direct SQL command execution
- Database maintenance scripts
- Schema updates and modifications
- Data import/export operations
- Administrative database tasks
- SQL file processing and validation
- Comment filtering and cleanup
- Multi-statement execution handling

### Primary Functions
- [x] Execute raw SQL commands directly on database
- [x] Process SQL files with multiple statements
- [x] Filter out SQL comments automatically
- [x] Handle multi-line SQL scripts
- [x] Provide command execution feedback
- [x] Support administrative database operations
- [x] Safe SQL parsing and validation
- [x] Error handling for SQL execution

### Related Controllers
- [Database Maintenance Scripts](#) - Schema updates
- [Import/Export Controllers](#) - Data migration
- [Administrative Tools](#) - System maintenance

---

## 🗄️ Database Tables

### No Direct Table Operations
This controller operates at the SQL execution level and can interact with any database table depending on the SQL commands provided.

### Potential Target Tables
| Category | Tables | Operations |
|----------|--------|-----------|
| **Schema Changes** | Any table | CREATE, ALTER, DROP statements |
| **Data Operations** | Any table | INSERT, UPDATE, DELETE, SELECT |
| **Index Management** | System tables | CREATE/DROP INDEX statements |
| **User Management** | User tables | GRANT, REVOKE permissions |
| **Maintenance** | All tables | OPTIMIZE, REPAIR, ANALYZE |

---

## 🔑 Key Functions

### 1. **Default Action** - SQL Upload Interface
**Location**: Lines 35-42  
**Purpose**: Display the SQL file upload and execution interface

**Process Flow**:
1. Check user authentication
2. Display header template
3. Show SQL upload form
4. Set script flag for interface
5. Display footer template

**Interface Configuration**:
```php
$smarty->display("runsqlfileview/uploadfiles.html");
$smarty->assign("runsqlfilescript", 1);
```

---

### 2. **run_sql_file()** - SQL Command Processor
**Location**: Lines 73-108  
**Purpose**: Parse and execute SQL commands from user input

**Function Signature**:
```php
function run_sql_file()
```

**Process Flow**:
1. Extract SQL commands from POST data
2. Remove comments and empty lines
3. Split commands by semicolon delimiter
4. Execute each command individually
5. Track success and failure counts
6. Return execution statistics

**Comment Removal Logic**:
```php
$lines = explode("\n", $commands);
$commands = '';
foreach ($lines as $line) {
    $line = trim($line);
    if ($line && !strpos($line, '--')) {
        $commands .= $line . "\n";
    }
}
```

**Command Splitting**:
```php
// Convert to array by semicolon delimiter
$commands = explode(";", $commands);
```

**Execution Loop**:
```php
$total = $success = 0;
foreach ($commands as $command) {
    if (trim($command)) {
        $storeExt->run($command);
    }
}
```

**Return Statistics**:
```php
return array(
    "success" => $success,
    "total" => $total
);
```

---

## 🔄 Workflows

### Workflow 1: SQL Command Execution
```
┌─────────────────────────────────────────────────────────────┐
│              START: Execute SQL Commands                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Input SQL Commands                                      │
│     - Enter SQL commands in text area                       │
│     - Support multi-line statements                         │
│     - Allow multiple commands separated by semicolons       │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Parse and Clean Commands                                │
│     - Split input into individual lines                     │
│     - Remove SQL comments (lines starting with --)          │
│     - Filter out empty lines                                │
│     - Rebuild clean command string                          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Split Into Individual Commands                          │
│     - Split by semicolon delimiter                          │
│     - Create array of individual SQL statements             │
│     - Trim whitespace from each command                     │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Execute Each Command                                    │
│     FOR EACH SQL command:                                   │
│       │                                                     │
│       ├─→ Check if command is not empty                    │
│       │                                                     │
│       ├─→ Execute command via database extension           │
│       │                                                     │
│       ├─→ Handle any execution errors                      │
│       │                                                     │
│       └─→ Track success/failure statistics                 │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Return Execution Results                                │
│     - Display success status                                │
│     - Show any error messages                               │
│     - Provide execution statistics                          │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) | Default action | Display SQL upload interface |
| `do=addfiles` | `run_sql_file()` | Execute SQL commands |
| `do=sucess` | Success page | Display success confirmation |
| `do=error` | Error page | Display error message |

### Required Parameters by Action

**SQL Execution** (`do=addfiles`):
- `sql` - SQL commands string (POST data)

### Input Format

**SQL Commands**:
```sql
-- Comments are automatically filtered out
CREATE TABLE test (id INT PRIMARY KEY);
INSERT INTO test (id) VALUES (1);
UPDATE test SET id = 2 WHERE id = 1;
DROP TABLE test;
```

**Multi-line Support**:
```sql
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (username, email) VALUES 
('admin', 'admin@example.com'),
('user1', 'user1@example.com');
```

---

## 🧮 Calculation Methods

### Comment Detection
```php
// Simple comment detection
if ($line && !strpos($line, '--')) {
    // Line is not a comment
}
```

### Command Counting
```php
// Statistics tracking (currently not implemented)
$total = $success = 0;
foreach ($commands as $command) {
    if (trim($command)) {
        $total++; // Should be incremented
        try {
            $storeExt->run($command);
            $success++; // Should be incremented on success
        } catch (Exception $e) {
            // Handle error
        }
    }
}
```

### Command Validation
```php
// Check for non-empty commands
if (trim($command)) {
    // Command has content, safe to execute
}
```

---

## 🔒 Security & Permissions

### ⚠️ **CRITICAL SECURITY WARNING** ⚠️

This controller presents **EXTREMELY HIGH** security risks:

1. **Direct SQL Execution**: Allows arbitrary SQL command execution
2. **No Input Validation**: No filtering of dangerous SQL commands
3. **Full Database Access**: Can access/modify any database table
4. **Data Loss Risk**: Can execute DROP, DELETE, TRUNCATE commands
5. **Privilege Escalation**: May allow unauthorized database operations

### Authentication Requirements
```php
include_once("../public/authentication.php");
```

### Missing Security Controls

**No Command Filtering**:
```php
// DANGEROUS: No validation of SQL commands
$commands = $_POST['sql']; // Direct use of user input
```

**No Operation Restrictions**:
```php
// MISSING: Should restrict dangerous operations
$allowedOperations = ['SELECT', 'INSERT', 'UPDATE'];
// Should validate against allowed operations
```

**No SQL Injection Protection**:
```php
// MISSING: Should sanitize input
// Should use parameterized queries where possible
```

### Recommended Security Improvements

**1. Command Whitelist**:
```php
function validateSQLCommand($command) {
    $dangerous = ['DROP', 'DELETE', 'TRUNCATE', 'ALTER', 'GRANT', 'REVOKE'];
    $cmd_upper = strtoupper(trim($command));
    
    foreach ($dangerous as $danger) {
        if (strpos($cmd_upper, $danger) === 0) {
            throw new Exception("Dangerous command not allowed: $danger");
        }
    }
}
```

**2. User Permission Checks**:
```php
// Should verify user has SQL execution permissions
if (!$_SESSION['user_can_execute_sql']) {
    die('Insufficient permissions');
}
```

**3. Audit Logging**:
```php
// Should log all SQL executions
logSQLExecution($_SESSION['userid'], $command, $result);
```

---

## 📊 Performance Considerations

### Database Impact
- **High Risk**: Direct database operations can impact performance
- **Transaction Control**: No automatic transaction wrapping
- **Resource Usage**: Large SQL files may consume significant resources

### Memory Usage
- **String Processing**: Large SQL content held in memory
- **Command Splitting**: Creates arrays of commands in memory

### Execution Time
- **No Timeout Control**: Long-running SQL may timeout
- **Sequential Execution**: Commands executed one by one

### Recommended Improvements
```php
// Add transaction control
$storeExt->beginTransaction();
try {
    foreach ($commands as $command) {
        $storeExt->run($command);
    }
    $storeExt->commit();
} catch (Exception $e) {
    $storeExt->rollback();
    throw $e;
}
```

---

## 🐛 Common Issues & Troubleshooting

### 1. **Incomplete Statistics**
**Issue**: Success/total counts not properly tracked  
**Cause**: Statistics variables not incremented

**Fix**:
```php
$total = $success = 0;
foreach ($commands as $command) {
    if (trim($command)) {
        $total++;
        try {
            $storeExt->run($command);
            $success++;
        } catch (Exception $e) {
            // Log error but continue
            error_log("SQL Error: " . $e->getMessage());
        }
    }
}
```

### 2. **Comment Detection Issues**
**Issue**: Comments not properly filtered  
**Cause**: Simple strpos() check insufficient

**Improved Detection**:
```php
foreach ($lines as $line) {
    $line = trim($line);
    // Better comment detection
    if ($line && !preg_match('/^\s*--/', $line) && $line !== '') {
        $commands .= $line . "\n";
    }
}
```

### 3. **SQL Parsing Problems**
**Issue**: Complex SQL statements broken by semicolon split  
**Cause**: Semicolons within strings or functions

**Better Parsing**:
```php
// Would need proper SQL parser for complex statements
// Current simple split may break on:
// INSERT INTO table VALUES ('data;with;semicolons');
```

### 4. **Error Handling Missing**
**Issue**: SQL errors not properly reported  
**Cause**: No try-catch around execution

**Improved Error Handling**:
```php
foreach ($commands as $command) {
    if (trim($command)) {
        try {
            $storeExt->run($command);
            $success++;
        } catch (Exception $e) {
            $errors[] = "Command failed: " . $command . " - " . $e->getMessage();
        }
        $total++;
    }
}
```

---

## 🧪 Testing Scenarios

### ⚠️ **DANGER: Test in Development Environment Only** ⚠️

### Test Case 1: Basic Command Execution
```sql
-- Test simple commands
SELECT COUNT(*) FROM user;
```

### Test Case 2: Comment Filtering
```sql
-- This is a comment
SELECT 1; -- This should work
-- SELECT 2; This should be filtered
```

### Test Case 3: Multi-Statement Execution
```sql
CREATE TEMPORARY TABLE test_temp (id INT);
INSERT INTO test_temp VALUES (1);
SELECT * FROM test_temp;
DROP TABLE test_temp;
```

### Test Case 4: Error Handling
```sql
-- This should cause an error
SELECT * FROM nonexistent_table;
SELECT 1; -- This should still execute
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [Database Security Guidelines](#) - Security best practices
- [SQL Injection Prevention](#) - Security measures
- [Database Administration](#) - Admin procedures

---

## ⚠️ **SECURITY RECOMMENDATIONS**

### Immediate Actions Required:
1. **Restrict Access**: Limit to super-admin users only
2. **Add Command Validation**: Implement SQL command whitelist
3. **Add Audit Logging**: Log all SQL executions
4. **Implement Safeguards**: Prevent dangerous operations
5. **Consider Removal**: Evaluate if this functionality is necessary

### Alternative Approaches:
- Use proper database migration tools
- Implement specific admin functions instead of raw SQL
- Use database admin tools (phpMyAdmin, Adminer) for maintenance

---

**Documented By**: AI Assistant  
**Review Status**: ✅ Complete ⚠️ **SECURITY CRITICAL**  
**Next Review**: Immediate security audit required