What are some common pitfalls when using PHP to interact with a MySQL database?

One common pitfall when using PHP to interact with a MySQL database is not properly escaping user input, which can lead to SQL injection attacks. To prevent this, always use prepared statements or parameterized queries to bind variables to your SQL queries instead of directly inserting user input. 

// Example of using prepared statements to prevent SQL injection
$mysqli = new mysqli("localhost", "username", "password", "database");

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

// Prepare a SQL query using a placeholder for user input
$stmt = $mysqli->prepare("SELECT * FROM users WHERE username = ?");
$stmt->bind_param("s", $username);

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

// Get the results
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // Do something with the data
}

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

Keywords

SQL injection PDO mysqli error handling data validation

Related Questions