Complete Implementation and Security Practices for PHP Database Operations and Data Display

Introduction

In modern web development, the combination of PHP and MySQL is extremely common, but many developers often overlook security and robustness when implementing database operations. This article analyzes common issues based on a typical database operation case and provides a complete optimized solution.

Analysis of Original Code Issues

The original code contains several serious problems: First, it lacks validation and escaping of user input data, directly concatenating SQL statements which easily leads to SQL injection attacks. Second, the error handling mechanism is inadequate, failing to effectively capture and handle exceptions during database connection or query processes. Additionally, the code structure is messy, mixing print and echo statements, with a misplaced semicolon after the while loop condition causing syntax errors.

Complete Optimized Solution

Below is the comprehensively optimized code implementation:

<?php
# Initialize MySQL Connection
if(!($db = mysql_connect('localhost', 'root', ''))) {
    die('Database Server Connection Failed - #'.mysql_errno().': '.mysql_error());
}
if(!mysql_select_db('ram')) {
    die('Server Connected Successfully, but Database Connection Failed - #'.mysql_errno().': '.mysql_error());
}

# Prepare INSERT Query
$insertTPL = 'INSERT INTO `names` VALUES("%s", "%s", "%s", "%s")';
$insertSQL = sprintf($insertTPL,
               mysql_real_escape_string($name),
               mysql_real_escape_string($add1),
               mysql_real_escape_string($add2),
               mysql_real_escape_string($mail));

# Execute INSERT Query
if(!($insertRes = mysql_query($insertSQL))) {
    echo '<p>Data Insertion Failed - #'.mysql_errno().': '.mysql_error().'</p>';
} else {
    echo '<p>Person\'s Information Successfully Inserted</p>';
}

# Prepare SELECT Query
$selectSQL = 'SELECT * FROM `names`';

# Execute SELECT Query
if(!($selectRes = mysql_query($selectSQL))) {
    echo 'Data Retrieval Failed - #'.mysql_errno().': '.mysql_error();
} else {
?>
<table border="2">
  <thead>
    <tr>
      <th>Name</th>
      <th>Address Line 1</th>
      <th>Address Line 2</th>
      <th>Email Address</th>
    </tr>
  </thead>
  <tbody>
    <?php
    if(mysql_num_rows($selectRes) == 0) {
        echo '<tr><td colspan="4">No Rows Returned</td></tr>';
    } else {
        while($row = mysql_fetch_assoc($selectRes)) {
            echo "<tr><td>{$row['name']}</td><td>{$row['addr1']}</td><td>{$row['addr2']}</td><td>{$row['mail']}</td></tr>\n";
        }
    }
    ?>
  </tbody>
</table>
<?php
}
?>

Key Technical Points Analysis

SQL Injection Protection

Using the mysql_real_escape_string() function to escape all user input is a fundamental measure to prevent SQL injection attacks. This function escapes special characters to ensure they are not interpreted as part of SQL statements.

Comprehensive Error Handling

Error checking is added after each critical operation step: database connection, database selection, query execution, etc. Using mysql_errno() and mysql_error() to obtain detailed error information helps quickly locate and resolve issues.

Code Structure Optimization

Adopting a clear code organization structure separates HTML output from PHP logic. Using mysql_fetch_assoc() instead of mysql_fetch_row() allows accessing data through field names rather than indices, improving code readability and maintainability.

Data Validation and Empty Result Handling

After executing the SELECT query, use mysql_num_rows() to check the number of returned data rows. When empty, display a friendly prompt message to avoid showing empty tables that might confuse users.

Best Practice Recommendations

Although the above solution addresses the main issues in the original code, the following best practices should be considered in actual development: Use PDO or MySQLi extensions instead of the deprecated mysql extension; Add primary key constraints to database tables; Implement more comprehensive input validation mechanisms; Consider using prepared statements for enhanced security.

Conclusion

Through the analysis and optimization presented in this article, we demonstrate how to build secure and robust PHP database operation applications. The key is to always prioritize security, implement comprehensive error handling, and maintain clear code structure. These practices not only prevent security vulnerabilities but also improve application stability and maintainability.