MySQL query runs without error but does not populate database

Question

Evening folks,

Having another of those "looked at this too long moments".

This code when run return the success message but nothing gets entered into the database table, no errors being thrown up, I know all the correct values are being received from _post but I can't see what's wrong, I have an almost identical query on another page and it works fine.

Can anyone see issues with the code?

if (isset($_POST['username']) && $_POST['username'] !== '')
{

    $salted = md5($_POST['pass1'] . 'salt');

  try
  {
    $sql = 'INSERT INTO users SET 
      username = :username,
      firstname = :firstname,
      lastname = :lastname,
      email = :email,
      password = $salted,
      joined = CURDATE()';
    $s = $PDO->prepare($sql);
    $s -> bindValue(':username', $_POST['username']);
    $s -> bindValue(':firstname', $_POST['firstname']);
    $s -> bindValue(':lastname', $_POST['lastname']);
    $s -> bindValue(':email', $_POST['email']);
    $s -> execute();

  }
  catch (PDOException $e)
  {
    $error = 'Error adding submitted user.';
    echo $error;
    exit();
  }

  ?> <div class="alert alert-success">User added to the database.</div> <?php

}

Your value $salted needs to be single-quoted as a string '$salted'. Although the MD5 hash is inherently injection-safe, you might as well just bind it as a parameter like all the others. — Michael Berkowski
– Michael Berkowski, Commented Oct 6, 2013 at 18:22
@CORRUPT The INSERT INTO ... SET is valid in MySQL, though not commonly used. It's the second syntax example in the docs you linked. — Michael Berkowski
– Michael Berkowski, Commented Oct 6, 2013 at 18:23
Please know that MD5 is considered to be an extremely weak hash, not suitable for modern web applications. — Michael Berkowski
– Michael Berkowski, Commented Oct 6, 2013 at 18:24
Did you actually configure PDO to throw exceptions? By default it does not - in fact it errors silently by default. — Michael Berkowski
– Michael Berkowski, Commented Oct 6, 2013 at 18:25
In the future, to see errors from PDO try print_h($s->errorInfo()); — octern
– octern, Commented Oct 6, 2013 at 18:34

Bill Karwin · Accepted Answer · 2013-10-06 19:09:50Z

1

Summarizing comments here, for the sake of having an answer. Marked Community Wiki.

A string in your INSERT statement should be quoted.
```
  password = '$salted',
```

You should use a parameter anyway.

  password = :password,

  . . .

  $s -> bindValue(':password', $salted);

MD5 isn't the preferred hashing function for modern strong password storage. Neither is SHA1.
Try to use SHA256 or Bcrypt instead.
```
$salted = hash('sha256', $_POST['pass1'] . 'salt');
```
Salting is better if you use a random salt string per user.

Make sure your PDO instance is configured to throw exceptions.

$PDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Always capture the PDO exception's error message, even if you don't output it to users.
```
error_log($e->getMessage());
```

answered Oct 6, 2013 at 19:09

community wiki

Bill Karwin

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

1 Comment

Holo Over a year ago

Thanks for that Bill. Much appreciated. Had I not left a 10 year gap in my PHP/MySQL knowledge things would be so much easier! :)

Collectives™ on Stack Overflow

MySQL query runs without error but does not populate database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related