Information not getting submitted to MySQL

Question

I am creating a simple login script using PHP and MySQL, no errors are coming up but for some reason the information submitted is just not being inserted into the database. The database is named 'test' (Without quotes) and the table 'users' (Also without quotes). The columns in the table are first_name, last_name, email, pass and registration_date. Here is the html form:

<form action="script4.php" method="post">
    <p>First Name:<input type="text" name="first_name" value="first_name" /></p>
    <p>Last Name:<input type="text" name="last_name" value="last_name" /></p>
    <p>Email: <input type="text" name="email" value="email" /></p>
    <p>Password: <input type="password" name="pass1" value="pass1" /></p>
    <p>Confirm Password: <input type="password" name="pass2" value="pass2"/></p>
    <input type="submit" name="submit" value="register" />
</form>

and here is script4.php

<?php
$first_name = $_POST['first_name'];
$last_name =  $_POST['last_name'];
$email =      $_POST['email'];
$pass1 =      $_POST['pass1'];
$pass2 =      $_POST['pass2'];

require ('mysql_connect.php');
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
$errors = array();}
if (!empty($_POST['first_name'])) {
$errors[] = "You forgot to enter your first name!";
} else {
$fn = trim($_POST['first_name']);
}
if (!empty($_POST['last_name'])) {
$errors[] = "You forgot to enter your first name!";
} else {
$ln = trim($_POST['last_name']);
}
if (!empty($_POST['email'])) {
$errors[] = "You forgot to enter your first name!";
} else {
$e = trim($_POST['email']);
}
if (!empty($_POST['pass1'])) {
if ($_POST['pass1'] != $_POST['pass2']) {
$errors[] = "Your passwords do not match.";
} else {
$p = trim($_POST['pass1']);}
}else {
$errors[] = "You forgot to enter your password."; 
}
if (empty($errors)) {
require ('mysql_connect.php');
$q = "INSERT INTO users ('first_name', 'last_name', 'email', 'pass',      'registration_date') VALUES ('$first_name', '$last_name', '$email', SHA1('$pass'), NOW()) or   trigger_error('Query Error: ' . mysql_error());";
$r = @mysqli_query ($dbc, $q);
if ($r) {
echo("Thanks");
} else {
echo("We are sorry, you could not be entered at this time.");
echo mysqli_error($dbc);
} }
mysqli_close($dbc);
?>

I know this script is vulnerable to sql injection, it is just a test:) The data will just not get submitted.

