如何使用从POST接收的变量将条目插入SQL表 [英] How do I insert an entry to an SQL table using a variable received from POST
问题描述
我正在尝试从上一页的表单传递文本变量，并将其插入SQL表中。我遇到的问题就在网上：$ sql = INSERT INTO CUSTOMER（fname，lname）VALUES（$ firstName，'smith'） ;;。
I am trying to pass text variables from a form form a previous page and insert it into a SQL table. The problem I'm having is on the line: $sql = "INSERT INTO CUSTOMER (fname, lname) VALUES ($firstName, 'smith')";.
如果我将替换 $ firstName；如果使用基本字符串（例如 John），则名称和姓氏将按预期插入到SQL表中。但是，由于我要插入从表单收集的文本值，因此我需要能够使用非静态变量，但由于某种原因，我无法弄清楚，因此不起作用。从我在网上阅读的所有内容来看，只需将变量添加到参数中就可以使它工作，但实际上并不能。
If I were to replace "$firstName" with a basic string like "John", the name and last name would be inserted into the SQL table as intended. But, since I'm trying to insert a text value gathered from a form, I need to be able to use the non-static variables but for some reason that I cannot figure out, doesn't work. From everything I read online, just adding the variable into the parameter should make it work but it just doesn't.
我对此很陌生，如果我的问题很抱歉令人困惑。另外，我可以肯定的是问题不在于带有表格的文件上。
任何帮助都将非常棒。
I'm very new to this so I'm sorry if my question is confusing. Also, I am fairly certain that the issue does not lie on the file with the form on it. Any help would be so awesome. thanks!
这是我遇到问题的代码：
Here is the code that I'm having trouble with:
<html>
<?php
$username = $_Post['username'];
$email = $_Post['email'];
$phone = $_Post['number'];
$firstName = $_Post['firstName'];
$lastName = $_Post['lastName'];
$address = $_Post['address'];
$password = $_Post['password'];
$conn = new mysqli('localhost','root','password','database');
if($conn->connect_error){
echo "$conn->connect_error";
die("Connection Failed : ". $conn->connect_error);
} else {
$sql = "INSERT INTO CUSTOMER (fname, lname) VALUES ($firstName, 'smith')";
$conn->query($sql);
echo $execval;
echo "Registration successfully...";
$conn->close();
}
?>
</html>
推荐答案
如果您获得用户输入以插入到数据库中，应该始终使用 Prepared statement防止 SQL注入或类似的东西。
If you get user input to insert into a database you should always use "Prepared statements" to prevent "SQL injection" or comparable things.
在这里检查：
Aashish gaba的解决方案也应该可以工作，但是它是不安全的。
The solution of Aashish gaba should work as well but it's unsecure.
这应该适合您的代码：
<?php
$username = $_POST['username'];
$email = $_POST['email'];
$phone = $_POST['number'];
$firstName = $_POST['firstName'];
$lastName = $_POST['lastName'];
$address = $_POST['address'];
$password = $_POST['password'];
$conn = new mysqli('localhost','root','password','database');
if($conn->connect_error){
echo "$conn->connect_error";
die("Connection Failed : ". $conn->connect_error);
} else {
$stmt = $conn->prepare("INSERT INTO CUSTOMER (fname, lname) VALUES (?, ?)");
$stmt->bind_param("ss",$fname, $lname);
// set parameters and execute
$fname = $firstName;
$lname = $lastName;
$stmt->execute();
echo $execval;
echo "Registration successfully...";
$conn->close();
}
?>
准备好的语句的一个好处是它们可重复使用，例如：
A nice to have of a prepared statement is the fact that they are reusable like:
// set parameters and execute
$fname = "person1_fname";
$lname = "person1_lname";
$stmt->execute();
$fname = "person2_fname";
$lname = "person2_lname";
$stmt->execute();
如果您向用户打印值，也不要忘记使用东西来防止其他注入。像这样：
Also don't forget to use somthing to prevent other injections if you print a value to a user. Like:
echo "Registration successfully for" . htmlspecialchars($_Post['username']);
此外，将密码作为哈希值（具有安全的哈希功能）保存到数据库中。
In addition save the password as hash (with a secure hashfunction) into the database.
这篇关于如何使用从POST接收的变量将条目插入SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
