html注册表单插入数据库错误

Question

我有一个注册表格，我已经验证使用JavaScript的HTML。问题是，当我进入我的数据库表时，它显示了一个错误的电话没有字段，这是表中的主键。问题是，虽然每次我尝试输入主键字段的不同值，它显示错误，如给定下面的例子：

INSERT INTO user_details(Name,Phone_No,Date_Of_Birth,Gender,Email,Password) VALUES ('XYZ','6734746586','2010-10-29','Female','[email protected]','123456') 

重复条目 '2147483647' 关键 'Phone_No'

正如你可以看到这里，我已经进入了6734746586电话没有。列，但错误显示为2147483647.怎么可能？

的registration.html形式在下面给出：

<div id="id02" class="modal"> 
<form name="register" class="modal-content animate" action="register.php" method="post" onSubmit="return validate();"> 
    <div class="imgcontainer"> 
     <span onclick="document.getElementById('id02').style.display='none'" class="close" title="Close Modal">&times;</span> 
    </div> 
    <div class="container"> 
     <label><b>Name:</b></label> 
     <input type="text" placeholder="Enter Name" name="name" id="name" required> 
     <label><b>Phone No.:</b></label> 
     <input type="text" placeholder="Enter Phone number" name="phone" id="phone" maxlength="10" required>  
     <label><b>Date of Birth:</b></label> 
     <input type="date" placeholder="Enter Date of Birth" name="dob" id="dob" required> 
     <label><b>Gender:</b></label> 
     <table width='100%'> 
     <tr> 
     <td><input type="radio" name="gender" id="gender" value="Male" checked> Male </td> 
     <td><input type="radio" name="gender" id="gender" value="Female"> Female </td> 
     </table> 
     <label><b>E-mail:</b></label> 
     <input type="text" placeholder="Enter Email Id" name="email" id="email" required> 
     <label><b>Password:</b></label> 
     <input type="password" placeholder="Enter Password" name="psw" id="psw" required> 
     <label><b>Repeat Password:</b></label> 
     <input type="password" placeholder="Repeat Password" name="psw-repeat" id="psw-repeat" required>    
     <input type="checkbox" checked="checked"> Remember me 
     <p>By creating an account you agree to our <a href="#">Terms & Privacy</a>.</p> 
     <div class="clearfix">  
      <button type="submit" class="register" id="register"> Register </button> 
      <button type="button" onclick="document.getElementById('id02').style.display='none'" class="cancelbtn">Cancel</button>  
     </div> 
    </div> 
</form> 
<script> 
    // Get the modal 
    var modal = document.getElementById('id02'); 
    // When the user clicks anywhere outside of the modal, close it 
    window.onclick = function(event) 
    { 
     if (event.target == modal) 
     { 
      modal.style.display = "none"; 
     } 
    } 
</script>  

用于验证形式的JavaScript是如下给出：

function validate() 

{ 

    var val=document.getElementById('phone').value; 

    if (!(/^\d{10}$/.test(val))) // Here \d means "digit," and {10} means "ten times." The^and $ anchor it to the start and end, so something like asdf1234567890asdf does not match. 
    { 
     alert("Invalid phone number. It must be of 10 digits only"); 

     phone.focus(); 

     return false; 

    } 

    var psw=document.getElementById('psw').value; 

    var psw_repeat=document.getElementById('psw-repeat').value; 

    if(!(psw==psw_repeat)) 

    { 
     alert ("Password & Repeat Password fields must be same"); 

     return false; 

    } 

    return true; 

} 

PHP的形式进行数据库连接是如下：

<?php 
$name=$_POST["name"]; 

$phone=$_POST["phone"]; 

$dob=$_POST["dob"]; 

$gender=$_POST["gender"]; 

$email=$_POST["email"]; 

$psw=$_POST["psw"]; 


$dbhost = "localhost:3306"; 

$dbuser = "root"; 

$dbpass = ""; 

$dbname="hotel"; 

//create connection 

$conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname); 

//check connection 

if($conn->connect_error) 

{ 

    die("Connection failed:" . $conn->connect_error); 

} 


$sql="INSERT INTO user_details(Name,Phone_No,Date_Of_Birth,Gender,Email,Password) VALUES ('$name','$phone','$dob','$gender','$email','$psw')"; 

if($conn->query($sql)== TRUE) 

{ 

    header('Location: index.php'); 

} 

else 

{ 

    echo "Error:".$sql."<br>".$conn->error; 

} 

$conn->close(); 

?> 

Answer 1

电话号码不是整数，它们是字符串。您创建了一个整数的列，并且电话号码大于该数据类型允许的值，因此它将值截断为它可以容纳的最大值，即在32位系统上的值为2147483647。显然你也设置它有一个唯一的索引，所以你显然遇到了一个截断的数字，并没有捕获你的错误，现在你不能插入另一个具有相同值的行。将该列更改为char或varchar，它将起作用（假设您不尝试插入相同的电话号码两次，因为您拥有的唯一索引会引发相同的错误）。

ALTER TABLE user_details MODIFY Phone_No CHAR(10); 

仅供参考，您的脚本是在SQL Injection Attack风险有一个看看发生了什么事Little Bobby Tables即使if you are escaping inputs, its not safe!使用prepared parameterized statements。