How can Parameterized Queries in PHP with MySQL protect against SQL Injection Attacks?

Parameterized Queries for Secure PHP-MySQL Connections

SQL injection represents a severe security threat to web applications. By manipulating SQL queries, attackers can gain unauthorized access to sensitive data or disrupt database operations. Parameterized queries provide an effective countermeasure to mitigate this vulnerability.

Here's a snippet of code from a login page that illustrates how to prepare a parameterized query in PHP with MySQL:

<code class="php">$dbc = @mysqli_connect($dbhost, $dbuser, $dbpass, $db); // MySQL connection

$userName = $_POST["username"];
$userPass = $_POST["password"];

$stmt = mysqli_prepare($dbc, "SELECT * FROM users WHERE username = ? AND password = ?");
mysqli_stmt_bind_param($stmt, "s", $userName);
mysqli_stmt_bind_param($stmt, "s", $userPass);
mysqli_stmt_execute($stmt);
$row = mysqli_stmt_fetch($stmt);

if (!$row) {
    echo "No existing user or wrong password.";
}</code>

This code establishes a connection to the MySQL database using mysqli_connect and assigns the connection identifier to $dbc. The $userName and $userPass variables receive the username and password entered by the user on the login page.

The mysqli_prepare function initializes a prepared statement. The SQL query that will be executed is passed as the first argument. In this case, it retrieves all rows from the users table where both the username and password match the input parameters.

Next, mysqli_stmt_bind_param is used to bind the user-provided input parameters, $userName and $userPass, to the placeholders (?) in the prepared statement. The code specifies that both parameters are of data type s (string).

The mysqli_stmt_execute function executes the prepared statement.

Finally, mysqli_stmt_fetch retrieves the first row from the result set and assigns it to the $row variable.

By using parameterized queries, this code ensures that malicious SQL statements cannot be executed, thereby protecting against SQL injection attacks.

For added security, it is highly recommended to encrypt or hash the user's password before storing it in the database.

The above is the detailed content of How can Parameterized Queries in PHP with MySQL protect against SQL Injection Attacks?. For more information, please follow other related articles on the PHP Chinese website!