在 PHP 中將特殊字元插入資料庫
本教程將介紹 mysqli_real_escape_string() 函式以在 PHP 中將特殊字元插入資料庫。我們將比較使用和不使用 mysqli_real_escape_string() 函式的插入資料。
建立 HTML 表單並設定資料庫連線以收集資料
首先，必須建立一個 HTML 表單供使用者輸入資料，並且必須設定一個資料庫連線來傳送命令和接收輸入。
以下是我們將在本教程中使用的程式碼。
HTML 程式碼：
<!DOCTYPE html>
<head>
<title>
Real Escape Strings
</title>
</head>
<body>
<form action = 'login.php' method = 'post' >
<div align="ccenter">
<label for="username"><b>User Name</b> </label>
<input type="text" placeholder="Your Name" name="username" required>
</br>
<label for="password"><b>User Password</b></label>
<input type="password" placeholder="Your Password" name="userpassword" required>
</br>
<label for="userlocation"><b>User Location</b> </label>
<input type="text" placeholder="Your Location" name="userlocation" required>
</br> </br> </br>
<input type="submit" name="1" value = "Insert without mysqli_real_escape_string " />
</br>
</br>
<input type="submit" name="2" value = "Insert with mysqli_real_escape_string " />
</div>
</form>
</body>
</html>
資料庫連線程式碼：
<?php
error_reporting(0);
define("server", "localhost");
define("user", "root");
define("password", "");
define("database", "demo");
$mysqli = mysqli_connect(server , user, password, database);
if ($mysqli -> connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
exit();
}
//db connection code ends
?>
我們獲得以下基礎知識：
- 表單輸入欄位
- 資料庫名稱：
demo - 表名：
user_login
在 PHP 中使用 mysqli_real_escape_string() 將特殊字元插入資料庫
要從表單欄位中獲取帶有特殊字元的使用者輸入，我們使用 mysqli_real_escape_string() 函式。
我們需要以下引數：資料庫連線和我們想要轉義的字串。我們的資料庫連線是 $mysqli，我們要轉義的字串是 $_POST['username']。
程式樣式語法：
<?php
//Note: This is a demo syntax, you can run the next PHP code blocks for output
$username= $mysqli_real_escape_string($mysqli, $_POST['username']);
?>
例子：
<?php
//Second example: insert user data using mysqli real escape string
//ensure that users do not send empty data, see the following condition
if(isset($_POST['2']) && !empty($_POST['username']) && !empty ($_POST['userpassword']) && !empty ($_POST['userlocation']))
{
//You apply real_escape_string on $_POST[''] form fields (user input)
$username = mysqli_real_escape_string($mysqli, $_POST['username']);
$userpassword = mysqli_real_escape_string($mysqli, $_POST['userpassword']);
$userlocation = mysqli_real_escape_string($mysqli, $_POST['userlocation']);
//same query (the values are dynamic, we escaped the strings)
$query="INSERT INTO user_login (username, userpassword, userlocation) VALUES ('$username', '$userpassword', '$userlocation')";
if (!mysqli_query($mysqli, $query)) {
echo "Failed!";
}
else{
//success
echo "Data inserted";
}
//close connection
$mysqli -> close();
}// ends first condition
輸出：
mysqli_real_escape_string() 函式消除了特殊字元，從而提高了資料庫的安全性。
它會生成用於 SQL 查詢的有效 SQL 表示式。另外，除了 prepared statements 之外，避免 SQL 注入也是一種比較安全的方法。
參考下面的另一個示例，它不使用 mysqli_real_escape_string() 函式。
<?php
//example-1: insert data without mysql real escape
if(isset($_POST['1'])){
$username = $_POST['username'];
$userpassword = $_POST['userpassword'];
$userlocation= $_POST['userlocation'];
$query="INSERT INTO user_login (username, userpassword, userlocation) VALUES
('$username', '$userpassword', '$userlocation')";
if (!$mysqli -> query($query)){
echo "Query failed to execute because of special characters!";
}
else{
echo "User Data Inserted!";
}
//first example ends here
}
?>
輸出：
如我們所見，由於使用者輸入中的特殊字元，它無法執行和收集資料。
Sarwan Soomro is a freelance software engineer and an expert technical writer who loves writing and coding. He has 5 years of web development and 3 years of professional writing experience, and an MSs in computer science. In addition, he has numerous professional qualifications in the cloud, database, desktop, and online technologies. And has developed multi-technology programming guides for beginners and published many tech articles.
LinkedIn