MYSQL syntax error: inserting image location

2023-03-31 18:40 问答作者：

Thanks for taking a moment to look at my issue.

I am working on registration / login system with image upload for user avatar.

I feel I am pretty close but am getting an error in my MYSQL syntax that inserts the image location into the database.

The error reads:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2."

Everything else seems to be working okay.

FYI: The directory to store images is "user_pic," the table name is "myMembers," and the column name is "pic_location."

Here is the 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 = "";
      $g开发者_运维技巧ender = "";
      $b_m = "";
      $b_d = "";
      $b_y = "";
      $email1 = "";
      $email2 = "";
      $pass1 = "";
      $pass2 = "";
      $user_pic = "";
}

?>

I believe the offending query is:

"mysql_query("INSERT INTO myMembers (pic_location) VALUES ('$location')");"

because the problem arose after adding this section.

I also tried including the pic_location in prior query so that it read

"$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());"

but that did not seem to work either…

This is the HTML portion if you're interested:

<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>

Thanks for any advice that will help me address this issue.

I'm sure that the script could be better so any general comments on improving it are welcome, also.

Thanks a lot. I really appreciate any help.

nbewley

You've missed a closing parenthese in your query. Change:

$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());

Also, unrelated, MD5 has been cryptographically broken. You should store passwords in SHA256 using PHP's hash function: hash( 'sha256', $password );

Alternative troubleshooting, try printing out the value of $location and posting it. You may need to escape that string as well.

you miss ) in this line end that close the 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()")

should be

$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())")

继续阅读：syntax-error

MYSQL syntax error: inserting image location

更多精彩内容

精彩评论

最新问答

央视是哪个频道？

请问买过的朋友，舒提啦旅行箱实际使用体验如何？？

检查不孕不育需要的费用？

海信ULED电视画质有什么不同的地方?？

钉子可以挂的住画框幕布吗？

问答排行榜

王昌瑞《潜梦追凶》剧组庆生新锐演员未来可期？

Is it allowed to ask users to enter credit card details for own payment method?

Escaping "<" in Perl-generated XML

imessage会显示已读吗？

微信重新建群怎么建？