CODEWITHSUNDEEP
phppostgresql
neilthawani
neilthawani

Reputation: 45

PHP script not inserting into pg database

I am writing a basic PHP registration script with the following database:

DROP TABLE IF EXISTS authentication;
CREATE TABLE authentication (
    userID SERIAL UNIQUE REFERENCES users(userID),
    eMail TEXT PRIMARY KEY REFERENCES users(eMail),
    passwordSalt TEXT,
    hashedSalt TEXT,
    saltText TEXT,
    securityAnswer1 TEXT,
    securityAnswer2 TEXT, 
    securityAnswer3 TEXT
);

and the following registration script:

<?php
  include 'https.php';

  //Create a connection to the database. If the connection fails, kill the
  //process and issue an error message
  $conn = pg_connect("host=localhost user=myusername password=mypassword dbname=mydatabase");
  if (!$conn) {
    die("Failed to connect to database.");
  }

  //If the register button is pressed..
  if (isset($_POST['submit'])) {
    //Check to make sure password+confirmation match. If not, error out.
    if ($_POST['password'] != $_POST['confirm-password']) {
      echo 'ERROR: passwords do not match<br />';
      echo 'Return to <a href="registration.php">registration</a> page.<br />';
      break;
    }

    //Then check to make sure the username doesn't already exist in the DB.
    $result = pg_prepare($conn, "duplicateUser", 'SELECT username FROM mydatabase.users WHERE username = $1');
    $result = pg_execute($conn, "duplicateUser", array($_POST['username']));
    $row = pg_fetch_assoc($result);

    //If it already exists, set match to true.
    foreach ($row as $res) {
      if ($res == $_POST['username'])
        $match = true;
    }

    //If match is true, error out.
    if ($match == true) {
      echo 'ERROR: username already in use<br />';
      echo 'Return to <a href="registration.php">registration</a> page.<br />';
      $match = false;
      break;
    }
  }
?>

<html>
<head>
<title>Registration page</title>
</head>
<body>
<form method='POST' action='registration.php'>
Username (e-mail): <input type='text' name='username' /><br />
Password: <input type='password' name='password' /><br />
Confirm password: <input type='password' name='confirm-password' /><br />
<input type='submit' name='submit' value='Register' /><br />
</form>
Return to <a href='index.php'>login</a> page.<br />

</body>
</html>

<?php

  //If the registration button is pressed..
  if (isset($_POST['submit'])) {
    //Take the username and password, salt the password using an SHA1
    //encryption of a random number, concatenate the password to the
    //beginning of the salt, then save the SHA1 of that concatenation
    //as the hashed and salted password.
    $username = $_POST['username'];
    $confirmusername = $_POST['confirm-username'];
    $password = $_POST['password'];
    $confirmpassword = $_POST['confirm-password'];
    if ($username != $confirmusername)
        echo "Please make sure your username is consistent.";
    if ($password != $confirmpassword)
        echo "Please make sure your passwords match.";

    $salt = sha1(rand());
    $saltedPass = $password.$salt;
    $hashedSalt = sha1($saltedPass);

    //Store the username, hashed+salted password, and salt in the
    //authentication table.
    $result = pg_prepare($conn, "authentication", 'INSERT INTO mydatabase.authentication VALUES ($1, $2, $3)');
    $result = pg_execute($conn, "authentication", array($username, $salt, $hashedSalt));

    //Start the session, set the $_SESSION variables loggedin to true and
    //username to the supplied username, then redirect to index.php
    session_start();
    $_SESSION['loggedin'] = true;
    $_SESSION['username'] = $username;
    header("Location: index.php");
  }
?>

But when I get on PHPpgAdmin, the rows haven't been inserted into the database. Any ideas?

Thanks.

Upvotes: 1

Views: 1044

Answers (2)

Daniel V&#233;rit&#233;
Daniel V&#233;rit&#233;

Reputation: 61676

The code should test the result of pg_prepare and pg_execute, and show or log pg_last_error() on error. Otherwise you're in the dark on any problem.

Also, since it connects to a database named mydatabase and refers to tables as mydatabase.tablename, it can be reasonably suspected that you're applying this MySQL thing of prepending the database name to the table name. It doesn't apply to Postgres. The databases of MySQL are the equivalent of schemas in PostgreSQL.

So unless you have explicitly created a postgres schema named mydatabase, try without any prefix:

INSERT INTO authentication VALUES ($1, $2, $3)

This also apply to the previous SELECT that probably fails for the same reason, and it also lacks error checking.

Upvotes: 1

Sean Redmond
Sean Redmond

Reputation: 4114

First thought is that you need to explicitly commit the transaction. This answer seems to confirms that there is no autocommit with Postgres.

Upvotes: 1

Related Questions