Unable to add user to MySQL database using PHP, error code #1064

Question

UPDATE:

My initial question was regarding an issue with adding a user to the database. It was solved but now I am having trouble logging in (login.php). My database has a current email entry of [email protected] , but when I try to login with this email, it says "Login failed".

I tried to incorporate the initial question's solution into login.php but it seems the code was already present. So I'm stuck... here is my register.php page and my login.php page.

I have a feeling it has to do with :email somehow. What is wrong with my login.php file?

register.php

<?php 

    // First we execute our common code to connection to the database and start the session 
    require("common.php"); 

    // This if statement checks to determine whether the registration form has been submitted 
    // If it has, then the registration code is run, otherwise the form is displayed 
    if(!empty($_POST)) 
    { 
        // Ensure that the user has entered a non-empty username 
        if(empty($_POST['email'])) 
        { 
            // Note that die() is generally a terrible way of handling user errors 
            // like this.  It is much better to display the error with the form 
            // and allow the user to correct their mistake.  However, that is an 
            // exercise for you to implement yourself. 
            die("Please enter an email."); 
        } 

        // Make sure the user entered a valid E-Mail address 
        // filter_var is a useful PHP function for validating form input, see: 
        // http://us.php.net/manual/en/function.filter-var.php 
        // http://us.php.net/manual/en/filter.filters.php 
        if(!filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) 
        { 
            die("Invalid E-Mail Address"); 
        } 

      // Now we perform the same type of check for the email address, in order 
        // to ensure that it is unique. 
        $query = " 
            SELECT 
                1 
            FROM users 
            WHERE 
                email = :email 
        "; 

        $query_params = array( 
            ':email' => $_POST['email'] 
        ); 

        try 
        { 
            $stmt = $db->prepare($query); 
            $result = $stmt->execute($query_params); 
        } 
        catch(PDOException $ex) 
        { 
            die("Failed to run query: "); 
        } 

        $row = $stmt->fetch(); 

        if($row) 
        { 
            die("This email address is already registered"); 
        } 

        // An INSERT query is used to add new rows to a database table. 
        // Again, we are using special tokens (technically called parameters) to 
        // protect against SQL injection attacks. 
        $query = " 
            INSERT INTO users (  
                email 
            ) VALUES ( 
             :email 
            ) 
        "; 

        $query_params = array( 
        ':email' => $_POST['email'] 
    ); 

    try 
    { 
        $stmt = $db->prepare($query); 
        $result = $stmt->execute($query_params); 
    } 
    catch(PDOException $ex) 
    { 
        die("Failed to run query: "); 
    }  

        // This redirects the user back to the login page after they register 
        header("Location: login.php"); 

        // Calling die or exit after performing a redirect using the header function 
        // is critical.  The rest of your PHP script will continue to execute and 
        // will be sent to the user if you do not die or exit. 
        die("Redirecting to login.php"); 
    } 

?> 
<h1>Register</h1> 
<form action="register.php" method="post"> 
    E-Mail:<br /> 
    <input type="text" name="email" value="" /> 
    <br /><br /> 
    <input type="submit" value="Register" /> 
</form>

login.php

