Data is not being inserted into MySQL Database using PHP

Question

Hey guys I have been pulling my hair out trying to figure out why this php code isnt working. Basically I have the database and tables all set up correctly and I am trying to get the data filled in by the user to go to the mysql database.

I have been googling the problem for hours and have yet to find a solution. When I click submit on the page the page refreshes and looks like the data has submitted but nothing appears when I query the DB.

<form action="#" method="post">
        <div class="row">
            <h4>Select your school</h4>
            <p>If you can't find it, contact your administrator about signing your school up!</p>

            <div class="dropdown">
                <button class="btn btn-primary dropdown-toggle" type="button" data-toggle="dropdown">
                    Choose your school from the dropdown menu
                    <span class="caret"></span>
                </button>
                <select title="Select your School" name="School" id="schools">
                    <option value="1">University of Central Florida</option>
                    <option value="2">Seminole State College</option>
                    <option value="3">School of Hard Knocks</option>
                </select>
            </div>
        </div>
        <div class="row">
            <h4>Name</h4>
            <input class="form-group col-lg-4" id="first" name="first" type="text" placeholder="First">
            <input class="form-group col-lg-4" id="last" name="last" type="text" placeholder="Last">
        </div>
        <div class="row">
            <h4>Email Address</h4>
            <input class="form-group col-lg-8" id="email" name="email" type="text" placeholder="ex. Flava.Flav@netscape.com">
        </div>
        <div class="row">
            <h4>Password</h4>
            <input class="form-group col-lg-8" id="password" name="password" type="text" placeholder="ex. Hunter2">
        </div>
        <div class="row">
            <input id="submit" name="submit" type="submit" value="submit" class="btn btn-primary">
        </div>
    </form>
</div> <!-- /container -->

  <?php
  error_reporting(E_ALL);
  if (isset($_POST['submit']))
  {
  $firstName = -1;
  $lastName = -1;
  $email = -1;
  $password = -1;
  $school = -1;
  $dropdown_val = -1;

  $connect=mysqli_connect('localhost','root','yanni123','eventmanager');

  if(mysqli_connect_errno($connect))
  {
  echo 'Failed to connect';
  }

  $firstName = $_POST["first"];
  $lastName = $_POST["last"];
  $email = $_POST["email"];
  $password = $_POST["password"];
  $dropdown_val = $_POST["School"];

  mysqli_query($connect, "INSERT INTO users (idusers, firstName, lastName, password, emailAddress, school)
  VALUES (1, '$firstName', '$lastName', '$password', '$email', '$dropdown_val')");

  mysqli_close($connect);
  }
  ?>

add error handling after `mysqli_query` using `mysqli_error`. and post the error message — amdixon, Nov 28 '15 at 22:38
Check for errors. You are also open to SQL injections with this code. http://php.net/manual/en/mysqli.error.php Check the connection and the query. — chris85, Nov 28 '15 at 22:38
Ok I will add the error handling and let you know, I tried to do this before but I couldn't get it working. Also this code will never be online its for learning purposes, but I will definitely add the check for SQL injections once I get it working. — YanniGen, Nov 28 '15 at 22:40
*"but I will definitely add the check for SQL injections once I get it working"* - If you're trying to enter something like `John's Bar & Grill` or `Jim O'Neil`, you'll have no choice BUT to escape your data. Plus, make sure your columns' types are right and their lengths long enough to accomodate the data going in. — Funk Forty Niner, Nov 28 '15 at 22:51
and, if your `idusers` column is AUTO_INCREMENT, that'll be a problem. and if you have other constraints for other columns. — Funk Forty Niner, Nov 28 '15 at 22:53
@Fred-ii- I added error handling to the code, it returned "Error description: Duplicate entry '1' for key 'PRIMARY' " Turns out it is wrong with my table, Im using autoincrement for the for idusers. How do I leave the value for idusers blank in the query so that it doesnt conflict with an existing entry? I though AUTO_INCREMENT was supposed to increment when the db encountered a conflicting id? Drew, Sorry about the bootstrap tag, first post. Wont happen again. — YanniGen, Nov 28 '15 at 23:07
I had a feeling that's what the error would be. You can do this `VALUES ('', '$firstName',` or just leave it right out along with the `idusers` column. The AI will happen on its own. and no, it will not AI it with the conflict. Not the way you have it now. — Funk Forty Niner, Nov 28 '15 at 23:08
@Fred-ii- solves another one with no error reporting, the powers of Fred are strong. — chris85, Nov 28 '15 at 23:12
@Drew wheres the second order injection? Isn't this a first level injection? — chris85, Nov 28 '15 at 23:12
the insert stmt, a little time bomb to occur later, first random ref I found [HERE](https://haiderm.com/second-order-sql-injection-explained-example/) — Drew, Nov 28 '15 at 23:13
@Fred-ii- Thanks for the help, it is working now. I will be adding the sql injection checks as well as you are right even though the code will never be online those characters you mentioned will definitely screw things over. — YanniGen, Nov 28 '15 at 23:17
@YanniGen You're welcome. I posted an answer below if you wish to accept it, in order to mark the question as solved. You're under no obligation, but it will inform the community that the question was solved. *Cheers* — Funk Forty Niner, Nov 28 '15 at 23:19
@chris85 Yeah, had a feeling about that `1` value in there, being an AI'd column. *Spidey sense tingle*. — Funk Forty Niner, Nov 28 '15 at 23:21

score 1 · Accepted Answer · edited May 23 '17 at 12:09

Taken from my comments:

"and, if your idusers column is AUTO_INCREMENT, that'll be a problem. and if you have other constraints for other columns."

OP:

"@Fred-ii- I added error handling to the code, it returned "Error description: Duplicate entry '1' for key 'PRIMARY' " Turns out it is wrong with my table, Im using autoincrement for the for idusers. How do I leave the value for idusers blank in the query so that it doesnt conflict with an existing entry? I though AUTO_INCREMENT was supposed to increment when the db encountered a conflicting id?"

Use the following and either remove both 1st entries, or use '' as the VALUE for the idusers AI'd column.

mysqli_query($connect, "INSERT INTO users (idusers, firstName, lastName, password, emailAddress, school)
            VALUES ('', '$firstName', '$lastName', '$password', '$email', '$dropdown_val')")
            or die(mysqli_error($connect));

The AI will happen on its own. and no, it will not AI it with the conflict. Not the way you have it now.

Bonus answer:

Your present code is open to SQL injection. Use mysqli_* with prepared statements, or PDO with prepared statements.

Passwords

I also noticed that you may be storing passwords in plain text. This is not recommended.

Use one of the following:

CRYPT_BLOWFISH
crypt()
bcrypt()
scrypt()
On OPENWALL
PBKDF2
PBKDF2 on PHP.net
PHP 5.5's password_hash() function.
Compatibility pack (if PHP < 5.5) https://github.com/ircmaxell/password_compat/

Data is not being inserted into MySQL Database using PHP

1 Answers1