How can the SQL query in the PHP script be improved to avoid potential security vulnerabilities?

To avoid potential security vulnerabilities in the SQL query within the PHP script, it is essential to use parameterized queries or prepared statements. This approach helps prevent SQL injection attacks by separating SQL code from user input data. By binding parameters to the query, the database engine can distinguish between code and data, reducing the risk of malicious input manipulation. 

// Improved PHP script with parameterized query to prevent SQL injection
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

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

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

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

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

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

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

Keywords

SQL injection prepared statements PDO mysqli_real_escape_string security vulnerabilities

Related Questions