小贝子编程

MYSQL语法错误:插入映像位置



感谢您抽出时间来看我的问题。

我正在为用户头像上传图像注册/登录系统。

我觉得我很接近，但我得到一个错误在我的MYSQL语法插入图像位置到数据库。

错误读取:

"你的SQL语法有错误;查看手册对应于MySQL服务器版本，以便使用正确的语法在第2行"附近"。"

一切似乎都很正常。

供参考:存放图片的目录是"user_pic,"，表名是"myMembers,"，列名是"pic_location."

下面是PHP:

<?php
include_once("scripts/checkuserlog.php");
?>
<?php
// This code runs only if the username is posted
if (isset ($_POST['username'])){
     $username = preg_replace('#[^A-Za-z0-9]#i', '', $_POST['username']); // filter everything but letters and numbers
     $gender = preg_replace('#[^a-z]#i', '', $_POST['gender']); // filter everything but lowercase letters
     $b_m = preg_replace('#[^0-9]#i', '', $_POST['birth_month']); // filter everything but numbers
     $b_d = preg_replace('#[^0-9]#i', '', $_POST['birth_day']); // filter everything but numbers
     $b_y = preg_replace('#[^0-9]#i', '', $_POST['birth_year']); // filter everything but numbers
     $email1 = $_POST['email1'];
     $email2 = $_POST['email2'];
     $pass1 = $_POST['pass1'];
     $pass2 = $_POST['pass2'];
     $user_pic = $_FILES['user_pic'];
     $humancheck = $_POST['humancheck'];
     $email1 = stripslashes($email1); 
     $pass1 = stripslashes($pass1); 
     $email2 = stripslashes($email2);
     $pass2 = stripslashes($pass2); 
     $email1 = strip_tags($email1);
     $pass1 = strip_tags($pass1);
     $email2 = strip_tags($email2);
     $pass2 = strip_tags($pass2);
     // Connect to database
     include_once "scripts/connect_to_mysql.php";
     $emailCHecker = mysql_real_escape_string($email1);
     $emailCHecker = str_replace("`", "", $emailCHecker);
     // Database duplicate username check setup for use below in the error handling if else conditionals
     $sql_uname_check = mysql_query("SELECT username FROM myMembers WHERE username='$username'"); 
     $uname_check = mysql_num_rows($sql_uname_check);
     // Database duplicate e-mail check setup for use below in the error handling if else conditionals
     $sql_email_check = mysql_query("SELECT email FROM myMembers WHERE email='$emailCHecker'");
     $email_check = mysql_num_rows($sql_email_check);
     // Error handling for missing data
     if ((!$username) || (!$gender) || (!$b_m) || (!$b_d) || (!$b_y) || (!$email1) || (!$email2) || (!$pass1) || (!$pass2) || (!$user_pic)) { 
     $errorMsg = 'ERROR: You did not submit the following required information:<br /><br />';
     if(!$username){ 
       $errorMsg .= ' * User Name<br />';
     } 
     if(!$gender){ 
       $errorMsg .= ' * Gender: Confirm your sex.<br />';
     }  
     if(!$b_m){ 
       $errorMsg .= ' * Birth Month<br />';      
     }
     if(!$b_d){ 
       $errorMsg .= ' * Birth Day<br />';        
     } 
     if(!$b_y){ 
       $errorMsg .= ' * Birth year<br />';        
     }      
     if(!$email1){ 
       $errorMsg .= ' * Email Address<br />';      
     }
     if(!$email2){ 
       $errorMsg .= ' * Confirm Email Address<br />';        
     }  
     if(!$pass1){ 
       $errorMsg .= ' * Login Password<br />';      
     }
     if(!$pass2){ 
       $errorMsg .= ' * Confirm Login Password<br />';        
     }  
     if(!$user_pic){ 
       $errorMsg .= ' * Add a Profile Photo<br />';        
     }  
     } else if ($email1 != $email2) {
              $errorMsg = 'ERROR: Your Email fields below do not match<br />';
     } else if ($pass1 != $pass2) {
              $errorMsg = 'ERROR: Your Password fields below do not match<br />';    
     } else if (strlen($username) < 4) {
               $errorMsg = "<u>ERROR:</u><br />Your User Name is too short. 4 - 20 characters please.<br />"; 
     } else if (strlen($username) > 20) {
               $errorMsg = "<u>ERROR:</u><br />Your User Name is too long. 4 - 20 characters please.<br />"; 
     } else if ($uname_check > 0){ 
              $errorMsg = "<u>ERROR:</u><br />Your User Name is already in use inside of our system. Please try another.<br />"; 
     } else if ($email_check > 0){ 
              $errorMsg = "<u>ERROR:</u><br />Your Email address is already in use inside of our system. Please use another.<br />"; 
     } else if ($_FILES['user_pic']['size'] > 2000000 ){ 
              $errorMsg = "<u>ERROR:</u><br />Your image is too large.<br />"; 
              unlink($_FILES['user_pic']['tmp_name']); 
     } else if (!preg_match("/.(gif|jpg|png|jpeg)$/i", $_FILES['user_pic']['name'])) { 
              $errorMsg = "<u>ERROR:</u><br />Your image is in an unacceptable format.<br />"; 
              unlink($_FILES['user_pic']['tmp_name']); 
     } else {

     // Error handling is ended, process the data and add member to database
     $email1 = mysql_real_escape_string($email1);
     $pass1 = mysql_real_escape_string($pass1);
     // Add MD5 Hash to the password variable
     $db_password = md5($pass1); 
     // Convert Birthday to a DATE field type format(YYYY-MM-DD) out of the month, day, and year supplied 
     $full_birthday = "$b_y-$b_m-$b_d";
     // GET USER IP ADDRESS
     $ipaddress = getenv('REMOTE_ADDR');
     //add the avatar
     $name = $_FILES['user_pic']['name'];
     $tmp_name = $_FILES['user_pic']['tmp_name'];
     $location = "user_pic/$name";
     move_uploaded_file($tmp_name, "user_pic/.$name");
     // Add user info into the database table for the main site table
     $sql = mysql_query("INSERT INTO myMembers (username, gender, birthday, email, password, pic_location, ipaddress, sign_up_date) 
     VALUES('$username','$gender','$full_birthday','$email1','$db_password', '$location', '$ipaddress', now()")  
     or die (mysql_error());
     $id = mysql_insert_id();
     // Create directory to hold each user's files(pics, MP3s, etc.)
     $newname = "image01.jpg";
     $place_file = move_uploaded_file( $_FILES['user_pic']['tmp_name'], "members/$id/".$newname);
     mysql_query("INSERT INTO myMembers (pic_location) VALUES ('$location')");
     include_once 'msgToUser.php'; 
     exit();
     }// Close else after duplication checks
} else { // if the form is not posted with variables, place default empty variables so no warnings or errors show
      $errorMsg = "";
     $username = "";
      $gender = "";
      $b_m = "";
      $b_d = "";
      $b_y = "";
      $email1 = "";
      $email2 = "";
      $pass1 = "";
      $pass2 = "";
      $user_pic = "";
}
?>

我认为违规查询是:

"mysql_query("INSERT INTO myMembers (pic_location) VALUES .(美元位置)");"

，因为在添加此部分后出现了问题。

我还尝试在先前的查询中包括pic_location，以便它读取

$sql = mysql_query("INSERT INTO myMembers (username, gender，)生日，电子邮件，密码，pic_location, ipaddress, sign_up_date)美元美元值("用户名"、"性别"、"full_birthday美元","email1美元","db_password美元","美元位置"、"美元ipaddress’,现在()")或死亡(mysql_error());"

但这似乎也不起作用…

如果您感兴趣，这是HTML部分:

<html>
<body>
<h4>Create your Account: </h4><h9>all fields required</h9>
<table class="table_f" width="100%" cellpadding="3">
<form action="register.php" method="post" enctype="multipart/form-data">
          <tr>
          <td colspan="2"><font color="#94A0D1"><?php print "$errorMsg"; ?></font></td>
          </tr>       
          <tr>
          <td><h11>User Name:</h11></td>
            <td><input name="username" type="text" class="formFields" id="username" value="<?php print "$username"; ?>" size="32" maxlength="20" />
          </tr>
          <tr>
            <td><h11>Gender:</h11></td>
            <td><label>        
            <input name="gender" style="color: #a2a2a2; font-family: 'light', Verdana; font-size: 11px; letter-spacing: 1px" type="radio" id="gender" value="m" checked="checked" />Male &nbsp;
            <input type="radio" name="gender" id="gender" value="f" />Female
</label></td>
          </tr>
          <tr>
            <td><h11>Date of Birth: </h11></td>
            <td>
<select name="birth_month" class="formFields" id="birth_month">
<option value="<?php print "$b_m"; ?>"><?php print "$b_m"; ?></option>
<option value="01">January</option>
<option value="02">February</option>
<option value="03">March</option>
<option value="04">April</option>
<option value="05">May</option>
<option value="06">June</option>
<option value="07">July</option>
<option value="08">August</option>
<option value="09">September</option>
<option value="10">October</option>
<option value="11">November</option>
<option value="12">December</option>
</select> 
<select name="birth_day" class="formFields" id="birth_day">
<option value="<?php print "$b_d"; ?>"><?php print "$b_d"; ?></option>
<option value="01">1</option>
<option value="02">2</option>
<option value="03">3</option>
<option value="04">4</option>
<option value="05">5</option>
<option value="06">6</option>
<option value="07">7</option>
<option value="08">8</option>
<option value="09">9</option>
<option value="10">10</option>
<option value="11">11</option>
<option value="12">12</option>
<option value="13">13</option>
<option value="14">14</option>
<option value="15">15</option>
<option value="16">16</option>
<option value="17">17</option>
<option value="18">18</option>
<option value="19">19</option>
<option value="20">20</option>
<option value="21">21</option>
<option value="22">22</option>
<option value="23">23</option>
<option value="24">24</option>
<option value="25">25</option>
<option value="26">26</option>
<option value="27">27</option>
<option value="28">28</option>
<option value="29">29</option>
<option value="30">30</option>
<option value="31">31</option>
</select> 
<select name="birth_year" class="formFields" id="birth_year">
<option value="<?php print "$b_y"; ?>"><?php print "$b_y"; ?></option>
<option value="2010">2010</option>
<option value="2009">2009</option>
<option value="2008">2008</option>
<option value="2007">2007</option>
<option value="2006">2006</option>
<option value="2005">2005</option>
<option value="2004">2004</option>
<option value="2003">2003</option>
<option value="2002">2002</option>
<option value="2001">2001</option>
<option value="2000">2000</option>
<option value="1999">1999</option>
<option value="1998">1998</option>
<option value="1997">1997</option>
<option value="1996">1996</option>
<option value="1995">1995</option>
<option value="1994">1994</option>
<option value="1993">1993</option>
<option value="1992">1992</option>
<option value="1991">1991</option>
<option value="1990">1990</option>
<option value="1989">1989</option>
<option value="1988">1988</option>
<option value="1987">1987</option>
<option value="1986">1986</option>
<option value="1985">1985</option>
<option value="1984">1984</option>
<option value="1983">1983</option>
<option value="1982">1982</option>
<option value="1981">1981</option>
<option value="1980">1980</option>
<option value="1979">1979</option>
<option value="1978">1978</option>
<option value="1977">1977</option>
<option value="1976">1976</option>
<option value="1975">1975</option>
<option value="1974">1974</option>
<option value="1973">1973</option>
<option value="1972">1972</option>
<option value="1971">1971</option>
<option value="1970">1970</option>
<option value="1969">1969</option>
<option value="1968">1968</option>
<option value="1967">1967</option>
<option value="1966">1966</option>
<option value="1965">1965</option>
<option value="1964">1964</option>
<option value="1963">1963</option>
<option value="1962">1962</option>
<option value="1961">1961</option>
<option value="1960">1960</option>
<option value="1959">1959</option>
<option value="1958">1958</option>
<option value="1957">1957</option>
<option value="1956">1956</option>
<option value="1955">1955</option>
<option value="1954">1954</option>
<option value="1953">1953</option>
<option value="1952">1952</option>
<option value="1951">1951</option>
<option value="1950">1950</option>
<option value="1949">1949</option>
<option value="1948">1948</option>
<option value="1947">1947</option>
<option value="1946">1946</option>
<option value="1945">1945</option>
<option value="1944">1944</option>
<option value="1943">1943</option>
<option value="1942">1942</option>
<option value="1941">1941</option>
<option value="1940">1940</option>
<option value="1939">1939</option>
<option value="1938">1938</option>
<option value="1937">1937</option>
<option value="1936">1936</option>
<option value="1935">1935</option>
<option value="1934">1934</option>
<option value="1933">1933</option>
<option value="1932">1932</option>
<option value="1931">1931</option>
</select> 
</div>
</td>
          </tr>                  
          <tr>
            <td><h11>Email Address: </h11></td>
            <td><input name="email1" type="text" class="formFields" id="email1" value="<?php print "$email1"; ?>" size="32" maxlength="48" /></td>
          </tr>
          <tr>
            <td><h11>Confirm Email: </h11></td>
            <td><input name="email2" type="text" class="formFields" id="email2" value="<?php print "$email2"; ?>" size="32" maxlength="48" /></td>
          </tr>
          <tr>
            <td><h11>Create Password: </h11></td>
            <td><input name="pass1" type="password" class="formFields" id="pass1" size="32" maxlength="16" />
          </tr>
          <tr>
            <td><h11>Confirm Password: </h11></td>
            <td><input name="pass2" type="password" class="formFields" id="pass2" size="32" maxlength="16" />
          </tr>
          <tr>
            <td><h11>Add Profile Photo: </h11></td>         
              <input type='hidden' name='MAX_FILE_SIZE' value='2000000'>
              <td width="521"><input name="user_pic" type="file" class="formFields" size="42" />
              50 kb max </td>
          </tr> 
          <tr>
            <td>&nbsp;</td>
            <td>
              <input type="submit" style="color: #a2a2a2; font-family: helvetica; font-size: 11px; letter-spacing: 1px" name="Submit" value="Register" />
            </td>
          </tr>
        </form>
      </table>
      <br /></td>
  </tr>
</table>
</body>
</html>

谢谢你的建议，这将有助于我解决这个问题。

我相信这个脚本可以更好，所以任何关于改进它的一般性意见也是欢迎的。

非常感谢。我非常感谢你的帮助。

nbewley

您的查询中遗漏了一个右括号。变化:

$sql = mysql_query("INSERT INTO myMembers (username, gender, birthday, email, password, pic_location, ipaddress, sign_up_date) 
     VALUES('$username','$gender','$full_birthday','$email1','$db_password', '$location', '$ipaddress', now()")  
     or die (mysql_error());


$sql = mysql_query("INSERT INTO myMembers (username, gender, birthday, email, password, pic_location, ipaddress, sign_up_date) 
     VALUES('$username','$gender','$full_birthday','$email1','$db_password', '$location', '$ipaddress', now())")  
     or die (mysql_error());

同样，与此无关的是，MD5密码被破解了。您应该使用PHP的哈希函数在SHA256中存储密码:hash( 'sha256', $password );

可选的故障排除，尝试打印$location的值并发布它。您可能还需要转义该字符串

你错过了)在这行结束，关闭VALUES 

$sql = mysql_query("INSERT INTO myMembers (username, gender, birthday, email, password, pic_location, ipaddress, sign_up_date) 
     VALUES('$username','$gender','$full_birthday','$email1','$db_password', '$location', '$ipaddress', now()")
应该

$sql = mysql_query("INSERT INTO myMembers (username, gender, birthday, email, password, pic_location, ipaddress, sign_up_date) 
     VALUES('$username','$gender','$full_birthday','$email1','$db_password', '$location', '$ipaddress', now())")

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium