Fix SQL Query Syntax Incorrect in the PHP Script?

This code provides a secure login system using mysqli with prepared statements to prevent SQL injection. It checks user credentials against a database and returns success or failure messages based on the result. It also includes improved error handling and database connection management for modern PHP environments.

PHP Code (with Errors):

code<?php
require "conn.php";

// Hardcoded username and password (for demo purposes)
$user_name = "un";
$user_pass = "123";

// Correct SQL query syntax (added '=' for password comparison)
$mysql_qry = "SELECT * FROM user WHERE username LIKE '$user_name' AND main_password = '$user_pass';";

// Execute the query and check for connection issues
$result = mysql_query($mysql_qry, $conn);

if ($result && mysql_num_rows($result) > 0) {
    echo "Login Success!";
} else {
    echo "Invalid Username or Password!";
}
?>

Resolving Coding Errors in PHP for Better Security

PHP Error Resolution: Handling Deprecated Functions

In the PHP code provided, the mysql_query() and mysql_num_rows() functions are used to interact with the database. However, these functions have been deprecated since PHP 5.5.0 and completely removed in PHP 7.0.0. It is highly recommended to switch to mysqli or PDO for improved security and performance.

Using deprecated functions not only causes compatibility issues but also leaves the code vulnerable to security risks such as SQL injection. In this code, the user input ($user_name and $user_pass) is directly injected into the SQL query without any form of sanitization or prepared statements, which exposes the application to potential attacks.

Corrected Code:

code<?php
require "conn.php"; // Ensure conn.php connects to the database using mysqli

// Hardcoded username and password (for demo purposes)
$user_name = "un";
$user_pass = "123";

// Create the SQL query (replaced 'like' with '=' for exact matching)
$mysql_qry = "SELECT * FROM user WHERE username = ? AND main_password = ?";

// Prepare the SQL statement
if ($stmt = $conn->prepare($mysql_qry)) {
    // Bind parameters (s = string)
    $stmt->bind_param("ss", $user_name, $user_pass);

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

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

    // Check if any rows were returned (i.e., login success)
    if ($result->num_rows > 0) {
        echo "Login Success!";
    } else {
        echo "Invalid Username or Password!";
    }

    // Close the statement
    $stmt->close();
} else {
    echo "Error: Failed to prepare SQL statement!";
}

// Close the database connection
$conn->close();
?>

Key Changes:

1. Use of mysqli_*: Since mysql_* functions are deprecated, the code is now using mysqli_* functions to interact with the database.
2. Prepared Statements: Instead of directly injecting user input into the SQL query, prepared statements are used to prevent SQL injection attacks.
3. Exact Matching: The SQL query now uses = for exact matching instead of LIKE, which is more appropriate when comparing usernames and passwords.
4. Error Handling: Added basic error handling to check if the SQL query preparation was successful.

Improve Security Further:

• Password Hashing: Never store passwords in plaintext. Use password hashing (e.g., password_hash() and password_verify() in PHP) to securely store and verify passwords.
• Input Validation: Validate and sanitize user inputs to further enhance security.