Inserting form data into a DB and verifying email doesn't already

  • (2 Pages)
  • +
  • 1
  • 2

17 Replies - 477 Views - Last Post: 24 October 2017 - 12:09 PM Rate Topic: -----

#1 williamscel  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 42
  • Joined: 25-October 08

Inserting form data into a DB and verifying email doesn't already

Posted 22 October 2017 - 04:29 PM

Hello.
Im trying to insert form data into by members table in my database. Im trying to
establish a conenction with the db
accept form data
on submit check it the email is already in the db
if it is give an error msg and return to form.
if email doesnt exist add data from form to db.

also i wanted to use a wraping selection field (with up and down arrows) for the age field.

now i have an error at the closing html line. ive been stating at this code for hours and now need to ask for help.

<?php
//session_start();
if (isset($_POST['Submit'])){   
 //connect to server and select database
 $mysqli = mysqli_connect("localhost", "cs213user", "letmein", "testDB");
  if(!$mysqli)
     {
      die("Connection failed: " . mysqli_connect_error());
     }
   
$f_name = ($_POST['fname']);
$l_name = ($_POST['lname']);
$email = ($_POST['email']);
$password = ($_POST['password']);
$age = ($_POST['age']);
$gender = ($_POST['gender']);

//remove any whitespace and convert characters to lowercase  
   $email =    strtolower(trim($_POST['email'])); 
    
         
   $result = mysql_query("SELECT 'email' FROM 'members' WHERE 'email' = '".$_POST['email']."'") or exit(mysql_error());    
   $num_rows = mysql_num_rows($result);     
    if($num_rows > 0) 
    { 
        $message = "Error: email already exists."; 
    } 
    elseif($num_rows == 0) 
    { 
      $sql = "INSERT INTO users (firstname, lastname, email, password, age, gender, startdate)
       VALUES ('".$_POST["f_name"]."','".$_POST["l_name"]."','".$_POST["email"]."', '".$_POST["password"]."', '".$_POST["age"]."', '".$_POST["gender"]."', '".Now()."')";

        //$execute = mysql_query($query); 
         
        //$message = 'Success: email has been recorded'; 
   } 

    //echo $message;   

?>

<html>
    <head>
    <title>Create An Account</title>
    </head>
    <body style="background-color: bisque">
    <form method="post" action="">
    <fieldset> 
        <legend><h3> User Information </h3></legend>
         <strong>First Name:</strong>
         <br/>
         <input type="text" name="fname"/>
         <br/>
         <strong>Last Name:</strong>
         <br/>
         <input type="text" name="lname"/><br/>
         <strong>Email:</strong><br/>
         <input type="test" name="username"/><br/>
         <strong>Password:</strong><br/>
         <input type="password" name="password"/><br/>
         <strong>Age:</strong><br/>
         <select>
         <?php
            for ($i=18; $i<100; $i++)
            {
                ?>
                    <option value="<?php echo $i;?>"><?php echo $i;?> </option>
                <?php
            }
         ?>
         </select><br/>
         <strong>Gender:</strong><br/>
         <input type="radio" name="gender" value="male"> Male
         <input type="radio" name="gender" value="female"> Female<br/><br/>
         <input type="submit" name="create" value="Create Account"/>
     </fieldset>
    </form>       
    </body>
</html>


This post has been edited by williamscel: 22 October 2017 - 04:40 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Inserting form data into a DB and verifying email doesn't already

#2 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2330
  • View blog
  • Posts: 9,378
  • Joined: 03-December 12

Re: Inserting form data into a DB and verifying email doesn't already

Posted 22 October 2017 - 04:32 PM

PDO is preferred.
Prepared statements.
Add a unique flag to the email column on the table.


What is the error?
Was This Post Helpful? 0
  • +
  • -

#3 williamscel  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 42
  • Joined: 25-October 08

Re: Inserting form data into a DB and verifying email doesn't already

Posted 22 October 2017 - 04:59 PM

View Postastonecipher, on 22 October 2017 - 05:32 PM, said:

PDO is preferred.
Prepared statements.
Add a unique flag to the email column on the table.


What is the error?

the actually is no error. but it does do anything either. (that just the worst kind of error i think, sigh)
when i submit the form it comes back blank and when i check the db the record wasnt added.
whats PDO. ive done some reading on prepared statements but tried implementing it and failed
Was This Post Helpful? 0
  • +
  • -

#4 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2330
  • View blog
  • Posts: 9,378
  • Joined: 03-December 12

Re: Inserting form data into a DB and verifying email doesn't already

Posted 22 October 2017 - 05:03 PM

PDO is the DB library most commonly used in PHP.

How did you try to implement prepared statements?

Do you have error reporting turned on?
Was This Post Helpful? 0
  • +
  • -

#5 benanamen  Icon User is offline

  • D.I.C Head

Reputation: 17
  • View blog
  • Posts: 131
  • Joined: 28-March 15

Re: Inserting form data into a DB and verifying email doesn't already

Posted 22 October 2017 - 06:23 PM

Your code is dangerous obsolete junk and will not work at all in current versions of Php. There is no "fixing" this code.

You need to use PDO with prepared statements. Here is a tutorial to get you going. PDO

If you still have problems once you start using PDO come on back and we will help you out.

This post has been edited by benanamen: 22 October 2017 - 06:24 PM

Was This Post Helpful? 1
  • +
  • -

#6 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3714
  • Posts: 13,467
  • Joined: 08-August 08

Re: Inserting form data into a DB and verifying email doesn't already

Posted 22 October 2017 - 06:23 PM

Read this twice, then try out the things it talks about.
Was This Post Helpful? 0
  • +
  • -

#7 williamscel  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 42
  • Joined: 25-October 08

Re: Inserting form data into a DB and verifying email doesn't already

Posted 22 October 2017 - 09:34 PM

I did some work on the code and this is what i have. i get a 500 error when i post. also im trying to include a php file that has the con to the db. why is it that when i use the include command i get a redirect to that page in the url along with redirect error?
<?php
include 'userlogin.php';
if(isset($_POST["signup"])) {
   
    //remove any whitespace and convert characters to lowercase  
   $email = strtolower(trim($_POST['email'])); 

    $sql = $db->prepare('SELECT email FROM members WHERE email=?');
    if (!$sql) {
        die("Database error: $db->error");
    }
    $sql->bind_param("s", $email);
    $sql->execute();
    if ($sql->num_rows) {
        echo "User already exists<br/>";
    } else {
        $query = $db->prepare("INSERT INTO members (firstname, lastname, email, password, age, gender) VALUES(?, ?, ?, ?, ?, ?)");
        $query->bind_param('ssssss', $f_name, $l_name, $email, $password, $age, $gender);
        $query->execute();
        echo "Account created";
    }
}
?>


Heres the form:
<html>
    <head>
    <title>Create An Account</title>
    </head>
    <body style="background-color: bisque">
    <form method="post" action="">
    <fieldset> 
        <legend><h3> User Information </h3></legend>
         <strong>First Name:</strong>
         <br/>
         <input type="text" name="f_name"/>
         <br/>
         <strong>Last Name:</strong>
         <br/>
         <input type="text" name="l_name"/><br/>
         <strong>Email:</strong><br/>
         <input type="test" name="email"/><br/>
         <strong>Password:</strong><br/>
         <input type="password" name="password"/><br/>
         <strong>Age:</strong><br/>
         <select>
         <?php
            for ($i=18; $i<100; $i++)
            {
                ?>
                    <option name="age" value="<?php echo $i;?>"><?php echo $i;?> </option>
                <?php
            }
         ?>
         </select><br/>
         <strong>Gender:</strong><br/>
         <input type="radio" name="gender" value="male"> Male
         <input type="radio" name="gender" value="female"> Female<br/><br/>
         <input type="submit" name="signup" value="Create Account"/>
     </fieldset>
    </form>       
    </body>
</html>



View Postbenanamen, on 22 October 2017 - 07:23 PM, said:

Your code is dangerous obsolete junk and will not work at all in current versions of Php. There is no "fixing" this code.

You need to use PDO with prepared statements. Here is a tutorial to get you going. PDO

If you still have problems once you start using PDO come on back and we will help you out.


thanks so much for the link. i did some reading and refactored the code. i do get a 500 error when i post. not quite sure why.

This post has been edited by williamscel: 22 October 2017 - 09:32 PM

Was This Post Helpful? 0
  • +
  • -

#8 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3714
  • Posts: 13,467
  • Joined: 08-August 08

Re: Inserting form data into a DB and verifying email doesn't already

Posted 23 October 2017 - 04:15 AM

Make sure your variable types are correct. For example, is $age really a string?
Was This Post Helpful? 0
  • +
  • -

#9 benanamen  Icon User is offline

  • D.I.C Head

Reputation: 17
  • View blog
  • Posts: 131
  • Joined: 28-March 15

Re: Inserting form data into a DB and verifying email doesn't already

Posted 23 October 2017 - 10:32 AM

Good job on ditching the obsolete code.

It appears you went for Mysqli instead of PDO. I highly recommend you use PDO. While you are trying to get your code working I suggest you put all the Php code in the same page and separate it later.

So, let's address some of the other issues you still have.

The Form
  • While not a problem, you should just completely leave out the form action altogether.

  • Again, not a problem, but I find this a cleaner option for the age dropdown...

<?php 
foreach (range(18, 100) as $age){
echo "<option name='age' value='$age'>$age</option>\n";
}
?>


The Code
Depending on the name of a button to be submitted for your script to work will completely fail in certain circumstances.The proper foolproof method is to check the Request Method.
if ($_SERVER['REQUEST_METHOD'] == 'POST'){
//Process Form
}


  • Outputting internal system errors is a security risk. It is of no use to the user and of great use to a hacker. Don't do it.

  • Providing a hard message that a specific email already exists is also a security issue. Don't do it.

  • Your logic for inserting the data is wrong. The correct method is to set a unique key on the email column and then attempt the insert and capture the duplicate error if any. Your method creates a race condition wherein two or more concurrent submissions will receive an "Ok" to insert. Every submission other than the first one that wins the race will create a duplicate.

  • You NEVER EVER store plaintext passwords. You need to use password_hash and password_verify.

Was This Post Helpful? 0
  • +
  • -

#10 ArtificialSoldier  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1828
  • View blog
  • Posts: 5,755
  • Joined: 15-January 14

Re: Inserting form data into a DB and verifying email doesn't already

Posted 23 October 2017 - 10:38 AM

You're not getting any of the form data either. When you run that database query $f_name, $l_name, $email, $password, $age, and $gender are undefined. You need to get the values from $_POST.

Those would be defined if the register_globals config option is set, but it's bad practice to rely on that option. It shouldn't ever be set.
Was This Post Helpful? 0
  • +
  • -

#11 williamscel  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 42
  • Joined: 25-October 08

Re: Inserting form data into a DB and verifying email doesn't already

Posted 23 October 2017 - 11:09 PM

View Postbenanamen, on 23 October 2017 - 11:32 AM, said:

Good job on ditching the obsolete code.

It appears you went for Mysqli instead of PDO. I highly recommend you use PDO. While you are trying to get your code working I suggest you put all the Php code in the same page and separate it later.

So, let's address some of the other issues you still have.

The Form
  • While not a problem, you should just completely leave out the form action altogether.

  • Again, not a problem, but I find this a cleaner option for the age dropdown...

<?php 
foreach (range(18, 100) as $age){
echo "<option name='age' value='$age'>$age</option>\n";
}
?>



The Code
Depending on the name of a button to be submitted for your script to work will completely fail in certain circumstances.The proper foolproof method is to check the Request Method.
if ($_SERVER['REQUEST_METHOD'] == 'POST'){
//Process Form
}


  • Outputting internal system errors is a security risk. It is of no use to the user and of great use to a hacker. Don't do it.

  • Providing a hard message that a specific email already exists is also a security issue. Don't do it.

  • Your logic for inserting the data is wrong. The correct method is to set a unique key on the email column and then attempt the insert and capture the duplicate error if any. Your method creates a race condition wherein two or more concurrent submissions will receive an "Ok" to insert. Every submission other than the first one that wins the race will create a duplicate.

  • You NEVER EVER store plaintext passwords. You need to use password_hash and password_verify.


thanks for all the pointers. Ive been fighting with this code for days. I only started learning php in September. the professor wants us to output a hard message if the email address exists, thats why i did it that way. i went back to 1 and did the php over with pdo.
i get an incorrect data int data type for age when i post.
I also have a unique constraint set on the email col in the db.
will i still need code to check if the email exists?
not very comfortable to prepared statements considering ive never seem them. we definitely not there as yet in class.
The php:
<?php
if($_SERVER['REQUEST_METHOD']== 'POST'){
$hostname='localhost';
$user='cs213user';
$pass='letmein';

try {
//Establish conn to database
$dbh = new PDO("mysql:host=$hostname;dbname=testDB",$user,$pass);
//PDO err handling
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

//remove any whitespace and convert characters to lowercase  
   $email = strtolower(trim($_POST['email'])); 
if ($dbh->query($sql > 0)) {
echo "Email already exist";
}
else{
  
//Insert form data in members table in testDB including hash password
$sql = "INSERT INTO members (firstname, lastname, email, password, age, gender)
VALUES ('".$_POST["f_name"]."','".$_POST["l_name"]."','".$_POST["email"]."','".password_hash($_POST["password"], password_default)."','".$_POST['age']."','".$_POST["gender"]."')";
}

//clear values for conn 
$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}

}
?>



The Form:
<html>
    <head>
    <title>Create An Account</title>
    </head>
    <body style="background-color: bisque">
    <form method="post" action="">
    <fieldset> 
        <legend><h3> User Information </h3></legend>
         <strong>First Name:</strong>
         <br/>
         <input type="text" name="f_name"/>
         <br/>
         <strong>Last Name:</strong>
         <br/>
         <input type="text" name="l_name"/><br/>
         <strong>Email:</strong><br/>
         <input type="test" name="email"/><br/>
         <strong>Password:</strong><br/>
         <input type="password" name="password"/><br/>
         <strong>Age:</strong><br/>
         <select>
         <?php
            foreach (range(18, 100) as $age){
            echo "<option name='age' value='$age'>$age</option>\n";
            }
          ?>
         </select><br/>
         <strong>Gender:</strong><br/>
         <input type="radio" name="gender" value="male"> Male
         <input type="radio" name="gender" value="female"> Female<br/><br/>
         <input type="submit" name="signup" value="Create Account"/>
     </fieldset>
    </form>       
    </body>
</html>


This post has been edited by williamscel: 23 October 2017 - 11:13 PM

Was This Post Helpful? 0
  • +
  • -

#12 andrewsw  Icon User is online

  • the case is sol-ved
  • member icon

Reputation: 6375
  • View blog
  • Posts: 25,758
  • Joined: 12-December 12

Re: Inserting form data into a DB and verifying email doesn't already

Posted 23 October 2017 - 11:17 PM

Quote the exact error message, don't attempt to paraphrase.

It is the select element that needs a name, not all of the options. (Naming all the options the same probably creates an array of posted values.)
Was This Post Helpful? 0
  • +
  • -

#13 williamscel  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 42
  • Joined: 25-October 08

Re: Inserting form data into a DB and verifying email doesn't already

Posted 23 October 2017 - 11:27 PM

the err message is "SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'age' at row 1"
i correct that by naming the select element age. now it produces a empty query :
SQLSTATE[42000]: Syntax error or access violation: 1065 Query was empty

This post has been edited by andrewsw: 23 October 2017 - 11:37 PM
Reason for edit:: Removed previous quote, just press REPLY

Was This Post Helpful? 0
  • +
  • -

#14 andrewsw  Icon User is online

  • the case is sol-ved
  • member icon

Reputation: 6375
  • View blog
  • Posts: 25,758
  • Joined: 12-December 12

Re: Inserting form data into a DB and verifying email doesn't already

Posted 23 October 2017 - 11:41 PM

Print out the value of $sql so you can check it.

It is curious why age is in black ink above.

Note that you do not need to quote the previous post, there is a Reply button further down the page, or use the Fast Reply box.
Was This Post Helpful? 0
  • +
  • -

#15 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3714
  • Posts: 13,467
  • Joined: 08-August 08

Re: Inserting form data into a DB and verifying email doesn't already

Posted 24 October 2017 - 04:10 AM

NEVER put user data in a query. This:
$sql = "INSERT INTO members (firstname, lastname, email, password, age, gender)
VALUES ('".$_POST["f_name"]."','".$_POST["l_name"]."','".$_POST["email"]."','".password_hash($_POST["password"], password_default)."','".$_POST['age']."','".$_POST["gender"]."')";
}
defeats the purpose of prepared queries, making PDO or MySQLi just as vulnerable to SQL injection attacks as an old fashioned MySQL_* query.

This post has been edited by CTphpnwb: 24 October 2017 - 04:11 AM

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2