How Do Parameterized Queries Protect Against SQL Injection?

Using Parameterized Queries for Secure Database Interaction

SQL injection remains a significant security threat, allowing attackers to execute malicious queries using user-submitted input. To safeguard your website, parameterized queries offer a robust solution. However, integrating them requires proper database connection handling.

Consider this example login page code:

<code class="php">$userName = $_POST["username"];
$userPass = $_POST["password"];

$query = "SELECT * FROM users WHERE username = '$userName' AND password = '$userPass'";

$result = mysqli_query($dbc, $query); //$dbc is for MySQL connection: $dbc = @mysqli_connect($dbhost, $dbuser, $dbpass, $db)

$row = mysqli_fetch_array($result);

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

Implementing Parameterized Queries:

To prevent SQL injection, replace this code with:

<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>

• mysqli_prepare(): Prepares a parameterized query statement.
• mysqli_stmt_bind_param(): Binds input values to placeholders (?).
• mysqli_stmt_execute(): Executes the prepared query.
• mysqli_stmt_fetch(): Retrieves the result row.

Connection Handling:

In the original code, the connection to the database is established using mysqli_connect(). This needs to be done before executing any queries. Ensure you have a valid $dbc before proceeding.

Security Best Practices:

• Hash or encrypt passwords before storing them in the database.
• Validate user input to prevent malicious characters.
• Use prepared statements consistently for all database interactions involving user-provided data.

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