<?php 

    // First we execute our common code to connection to the database and start the session 
    require("common.php"); 

    // This if statement checks to determine whether the login form has been submitted 
    // If it has, then the login code is run, otherwise the form is displayed 
    if(!empty($_POST)) 
    { 
        // This query retreives the user's information from the database using 
        // their email. 
        $query = " 
            SELECT 
                email 
            FROM users 
            WHERE 
                email = :email 
        "; 

        // The parameter values 
        $query_params = array( 
            ':email' => $_POST['email'] 
        ); 

        try 
        { 
            // Execute the query against the database 
            $stmt = $db->prepare($query); 
            $result = $stmt->execute($query_params); 
        } 
        catch(PDOException $ex) 
        { 
            // Note: On a production website, you should not output $ex->getMessage(). 
            // It may provide an attacker with helpful information about your code.  
            die("Failed to run query: " . $ex->getMessage()); 
        } 

        // This variable tells us whether the user has successfully logged in or not. 
        // We initialize it to false, assuming they have not. 
        // If we determine that they have entered the right details, then we switch it to true. 
        $login_ok = false; 

        // Retrieve the user data from the database.  If $row is false, then the email
        // they entered is not registered. 
        $row = $stmt->fetch(); 

        // If the user logged in successfully, then we send them to the private members-only page 
        // Otherwise, we display a login failed message and show the login form again 
        if($login_ok) 
        { 

            // This stores the user's data into the session at the index 'user'. 
            // We will check this index on the private members-only page to determine whether 
            // or not the user is logged in.  We can also use it to retrieve 
            // the user's details. 
            $_SESSION['user'] = $row; 

            // Redirect the user to the private members-only page. 
            header("Location: private.php"); 
            die("Redirecting to: private.php"); 
        } 
        else 
        { 
            // Tell the user they failed 
            print("Login Failed.");   
        } 
    } 

?> 
<h1>Login</h1> 
<form action="login.php" method="post"> 
    Email:<br /> 
    <input type="text" name="email" value="My Email" /> 
    <br /><br /> 
    <input type="submit" value="Login" /> 
</form> 
<a href="register.php">Register</a>

Thank you!

You can disregard the text below.

OLD QUESTION (for register.php):

I'm trying to create a simple login system for my site, where only an email address is needed for registration/login. The problem arises when I try to add a user to the database using phpMyAdmin, I get an error (more below).

I've successfully created a MySQL database using:

CREATE TABLE `users` (
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

and then on the register.php page I have this code.

<?php 

    // First we execute our common code to connection to the database and start the session 
    require("common.php"); 

    // This if statement checks to determine whether the registration form has been submitted 
    // If it has, then the registration code is run, otherwise the form is displayed 
    if(!empty($_POST)) 
    { 
        // Ensure that the user has entered a non-empty username 
        if(empty($_POST['email'])) 
        { 
            // Note that die() is generally a terrible way of handling user errors 
            // like this.  It is much better to display the error with the form 
            // and allow the user to correct their mistake.  However, that is an 
            // exercise for you to implement yourself. 
            die("Please enter an email."); 
        } 

        // Make sure the user entered a valid E-Mail address 
        // filter_var is a useful PHP function for validating form input, see: 
        // http://us.php.net/manual/en/function.filter-var.php 
        // http://us.php.net/manual/en/filter.filters.php 
        if(!filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) 
        { 
            die("Invalid E-Mail Address"); 
        } 

      // Now we perform the same type of check for the email address, in order 
        // to ensure that it is unique. 
        $query = " 
            SELECT 
                1 
            FROM users 
            WHERE 
                email = :email 
        "; 

        $query_params = array( 
            ':email' => $_POST['email'] 
        ); 

        try 
        { 
            $stmt = $db->prepare($query); 
            $result = $stmt->execute($query_params); 
        } 
        catch(PDOException $ex) 
        { 
            die("Failed to run query: "); 
        } 

        $row = $stmt->fetch(); 

        if($row) 
        { 
            die("This email address is already registered"); 
        } 

        // An INSERT query is used to add new rows to a database table. 
        // Again, we are using special tokens (technically called parameters) to 
        // protect against SQL injection attacks. 
        $query = " 
            INSERT INTO users (  
                email 
            ) VALUES ( 
             :email 
            ) 
        "; 



        // This redirects the user back to the login page after they register 
        header("Location: login.php"); 

        // Calling die or exit after performing a redirect using the header function 
        // is critical.  The rest of your PHP script will continue to execute and 
        // will be sent to the user if you do not die or exit. 
        die("Redirecting to login.php"); 
    } 

?> 
<h1>Register</h1> 
<form action="register.php" method="post"> 
    E-Mail:<br /> 
    <input type="text" name="email" value="" /> 
    <br /><br /> 
    <input type="submit" value="Register" /> 
</form>

The problem might be due to this code:

INSERT INTO users (  
                email 
            ) VALUES ( 
             :email 
            )

which is the code causing me problems in phpMyAdmin. It gives me this error:

#1064 - 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 ':email 
            )' at line 4

