PHP语法错误排查及员工请假数据权限控制问题求助

Let's break down and solve your two problems step by step:

1. Fixing the Syntax Error #

Your SQL query has two critical issues causing the Parse error: syntax error, unexpected '"':

• Duplicate WHERE clauses and an extra, misplaced ON keyword that breaks the query structure.
• Directly embedding $_SESSION["VALID_USER_ID"] in a double-quoted string creates quote conflicts, plus it exposes your code to SQL injection attacks.

Corrected Query with Prepared Statements (Safe & Valid) #

Use a prepared statement with placeholders to fix both the syntax error and secure your code:

// Fix SQL structure and use a placeholder for the session value
$result = $database->prepare("
    SELECT leaves.*, employee.full_name, employee.phone, employee.email 
    FROM leaves 
    INNER JOIN employee ON employee.id = leaves.user_id 
    WHERE employee.username = ?
");
// Bind the session value to the placeholder (prevents injection & syntax issues)
$result->execute([$_SESSION["VALID_USER_ID"]]);

Why This Works: #

• Removed redundant WHERE/ON clauses—we only need one ON for the join and one WHERE to filter by the logged-in user.
• The ? placeholder avoids double-quote conflicts, and binding the session value keeps your query safe from injection.
• Added employee.full_name, employee.phone, employee.email to the SELECT—your original code tried to echo these fields but didn’t fetch them, which would have caused undefined index warnings.

2. Restricting Access to Only the Logged-In User's Records #

The original query’s messy join/filter logic let users see others’ records. The corrected query fixes this by:

• Properly linking leaves and employee tables via employee.id = leaves.user_id (connecting each leave record to its submitter).
• Filtering results to only include entries where employee.username matches the logged-in user’s username from the session.

Full Corrected Code #

Here’s your complete code with all fixes and minor improvements:

<div class="container">
    <div class="page-header">
        <h3>My Leaves</h3>
        <div class="table-responsive">
            <table class="table">
                <tr>
                    <th>Employee Name</th>
                    <th>Phone</th>
                    <th>Email</th>
                    <th>From</th>
                    <th>To</th>
                    <th>Reason</th>
                    <th>Status</th>
                </tr>
                <?php
                include ('database.php');
                // Start session if not already started (critical for accessing $_SESSION)
                if (!session_id()) session_start();
                
                // Corrected prepared statement
                $result = $database->prepare("
                    SELECT leaves.*, employee.full_name, employee.phone, employee.email 
                    FROM leaves 
                    INNER JOIN employee ON employee.id = leaves.user_id 
                    WHERE employee.username = ?
                ");
                $result->execute([$_SESSION["VALID_USER_ID"]]);
                
                // Use while loop for cleaner row fetching
                while ($row_message = $result->fetch()) {
                ?>
                <tr>
                    <td><?php echo htmlspecialchars($row_message['full_name']); ?></td>
                    <td><?php echo htmlspecialchars($row_message['phone']); ?></td>
                    <td><?php echo htmlspecialchars($row_message['email']); ?></td>
                    <td><?php echo htmlspecialchars($row_message['fromdate']); ?></td>
                    <td><?php echo htmlspecialchars($row_message['todate']); ?></td>
                    <td><?php echo htmlspecialchars($row_message['reason']); ?></td>
                    <td><?php echo htmlspecialchars($row_message['status']); ?></td>
                </tr>
                <?php } ?>
            </table>
            <a href="home"><button type="button" class="btn btn-primary"><i class="glyphicon glyphicon-arrow-left"></i> Back</button></a>
        </div>
    </div>
</div>

Extra Improvements: #

• Added session_start() to ensure the session is active (required to access $_SESSION values).
• Switched to a while loop for cleaner row fetching (more idiomatic for database results).
• Used htmlspecialchars() around all echoed values to prevent XSS attacks by sanitizing user-generated content.

Quick Validation Checks #

1. Confirm $_SESSION["VALID_USER_ID"] holds the correct username for the logged-in user (debug with var_dump($_SESSION); if needed).
2. Ensure your database.php file correctly initializes the PDO/mysqli connection with prepared statement support.

内容的提问来源于stack exchange，提问作者user9236271