-1

I'm working on a university project using MySQL and PHP, a number of errors have been occurring. The first of which was being unable to connect to the database which I've resolved by inputting the database's name. This is supposed to be a registration form for a website. The data is now not being stored in the database, but I cannot see what is causing this - or there is another error occurring which I am unaware of. Connecting to the database:

<?php
  $db = new mysqli('(database name)', 'localhost', 'root', '', '09');
  if($db->connect_errno > 0) {
    die('Unable to connect to database [' . $db->connect_error . ']');
  }
?>

Registration:

  require_once '../../includes/db-connect.php';

  //get data from POST
  $fname = filter_var($_POST['fname']);
  $lname = filter_var($_POST['lname']);
  $dob = filter_var($_POST['dob']);
  $email = filter_var($_POST['email']);
  $password = filter_var($_POST['password']);
  $confirmpassword = filter_var($_POST['confirmPassword']);

  $sql = 'INSERT INTO users ' .
    '(`fname`, `lname`, `dob`, `email`, `password`)' .
    'VALUES ' .
    '("' . $db->escape_string($fname) .
    '", "' . $db->escape_string($lname) .
    '", "' . $db->escape_string($dob) .
    '", "' . $db->escape_string($email) .
    '", "' . $db->escape_string($password) .'")';

  $result = mysqli_query($db, $sql);

  //if the user is successfully entered into the table, redirect to login page
  if($result) {
    header('Location: /ct4009_2/user/login/login.php');
    die();
  }

  die('There was an error inserting user data into the database');
 ?>

Finally, the sql query I run to propagate database tables:

CREATE TABLE `users` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `profilePic` varchar(255) NOT NULL,
  `fname` varchar(255) NOT NULL,
  `lname` varchar(255) NOT NULL,
  `dob` date NOT NULL,
  `deleted` INT DEFAULT 0
);

CREATE TABLE `posts` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `poster_id` INT NOT NULL,
  `date` varchar(255) NOT NULL,
  `post_body` varchar(1000) NOT NULL,
  `img_path` varchar(255),
  `comments` varchar(1000),
  `likes` varchar(1000),
  `deleted` INT DEFAULT 0
);
Matthew
  • 49
  • 1
  • 1
  • 4
  • 1
    **Warning:** You are wide open to [SQL Injections](http://php.net/manual/en/security.database.sql-injection.php) and should really use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](http://php.net/manual/en/pdo.prepared-statements.php) or by [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Never trust any kind of input, especially that which comes from the client side. Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). – Dharman Apr 20 '19 at 19:03
  • 2
    **Never store passwords in clear text or using MD5/SHA1!** Only store password hashes. Use PHP's [`password_hash()`](http://php.net/manual/en/function.password-hash.php) and [`password_verify()`](http://php.net/manual/en/function.password-verify.php) . If you're running a PHP version lower than 5.5 (which I really hope you aren't), you can use the [password_compat](https://github.com/ircmaxell/password_compat) library to get the same functionality. – Dharman Apr 20 '19 at 19:03
  • @Dharman: Please show the "wide open to SQL Injections", I don't see it. All texts are escaped. Really, it doesn't help to post a standard text without reason. – Wiimm Apr 20 '19 at 19:17
  • 1
    @Wiimm Maybe I should have said, "You might". Either way escaping the input is not the best way to create SQL statements. The links I provided should teach OP how to create SQL statements properly. Until prepared statements are used, we cannot rule out that the code is safe. – Dharman Apr 20 '19 at 19:21
  • Note: The [object-oriented interface to `mysqli`](https://www.php.net/manual/en/mysqli.quickstart.connections.php) is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface where missing a single `i` can cause trouble. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is largely an artifact from the PHP 4 era when `mysqli` API was introduced and should not be used in new code. – tadman Apr 20 '19 at 20:23
  • @Wiimm "All texts are escaped" is something that takes careful attention to determine, you must read every line of code to ensure that they actually are. Even if they were escaped once there's a chance they get reverted to their unescaped form accidentally. A prepared statement requires no such deep searching, it's immediately obvious if it's correct or not. This is why it's extremely important to use them. That and it saves a ton of time in tracking down escaping bugs. – tadman Apr 20 '19 at 20:25
  • In general I like "use prepared..." hints -- they are really necessary for many code fragments posted here. I criticized only "You are wide open to SQL Injections" because it is wrong here. – Wiimm Apr 20 '19 at 21:40
  • Change `$result = mysqli_query($db, $sql);` to `$result = mysqli_query($db, $sql) or die(mysqli_error($db));` to find out what the problem was with your query. – Nick Apr 21 '19 at 05:38

1 Answers1

1

In your table def I find this member:

 `profilePic` varchar(255) NOT NULL,

It has no default, and NULL is not allowed. In your INSERT you don't define a value for it. Are you sure, that no error is thrown?

Wiimm
  • 2,971
  • 1
  • 15
  • 25