The register.php page is successfully connecting to my DB and is also redirecting me as it should, so I feel the problem is adding the user.

Where do you actually call the INSERT... statement? I can only see a prepare/execute with the SELECT... statement — DaveyBoy
– DaveyBoy, Commented Mar 17, 2015 at 21:40
It's there.. about 70% of the way down the code, you'll see INSERT INTO users ( email ) VALUES ( :email ) — Nova
– Nova, Commented Mar 17, 2015 at 21:41
I see it but you don't call it. You call prepare and call the SELECT... statement, assign the INSERT... statement to a variable and then do a redirect — DaveyBoy
– DaveyBoy, Commented Mar 17, 2015 at 21:43
Thanks Davey, but I have no idea how to do what you said. I started using MySQL today. Can you provide a brief example? I feel this is an easy fix — Nova
– Nova, Commented Mar 17, 2015 at 21:46

Konrad · Accepted Answer · 2015-03-17 21:47:49Z

2

It doesn't look like your INSERT statement is executing the query or assigning the email parameter.

Under

$query = " 
    INSERT INTO users (  
        email 
    ) VALUES ( 
     :email
    ) 
";

Add

 $query_params = array( 
        ':email' => $_POST['email'] 
    ); 

    try 
    { 
        $stmt = $db->prepare($query); 
        $result = $stmt->execute($query_params); 
    } 
    catch(PDOException $ex) 
    { 
        die("Failed to run query: "); 
    }

answered Mar 17, 2015 at 21:47

Konrad

1582 silver badges10 bronze badges

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

3 Comments

Nova Over a year ago

Hi Konrad. I am now having a similar issue with login.php. Can you look at my updated question and help me? Thanks a lot

Konrad Over a year ago

@Nova It looks like you would have an issue where you set $login_ok = false; , but never set it back to true based on the results of your query. It's always false in your script.

Nova Over a year ago

Oh yes I see that. This worked! $row = $stmt->fetch(); if($row) { $login_ok = true; } Thanks again

DaveyBoy · Accepted Answer · 2015-03-17 21:56:20Z

I've added/adjusted your code. I've used different placeholder names to make debugging easier

<?php 

// First we execute our common code to connection to the database and start the session 
require("common.php"); 

// This if statement checks to determine whether the registration form has been submitted 
// If it has, then the registration code is run, otherwise the form is displayed 
if(!empty($_POST)) 
{ 
    // Ensure that the user has entered a non-empty username 
    if(empty($_POST['email'])) 
    { 
        // Note that die() is generally a terrible way of handling user errors 
        // like this.  It is much better to display the error with the form 
        // and allow the user to correct their mistake.  However, that is an 
        // exercise for you to implement yourself. 
        die("Please enter an email."); 
    } 

    // Make sure the user entered a valid E-Mail address 
    // filter_var is a useful PHP function for validating form input, see: 
    // http://us.php.net/manual/en/function.filter-var.php 
    // http://us.php.net/manual/en/filter.filters.php 
    if(!filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) 
    { 
        die("Invalid E-Mail Address"); 
    } 

  // Now we perform the same type of check for the email address, in order 
    // to ensure that it is unique. 
    $checkSQL = <<<SQL1 
        SELECT 
            1 
        FROM users 
        WHERE 
            email = :checkemail 
    SQL1;

    $insertSQL = <<<SQL2 
        INSERT INTO users (  
            email 
        ) VALUES ( 
         :insertemail 
        ) 
    SQL2;

    try 
    { 
        $s_ps = $db->prepare($checkSQL); 
        $s_ps -> bindValue(':checkemail',$_POST['email'],PDO::PARAM_STR);
        $s_ps -> execute(); 

        $checkRow = $s_ps->fetch(); 

        if($checkRow) 
        { 
            die("This email address is already registered"); 
        } 

        // An INSERT query is used to add new rows to a database table. 
        // Again, we are using special tokens (technically called parameters) to 
        // protect against SQL injection attacks. 

        $i_ps=$db -> prepare($insertSQL);
        $i_ps -> bindValue(':insertemail',$_POST['email'],PDO::PARAM_STR);
        $i_ps -> execute();

    } 
    catch(PDOException $ex) 
    { 
        die("Failed to run query: ".$ex->getMessage()); 
    } 

    // This redirects the user back to the login page after they register 
    header("Location: login.php"); 

    // Calling die or exit after performing a redirect using the header function 
    // is critical.  The rest of your PHP script will continue to execute and 
    // will be sent to the user if you do not die or exit. 
    die("Redirecting to login.php"); 
} 

