php 使用 SQL 時，引號要避開，否則會出錯！mysqli_real_escape_string()

 

<?php
$mysqli = new mysqli("localhost","my_user","my_password","my_db");

if ($mysqli -> connect_errno) {
  echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
  exit();
}

// Escape special characters, if any
$firstname = $mysqli -> real_escape_string($_POST['firstname']);
$lastname = $mysqli -> real_escape_string($_POST['lastname']);
$age = $mysqli -> real_escape_string($_POST['age']);

$sql="INSERT INTO Persons (FirstName, LastName, Age) VALUES ('$firstname', '$lastname', '$age')";

if (!$mysqli -> query($sql)) {
  printf("%d Row inserted.\n", $mysqli->affected_rows);
}

$mysqli -> close();
?>

像下面這個例子： $lastname 裡面的值有單引號。這樣進入 SQL 就會發生錯誤，應該要避開。

就使用這個函數：mysqli_real_escape_string()，就可以把單引號等避開了！

<?php

$lastname = "D'Ore";

$sql="INSERT INTO Persons (LastName) VALUES ('$lastname')";

// This query will fail, cause we didn't escape $lastname
if (!$mysqli -> query($sql)) {
  printf("%d Row inserted.\n", $mysqli->affected_rows);
}

?>

https://www.w3schools.com/php/func_mysqli_real_escape_string.asp

https://www.php.net/manual/en/mysqli.real-escape-string.php