How can Parameterized Queries in PHP Protect against SQL Injection in MySQL Databases?

Parameterized Queries in PHP for Secure MySQL Connections

SQL injection remains a persistent security threat that can compromise sensitive data in database applications. Parameterized queries are a crucial technique for mitigating this risk. However, implementing them correctly requires understanding not only the query itself but also the database connection process.

Consider the following code snippet from a PHP login page:

<code class="php">$query = "SELECT * FROM users WHERE username = '$userName' AND password = '$userPass'";
$result = mysqli_query($dbc, $query);
$row = mysqli_fetch_array($result);</code>

This code is vulnerable to SQL injection because the user-provided inputs $userName and $userPass are directly included in the query string.

To implement parameterized queries, replace this code with the following:

<code class="php">$stmt = mysqli_prepare($dbc, "SELECT * FROM users WHERE username = ? AND password = ?");
mysqli_stmt_bind_param($stmt, "ss", $userName, $userPass);
mysqli_stmt_execute($stmt);
$row = mysqli_stmt_fetch($stmt);</code>

In this improved code:

• mysqli_prepare prepares a PDO statement object that can be executed multiple times.
• mysqli_stmt_bind_param binds the user-provided inputs to parameterized placeholders (?) in the SQL query, preventing SQL injection.
• mysqli_stmt_execute executes the prepared statement.
• mysqli_stmt_fetch retrieves the result row.

Remember to encrypt or hash user passwords for added security. Parameterized queries along with these additional measures ensure secure MySQL database interactions in your PHP applications.

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