将用户插入sql数据库时出现问题

Question

我想做一个简单的登录和注册页面，但每次我试图登录用户时，它都会告诉我它无法进行查询。我使用mariadb服务器。

这是插入

$sql="INSERT INTO userid  VALUES ( {$_POST['usname']} , {$_POST['firstname']} , {$_POST['lastname']} , {$_POST['wordpass']} , '' )"; 

这是登录部分

<form action="thankyou.php" method="post">
<div>
<label for="uname">Choose an Username: </label>
<input type="text" id="uname" name="usname" pattern="[a-z]{3-15}" placeholder="Username">
<div class="tinytext"> Username must be at least 3 characters long and at most 15 characters long, containing only lowercase letters </div>
</div>
<div>
<label for="fname"> Enter First Name: </label>
<input type="text" id="fname" name="firstname" pattern="[a-z]{3-15}" placeholder="First Name">
<div class="tinytext"> First Name must be at least 3 characters long and at most 15 characters long, containing only lowercase letters</div>        
</div>
<div>
<label for="lname"> Enter Last Name Name: </label>
<input type="text" id="fname" name="lastname" pattern="[a-z]{3-15}" placeholder="Last Name">
<div class="tinytext"> Last Name must be at least 3 characters long and at most 15 characters long, containing only lowercase letters</div>     
</div>
<div>
<label for="pwd"> Enter Password: </label>
<input type="password" id="pwd" name="wordpass" minlenght="8" maxlenght="30" placeholder="Password">
<div class="tinytext"> Password must be at least 8 characters long and at most 30 characters long</div>     
</div>
<div>
<button onclick="window.location.href='thankyou.php'">Submit</button>
</div>
</form>

Answer 1

除了表单问题，SQL还将转换为错误的SQL语句。

假设：

usname = myusername
firstname = myfirstname
lastname = mylastname
wordpass = mypass

将翻译为：

INSERT INTO userid  VALUES (myusername, myfirstname, mylastname, mypass, '')

其中所有的值都不是字符串，并且会导致SQL错误！应为：

INSERT INTO userid  VALUES ('myusername', 'myfirstname', 'mylastname', 'mypass', '')

这是你的错误

BUUUT，您应该阅读有关SQL-IJECTION的内容，因为如果您将其直接发送到DB，您将得到这些内容！

编制报表示例(使用PDO(

<?php
// open new PDO connection
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// prepare your data
$data = [
'username' => $_POST['usname']
'firstname' => $_POST['firstname']
'lastname' => $_POST['lastname']
'password' => $_POST['wordpass']
];
// create prepared statement to prevent SQL-injection
$sql = "INSERT INTO userid (username, firstname, lastname, password) VALUES (:username, :firstname, :lastname, :password)";
$stmt = $conn->prepare($sql);
// execute statement and persist to DB
$stmt->execute($data);

其他示例可在此处找到：https://www.w3schools.com/php/php_mysql_prepared_statements.asp

Answer 2

实际上，表单中有一个小问题，您必须使其成为提交按钮，而不仅仅是一个简单的按钮。

因为在表单提交之前，您的thankyou.php也无法访问值

你的按钮应该像这个

<button type="submit" >Submit</button>

你的完整代码是这样的-

<form action="thankyou.php" method="post">
<div>
<label for="uname">Choose an Username: </label>
<input type="text" id="uname" name="usname" pattern="[a-z]{3-15}" placeholder="Username">
<div class="tinytext"> Username must be at least 3 characters long and at most 15 characters long, containing only lowercase letters </div>
</div>
<div>
<label for="fname"> Enter First Name: </label>
<input type="text" id="fname" name="firstname" pattern="[a-z]{3-15}" placeholder="First Name">
<div class="tinytext"> First Name must be at least 3 characters long and at most 15 characters long, containing only lowercase letters</div>        
</div>
<div>
<label for="lname"> Enter Last Name Name: </label>
<input type="text" id="fname" name="lastname" pattern="[a-z]{3-15}" placeholder="Last Name">
<div class="tinytext"> Last Name must be at least 3 characters long and at most 15 characters long, containing only lowercase letters</div>     
</div>
<div>
<label for="pwd"> Enter Password: </label>
<input type="password" id="pwd" name="wordpass" minlenght="8" maxlenght="30" placeholder="Password">
<div class="tinytext"> Password must be at least 8 characters long and at most 30 characters long</div>     
</div>
<div>
<button type="submit" >Submit</button>
</div>
</form>