In the provided PHP code snippets, what improvements can be made to ensure proper functionality and security in database operations?

Issue: The provided PHP code snippets are vulnerable to SQL injection attacks as they directly concatenate user input into SQL queries. To ensure proper functionality and security in database operations, it is recommended to use prepared statements with parameterized queries. 

// Fix: Using prepared statements with parameterized queries to prevent SQL injection

// Establish a database connection
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Prepare a SQL query with a parameter placeholder
$stmt = $conn->prepare("SELECT * FROM users WHERE username = ?");
$stmt->bind_param("s", $username);

// Set the parameter values and execute the query
$username = $_POST['username'];
$stmt->execute();

// Process the results
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // Process each row
}

// Close the statement and the connection
$stmt->close();
$conn->close();

Keywords

SQL injection prepared statements PDO error handling data validation

Related Questions