How to Update Data Securely in MySQL using Prepared Statements?

Using Prepared Statements for Update Queries

When executing update queries with PHP and MySQL, it is recommended to use prepared statements to ensure data integrity and prevent SQL injection. In a recent discussion on MySQL's UPDATE statement, a user sought guidance on how to utilize prepared statements.

Solution

In MySQL, prepared statements follow a similar format to INSERT or SELECT statements. To use a prepared statement for an UPDATE query, simply replace all variable values with placeholders represented by the question mark (?):

<code class="php">$sql = "UPDATE Applicant SET phone_number=?, street_name=?, city=?, county=?, zip_code=?, day_date=?, month_date=?, year_date=? WHERE account_id=?";</code>

Once the SQL statement is prepared, create a prepared statement object:

<code class="php">$stmt = $db_usag->prepare($sql);</code>

Next, bind parameters to the prepared statement. The following example assumes that the date and account_id parameters are integers (d), while the remaining parameters are strings (s):

<code class="php">$stmt->bind_param('sssssdddd', $phone_number, $street_name, $city, $county, $zip_code, $day_date, $month_date, $year_date, $account_id);</code>

Execute the prepared statement:

<code class="php">$stmt->execute();</code>

Handle any errors if they occur:

<code class="php">if ($stmt->error) {
  echo "FAILURE!!! " . $stmt->error;
}
else echo "Updated {$stmt->affected_rows} rows";</code>

Finally, close the prepared statement:

<code class="php">$stmt->close();</code>

The above is the detailed content of How to Update Data Securely in MySQL using Prepared Statements?. For more information, please follow other related articles on the PHP Chinese website!