How can PHP developers ensure secure database connections when executing variable SQL queries?

To ensure secure database connections when executing variable SQL queries in PHP, developers should use prepared statements with parameterized queries. This approach helps prevent SQL injection attacks by separating SQL logic from user input data. By binding parameters to placeholders in the query, the database engine can distinguish between code and data, reducing the risk of malicious input affecting the query execution. 

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

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

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

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

// Set user input data
$username = $_POST['username'];

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

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

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

Keywords

SQL injection PDO prepared statements mysqli_real_escape_string secure coding practices

Related Questions