在 PHP 中将特殊字符插入数据库

Sarwan Soomro 2023年1月30日 2022年5月13日
  1. 创建 HTML 表单并设置数据库连接以收集数据
  2. 在 PHP 中使用 mysqli_real_escape_string() 将特殊字符插入数据库
在 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>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</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>&nbsp;</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

输出:

PHP:使用 mysqli_real_escape_string 插入数据

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

输出:

PHP:在没有 mysqli_real_escape_string 的情况下插入数据

如我们所见,由于用户输入中的特殊字符,它无法执行和收集数据。

Sarwan Soomro avatar Sarwan Soomro avatar

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

相关文章 - PHP MySQL