Debugging a MYSQL syntax error

Question

The following is a "forgot password" script I have on my site. I have one MYSQL table where I store the email addresses of users. It is called 'members' and has 2 columns: 'user' (users' email addresses) and 'pass' (their passwords).

The email address [email protected] exists in the members table. When I input this email address in the forgot password form, I get this error. I am having a lot of trouble debugging this.

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 '@gmail.com' at line 1

Forgot password script:

<?php // forgot_password.php
include_once 'header.php';

if (isset($_POST['submitted']))
    { // Handle the form.
    if (empty($_POST['email'])) 
        {
        $uid = FALSE;
        echo 'You forgot to enter your registered email address!';    
        }
        else 
            {
            // Check for the existence of the inputted email address.
            $email = trim(sanitizeString($_POST['email']));
            $result = queryMysql("SELECT user FROM members WHERE user='$email'");
                if (mysql_num_rows($result) == 1)
                    {
                    // Retrieve the user's email address
                    list($uid) = mysql_fetch_array ($result, MYSQL_NUM);
                    }
                    else 
                        {
                        echo '<p><font color="red" size="+1">The submitted email address does not match those on file!</font></p>';
                        $uid = FALSE;
                        }
            }

        if ($uid)
            {
            $p = substr(md5(uniqid(rand(),1)),3,10);
            $result = queryMysql("UPDATE members SET pass=SHA('$p') WHERE user = $uid");
            if (mysql_affected_rows() == 1) 
                {
                // If it ran OK, send an email.
                $email = trim(sanitizeString($_POST['email']));
                $body = "Your password has been temporarily changed to '$p'. Please log in using this password and your username.";
                mail ($email, 'Your temporary password.', $body, 'From: [email protected]');
                echo '<h3>Your password has been changed. You will receive the new, temporary password at the email address with which you registered. Once you have logged in with this password, you may change it by clicking on the "change password" link.</h3>';
                mysql_close(); // Close the database connection.
                }
                else
                    {
                    // If it did not run OK.
                    echo '<p><font color="red" size="+1">Your password could not be changed due to a system error. We apologize for any inconvenience.</font></p>';
                    }
            }
                else // Failed the validation test.
                    {
                    echo '<p><font color="red" size="+1">Please try again.</font></p>';
                    }
    } // End of the main Submit conditional.
?>

<h1>Reset Your Password</h1>
<p>Enter your email address below and your password will be reset.</p>
<form action="forgot_password.php" method="post">
<fieldset>
<p><b>Your registered email address:</b> <input type="text" name="email" size="20" maxlength="40" value="<?php if (isset($_POST['email'])) echo $_POST['email']; ?>" /></p>
</fieldset>
<div align="center"><input type="submit" name="submit" value="Reset My Password" /></div>
<input type="hidden" name="submitted" value="TRUE" />
</form>
</div>

Did you try outputting the generated query? Also the mysql extension is deprecated. You should switch to MySQLi or PDO and use prepared statements. — TimWolla
– TimWolla, Commented Mar 23, 2014 at 3:06
As an aside, email addresses aren't the best PK, I'd stick with an Auto-Inc INT. — Jack
– Jack, Commented Mar 23, 2014 at 3:11
Problem is, as a beginner, I am learning from the latest version of a book (O'Reilly's PHP guide) and it is not updated for mysqli. Programming language gets deprecated within a few months of books getting published. — user3388726
– user3388726, Commented Mar 23, 2014 at 3:11
have you tried removing the single quotes from the $email or echo it to see what actually being passed to the query? — iamsleepy
– iamsleepy, Commented Mar 23, 2014 at 3:12

Ignacio Vazquez-Abrams · Accepted Answer · 2014-03-23 03:22:38Z

1

You forgot to quote $uid in your UPDATE statement. And you forgot to escape it as well.

answered Mar 23, 2014 at 3:22

Ignacio Vazquez-Abrams

804k160 gold badges1.4k silver badges1.4k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Debugging a MYSQL syntax error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related