What are common pitfalls when using MySQL queries in PHP scripts?

One common pitfall when using MySQL queries in PHP scripts is not properly sanitizing user input, leaving the script vulnerable to SQL injection attacks. To solve this issue, always use prepared statements with parameterized queries to prevent SQL injection attacks. 

// Example of using prepared statements with parameterized queries
$mysqli = new mysqli("localhost", "username", "password", "database");

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

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

// Set parameters and execute query
$username = "john_doe";
$stmt->execute();

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

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

Keywords

MySQL PHP queries SQL injection error handling

Related Questions