**By building SQL statements with outside variables, you are leaving yourself open to SQL injection attacks.** Also, any input data with single quotes in it, like a name of "O'Malley", will blow up your SQL query. Please learn about using parametrized queries, preferably with the PDO module, to protect your web app. My site http://bobby-tables.com/php has examples to get you started, and [this question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has many examples in detail. — Andy Lester, Jan 27 '14 at 22:43
I have a book, which is where the code is from, but it isn't working lol! — Fabian Whitaker, Jan 27 '14 at 22:45
Does the book say nothing about the difference between (`) and (')? — Strawberry, Jan 27 '14 at 22:48
What book are you using, @Strawberry? I'm curious what book tells you to build SQL statements from untrusted user data. — Andy Lester, Jan 27 '14 at 22:49

dev7 · Accepted Answer · 2014-01-27T22:48:30.003

Remove the single quotes from the column names.
You are calling require ('mysql_connect.php') twice.
You had multiple syntax errors.
You were assigning variables but not calling them.
You tried to add $pass to the database instead of $pass1.

I cleaned your code.

if ($_SERVER['REQUEST_METHOD'] == 'POST') { $errors = array();

$first_name = empty($_POST['first_name']) ? '' : trim($_POST['first_name']);;
$last_name  = empty($_POST['last_name']) ? '' : trim($_POST['last_name']);;
$email      = empty($_POST['email']) ? '' : trim($_POST['email']);;
$pass1 =      empty($_POST['pass1']) ? '' : trim($_POST['pass1']);
$pass2 =      $_POST['pass2'];

if (!$first_name) {
    $errors[] = "You forgot to enter your first name!";
} 

if (!$last_name) {
    $errors[] = "You forgot to enter your first name!";
} 

if (!$email) {
    $errors[] = "You forgot to enter your first name!";
} 

if ($pass1) {
    if ($pass1 != $pass2) {
        $errors[] = "Your passwords do not match.";
    } 
} else {
    $errors[] = "You forgot to enter your password."; 
}

if (empty($errors)) {
    require ('mysql_connect.php');
    $q = "INSERT INTO users (first_name, last_name, email, pass,registration_date) VALUES ('$first_name', '$last_name', '$email', SHA1('$pass1'), NOW()) or   trigger_error('Query Error: ' . mysql_error());";
    $r = @mysqli_query ($dbc, $q);
    if ($r) {
        echo("Thanks");
    } else {
        echo("We are sorry, you could not be entered at this time.");
        echo mysqli_error($dbc);
    } 
    mysqli_close($dbc);
} else { 
  foreach ($errors as $error) echo $error . '<br>';
}

}

?>

Also, it will be wise to escape the $_POST data or even better - use a prepared statements as currently, you are volunerable to SQL injection.

Hope this helps!

Still isn't inputting into the database, but thank you for pointing out a few things that I hadn't noticed:) — Fabian Whitaker, Jan 27 '14 at 22:36
@Fabian I also added a new error printing so please repost the errors if still not working. — dev7, Jan 27 '14 at 22:53

Jivan · Answer 2 · 2014-01-27T22:54:00.137

Remove the ! in all your conditional statements:

if (!empty($_POST['last_name']))

Means "if last_name is NOT empty", because of the !. Which means that your script currently says "error" if the fields are NOT empty. And if the scripts says "error", then in the end it doesn't insert the values in the database.

It doesn't say "we are sorry" too, because this statement is inside your conditional if(empty($errors)). So if $errors is not empty, you directly go to the end of the script without displaying anything, but witout having inserted your values.

So what you should do, for instance, is this:

if (empty($_POST['first_name'])) {
    $errors[] = "You forgot to enter your first name!";
} else {
    $fn = trim($_POST['first_name']);
}

And in the end:

if (empty($errors)) {
    require ('mysql_connect.php');
    $q = "INSERT INTO users (first_name, last_name, email, pass, registration_date) VALUES ($first_name, $last_name, $email, SHA1($pass), NOW());";
    if (@mysqli_query ($dbc, $q)) {
        echo("Thanks");
    } else {
        echo mysqli_error($dbc);
        echo("We are sorry, there is a problem with the database connection.");
    }
} else {
    echo("We are sorry, there are errors in the values you entered.");
}
mysqli_close($dbc);

As the others said, be careful because you have to remove one of your require('mysql_connect.php').

that code will produce an error because `$dbc` won't be defined in the else. — Class, Jan 27 '14 at 22:49

Class · Answer 3 · 2014-01-27T22:58:32.167

Remove the first require ('mysql_connect.php');

and change the following line to something like this because you got wrong syntax for your query and your trigger_error

$q = "INSERT INTO users (first_name, last_name, email, pass, registration_date) VALUES ('$first_name', '$last_name', '$email', SHA1('$pass'), NOW())";
$r = mysqli_query($dbc, $q) or trigger_error('Query Error: ' . mysqli_error($dbc));

Remove the @ and change mysql_error to mysqli_error with link otherwise you won't get your error.

if(empty($errors)) {
    require ('mysql_connect.php');
    $q = "INSERT INTO `users` (`first_name`, `last_name`, `email`, `pass`, `registration_date`) VALUES ('$first_name', '$last_name', '$email', SHA1('$pass'), NOW())";
    $r = mysqli_query ($dbc, $q);
    if($r){
        echo "Thanks";
    }else{
        echo "We are sorry, you could not be entered at this time.";
        trigger_error('Query Error: ' .  mysqli_error($dbc));
    }
    mysqli_close($dbc);
}

Also you should look into binding parameters so eliminate sql injections.

Are your sure your tables's name and column names are correct because they can be case sensitive. — Class, Jan 27 '14 at 22:48

Information not getting submitted to MySQL

3 Answers3