这个问题在这里已有答案：
我在我的网站上创建了一个html表单，以便访问我网站的用户能够注册，表单有以下输入;用户名，性别，电子邮件和密码 .
我想知道如何使用mysqli预处理语句（为了保护我的网站免受sql注入）在使用用户IP地址将表单数据插入我的数据库中的多个列 .
下面是我从教程中编写（带有验证）的代码，我从这里阅读的答案中学到了这些代码 .
PHP CODE
<?php
// connect to db
include 'db.php';
// variables
$msg = $error = $error0 = $error1 = $error2 = "";
if($_SERVER["REQUEST_METHOD"] == "POST") {
// username validation
if (empty(trim($_POST["username"]))) {
$error= '<font color="#dc3545">Your username is required</font>';
} elseif (strlen(preg_replace('/[^a-zA-Z]/m', '', $_POST["username"])) < 3) {
$error= '<font color="#dc3545">Your username must have atleast 3 letters</font>';
} elseif (strlen(preg_replace('/[^a-zA-Z]/m', '', $_POST["username"])) > 15) {
$error= '<font color="#dc3545">Your username is too long</font>';
} elseif (!preg_match("/^[A-Za-z0-9_\.]+$/ ", $_POST["username"])) {
$error = '<font color="#dc3545">Your username must be in letters with either a number(0-9), underscore(_) or dot(.)</font>';
} else {
// prepare select statement
$check_uname = "SELECT id FROM users WHERE username = ?";
if($stmt = mysqli_prepare($db, $check_uname)) {
// Bind variables to the prepared statement as parameters
mysqli_stmt_bind_param($stmt, "s", $param_username);
// Set parameters
$param_username = trim($_POST["username"]);
// Attempt to execute the prepared statement
if(mysqli_stmt_execute($stmt)){
/* store result */
mysqli_stmt_store_result($stmt);
if(mysqli_stmt_num_rows($stmt) == 1){
$error = '<font color="#dc3545"><b>'.$_POST["username"].'</b> is already in use</font>';
} else{
$username = trim($_POST["username"]);
}
} else{
$error = '<font color="#dc3545">Oops! Something went wrong. Please try again later</font>';
}
}
// Close statement
mysqli_stmt_close($stmt);
}
// gender validation
if (empty($_POST['gender'])) {
$error0 = '<font color="#dc3545">Your gender is required</font>';
} else {
$gender = $_POST['gender'];
}
// email validation
if (empty(trim($_POST["email"]))) {
$error1 = '<font color="red">Your email address is required</font>';
} elseif (!filter_var($_POST["email"], FILTER_VALIDATE_EMAIL)) {
$error1 = '<font color="red">Your email address is invalid</font>';
} else {
// prepare select statement
$check_email = "SELECT * FROM users WHERE email = ?";
if($stmt = mysqli_prepare($db, $check_email)) {
// Bind variables to the prepared statement as parameters
mysqli_stmt_bind_param($stmt, "s", $param_email);
// Set parameters
$param_email = trim($_POST["email"]);
// Attempt to execute the prepared statement
if(mysqli_stmt_execute($stmt)){
/* store result */
mysqli_stmt_store_result($stmt);
if(mysqli_stmt_num_rows($stmt) == 1){
$error1 = '<font color="#dc3545"><b>'.$_POST["email"].'</b> is already in use</font>';
} else{
$email = trim($_POST["email"]);
}
} else{
$error1 = '<font color="#dc3545">Oops! Something went wrong. Please try again later</font>';
}
}
}
// password validation
if (empty(trim($_POST['password']))) {
$error2 = '<font color="#dc3545">Your password is required</font>';
} elseif (strlen(trim($_POST['password'])) < 6) {
$error2 = '<font color="#dc3545">Password must be at leats 6 characters</font>';
} elseif (strlen(trim($_POST['password'])) > 150) {
$error2 = '<font color="#dc3545">Your password is to long</font>';
} else {
$password = trim($_POST['password']);
}
// check errors before inserting into database
if (empty($error) && empty($error0) && empty($error1) && empty($error2)) {
// prepare insert statement
// user name, email, gender and password stored in table `users`
$insert = "INSERT INTO users(username, email, gender, password) VALUES(?, ?, ?, ?)";
// user name, email and gender stored in table `users_profile`, username will be set as default user 'display_name'
$insert = "INSERT INTO users_profile(username, gender, email) VALUES(?, ?, ?)";
// user name stored in table `users_lastlogin_dt` to save user last login date
$insert = "INSERT INTO users_lastlogin_dt(username) VALUES(?)";
// user name stored in table `users_lastlogin_ip` to save user last login ip address
$insert = "INSERT INTO users_lastlogin_ip(username) VALUES(?)";
// user name, signup date and time stored in table `users_signup_dt` to save user signup date and time
$insert = "INSERT INTO users_signup_dt(username, date_n_time) VALUES(?, NOW())";
// user name and signup ip address stored in table `users_signup_dt`
$insert = "INSERT INTO users_signup_ip(username, ip) VALUES(?, ?)";
// user name stored in table `users_verified`
$insert = "INSERT INTO users_verified(username) VALUES(?)";
// user name stored in table `users_mod_man_sec_ver_ser`
$insert = "INSERT INTO users_mod_man_sec_ver_ser(username) VALUES(?)";
if($stmt = mysqli_prepare($db, $insert)) {
// bind variables to the prepared statement as parameters
mysqli_stmt_bind_param($stmt, "ss", $param_username, $param_gender, $param_email, $param_password);
// set parameters
$param_username = $username; // user's name
$param_gender = $gender; // user's gender
$param_email = $email; // user's email
$param_password = password_hash($password, PASSWORD_DEFAULT); // creates a password hash
$ip = $_SERVER['REMOTE_ADDR']; // grab users ip address
// attempt to execute the prepared statement
if(mysqli_stmt_execute($stmt)){
$msg = '<div class="alert a_success alert-dismissable" id="alert"><a href="#" class="close" data-dismiss="alert" aria-label="close">×</a><b>Signup was successful</b></div>';
} else {
$msg = '<div class="a_danger alert-dismissable" id="alert"><a href="#" class="close" data-dismiss="alert" aria-label="close">×</a><b>Signup was unsuccessful, please try again</b></div>';
}
}
// close statement
mysqli_stmt_close($stmt);
}
// close connection
mysqli_close($db);
}
// echo success message or failed message
echo $msg;
?>
HTML CODE:
<div class="container">
<br>
<div class="row">
<center><h3><b>Create a new account</b></h3></center><br>
<div class="form_box">
<form id="canabody" method="POST" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"])?>" enctype="multipart/form-data">
<div class="form-grou">
<b>Username</b>
<br>
<input class="form-control" id="uname" type="text" name="username" placeholder="3 - 15 characters" maxlength="15" value="<?php if(isset($username)) echo $username; ?>" autocomplete="off" required>
<?php echo $error; ?>
</div>
<div class="form-group">
<b>Gender</b>
<br>
<select class="form-control custom-select" name="gender" id="ugender" required>
<option></option>
<option value="male" <?php if(isset($gender) && $gender == 'male') echo 'selected'; ?>>Male</option>
<option value="female" <?php if(isset($gender) && $gender == 'female') echo 'selected'; ?>>Female</option>
</select>
<?php echo $error0; ?>
</div>
<div class="form-group">
<b>Email address</b>
<br>
<input class="form-control" id="usrem" type="email" name="email" value="<?php if(isset($email)) echo $email; ?>" placeholder="yourname@domain.com" required>
<?php echo $error1; ?>
</div>
<div class="form-group">
<b>Password</b>
<br>
<input class="form-control" id="usrpass" type="password" name="password" maxlength="150" value="<?php if(isset($password)) echo $password; ?>" placeholder="6 - 150 characters" required>
<?php echo $error2; ?>
</div>
<button id="ok_but0" class="btn btn-primary btn-block" type="submit" name="reg_submit">Create account</button>
</form>
</div>
</div>
</div>
</div>
我不断收到错误说;
Warning: mysqli_stmt_bind_param（）：类型定义字符串中的元素数与第160行中C：\ xampp \ htdocs \ d_project \ signup.php中的绑定变量数不匹配
"line 160: mysqli_stmt_bind_param($stmt, " ss ", $param_username, $param_gender, $param_email, $param_password);" .
1 回答
您似乎坚持多个插件的工作方式 . 我将展示帕特里克Q和我所说的一个例子：
如何处理错误也变得更加复杂 . 如果您想要中止所有插入，则可以阅读事务 . 或者，如果遇到错误，您可以停止 . 或者您可以保留一系列错误并推送到它，然后如果数组不为空则将它们全部列出 .
对于初学者来说这不是一项微不足道的任务，但这是一个很好的学习练习 . 正如Patrick Q所观察到的更好地了解所需要的内容可能会让您重新考虑存储数据的结构 .