Properly creating a register function

Question

I am trying to create a website and I have a user database, in which I have the following functions:

function createUser($nome, $email, $morada, $username, $password) {
    global $conn;
    $stmt = $conn->prepare("INSERT INTO utilizador (nome, email, morada,  permissao, username, password)
                                                VALUES ('".$nome."', '".$email."', '".$morada."', '2', '".$username."', '".$password."')");
    $stmt->execute(array($nome, $morada, $contacto, $permissao, $username, $password));
}

function isLoginCorrect($username, $password) {
    global $conn;
    $stmt = $conn->prepare("SELECT * FROM utilizador WHERE username = '".$username."' AND password = '".$password."'");
    $stmt->execute();
    return $stmt->fetch() == true;
}

function getPermissao($username){
    global $conn;
    $stmt = $conn->prepare("SELECT permissao
                            FROM utilizador
                            WHERE username = '".$username."'"
                          );
    $stmt->execute(array($username));
    return $stmt->fetchColumn(0);
}

I am having troubles with the register:

    if (!$_POST['nome'] || !$_POST['email'] || !$_POST['morada'] || !$_POST['username'] || !$_POST['password']) {
          $_SESSION['error_messages'][] = 'Deve preencher todos os campos obrigatórios';
          //Guardar dados do formulário e redirecionar
          $_SESSION['form_values'] = $_POST;
          header("Location: $BASE_URL" . 'pages/users/register.php');
          exit;
        }

    // Variáveis post ( & Sanitize )
    $nome         = strip_tags($_POST['nome']);
    $contacto     = strip_tags($_POST['email']);
    $email       = strip_tags($_POST['morada']);
    $username     = strip_tags($_POST['username']);
    $password     = $_POST['password'];

    //Criar novo utilizador


  createUser($nome, $morada, $contacto, $username, $password);


        // Erros nos campos
        if (strpos($e->getMessage(), 'cliente_username_key') !== false) {
        $_SESSION['error_messages'][] = 'Ocorreu um erro ao efetuar o registo';
        $_SESSION['field_errors']['username'] = 'O Username introduzido já existe';
        }

    //Erro genérico
    else $_SESSION['error_messages'][] = 'Ocorreu um erro ao efetuar o registo';



    //Guardar dados do formulário & redirecionar
    $_SESSION['form_values'] = $_POST;
    header("Location: $BASE_URL" . 'pages/users/register.php');
    exit;


    // Registo efetuado com sucesso
    $_SESSION['success_messages'][] = 'Registo efetuado com sucesso';
    header("Location: $BASE_URL");

My database is correctly connected and I am able to login, but I can't register and I get the following error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[08P01]: <>: 7 ERROR: bind message supplies 6 parameters, but prepared statement "pdo_stmt_00000001" requires 0' in /usr/users2/mieec2012/ee12083/public_html/trabalhosSiem/trabalhoPHP2/database/users.php:7 Stack trace: #0 /usr/users2/mieec2012/ee12083/public_html/trabalhosSiem/trabalhoPHP2/database/users.php(7): PDOStatement->execute(Array) #1 /usr/users2/mieec2012/ee12083/public_html/trabalhosSiem/trabalhoPHP2/actions/users/register.php(27): createUser('cliente', NULL, 'iii@...', 'cliente', 'cliente') #2 {main} thrown in /usr/users2/mieec2012/ee12083/public_html/trabalhosSiem/trabalhoPHP2/database/users.php on line 7

Hey, your code is **wide open** to SQL injection attacks and **will be hacked** if it hasn't been already. You're doing your parameterized queries completely wrong. Never concatenate data directly into the query. Use parameters which are bound at or before execution. — Brad, Jan 14 '18 at 23:29
That's not how you use prepared statements, you may want to look for a tutorial on how to do it, and as @Brad mentioned above, your code is vulnerable. Also check this guide: https://stackoverflow.com/questions/549/the-definitive-guide-to-form-based-website-authentication — Spoody, Jan 14 '18 at 23:29
The point of prepared statements is not to have variables directly in the query. They should be question marks instead. The execute method should have an array with variables. — Jordi Kroon, Jan 14 '18 at 23:29

Ivan86 · Accepted Answer · 2018-01-15T00:02:00.513

Your problem is that you are making prepared statements without any placeholders yet you are sending in values on execute().

To make a proper PDO prepared statement you would do the following:

$stmt = $conn->prepare("INSERT INTO utilizador 
        (nome, email, morada,  permissao, username, password) 
 VALUES (:nome, :email, :morada, :permissao, :username, :password)");

and then execute it like this:

$stmt->execute(array('nome' => $nome, 'email' => $email, 'morada' => $morada, 'permissao' => '2', 'username' => $username, 'password' => $password));

Do this for all your statements.

Don't add variables directly to the prepared statement, use placeholders instead and execute with the values later. You are otherwise prone to SQL injection!

PDO prepared statements:

The parameters to prepared statements don't need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible).

more here: http://php.net/pdo.prepared-statements.

Properly creating a register function

1 Answers1