?> 
<h1>Register</h1> 
<form action="register.php" method="post"> 
E-Mail:<br /> 
<input type="text" name="email" value="" /> 
<br /><br /> 
<input type="submit" value="Register" /> 
</form>

Davey, thanks for that it's solved. Are you able to help me with my updated question? It's very similar (see above). Thank you!
You check for $login_ok to be true but you only ever assign it to be false. Put $login_ok=$row; just before the if ($login_ok).... Really, this should be asked as a new question - the original has been marked as accepted so it will show as such on the main SO page. If you ask a new question, I'll provide a longer answer for you

m2j · Accepted Answer · 2015-03-17 21:05:12Z

0

use

":email"

This might work...

answered Mar 17, 2015 at 21:05

m2j

1,1605 gold badges19 silver badges43 bronze badges

6 Comments

Nova Over a year ago

That works IF I input it into phpMyAdmin directly. However, if it's incorporated into my register.php code, it produces this error in my browser: Parse error: syntax error, unexpected ':' in /home/gather23/public_html/login/register.php on line 67

m2j Over a year ago

first save the variable email into php variable, like $email = 'Your email',.. then replace $email instead of :email

Nova Over a year ago

Won't this disable the SQL injection prevention function of :email ? $query_params = array( ':email' => $_POST['email'] );

m2j Over a year ago

$email = $_POST['email], then you can use ':email' => $email

Nova Over a year ago

Sorry, I'm a little confused. I've replaced $query_params = array( ':email' => $_POST['email'] ); with $email = $_POST['email'] ':email' => $email but now my browser says Parse error: syntax error, unexpected '':email'' (T_CONSTANT_ENCAPSED_STRING) in /home/gather23/public_html/login/register.php on line 40

|

dbinns66 · Accepted Answer · 2015-03-17 21:34:16Z

-1

The issue is mysql doens't know what :email is... Try...

$email = mysql_real_escape_string($_POST['email']);
$query = " 
        INSERT INTO users (  
            email 
        ) VALUES ( 
         '$email' 
        ) 
    ";

edited Mar 17, 2015 at 21:34

answered Mar 17, 2015 at 21:04

dbinns66

7916 silver badges7 bronze badges

3 Comments

Nova Over a year ago

isn't :email the ideal method, because it's using a token? This prevents SQL injections I believe. It looks like your method would change that?

dbinns66 Over a year ago

Updated to use mysql_real_escape_string which should handle the injection issue...

DaveyBoy Over a year ago

This is using prepared statements in PDO. Using mysql_ anything is not recommended as that set of functions is deprecated

Collectives™ on Stack Overflow

Unable to add user to MySQL database using PHP, error code #1064

4 Answers 4

3 Comments

2 Comments

6 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

2 Comments

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related