What are the potential pitfalls of using the UNION command in MySQL queries?

One potential pitfall of using the UNION command in MySQL queries is the risk of SQL injection attacks if user input is not properly sanitized. To prevent this, it is important to use parameterized queries or prepared statements to safely handle user input. 

// Example of using prepared statements to prevent SQL injection when using UNION command in MySQL queries

// Assuming $mysqli is a mysqli object connected to the database

// User input
$user_input = $_POST['user_input'];

// Prepare a statement with a placeholder for user input
$stmt = $mysqli->prepare("SELECT column_name FROM table1 WHERE column_name = ? UNION SELECT column_name FROM table2 WHERE column_name = ?");

// Bind the user input to the placeholder
$stmt->bind_param("ss", $user_input, $user_input);

// Execute the statement
$stmt->execute();

// Get the results
$result = $stmt->get_result();

// Fetch and display the data
while ($row = $result->fetch_assoc()) {
    echo $row['column_name'] . "<br>";
}

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

Keywords

UNION command MySQL queries SQL injection data type mismatch performance degradation

Related Questions