Error while trying to insert with PHP

Question

I’m trying to run this function in php:

$insert = $con ->prepare("INSERT INTO conta (conta, senha, email, acesso) VALUES(?, ?, ?, ?)");
        $insert ->bind_param("sssi", $usuario->getConta(), md5($usuario->getSenha()), $usuario->getEmail, 1);

        $insert ->execute();

However when I use the following function:

$insert = $con ->prepare("INSERT INTO conta (conta, senha, email) VALUES(?, ?, ?)");
        $insert ->bind_param("sss", $usuario->getConta(), md5($usuario->getSenha()), $usuario->getEmail);

        $insert ->execute();

It works but when I try to insert this whole number at the end it does not run and shows the following error message: Fatal error: Cannot pass Parameter 5 by Reference in C: xampp htdocs payment lib php usuario.php on line 11

I don’t know what to do.

In my bank all are varchar except the last which is an int (11)

Answer 1

Apparently is using Mysqli, nay PDO, not separate things and that can confuse who comes to answer, since they have great differences. If you mean using object-oriented programming it would be POO or OO, but not PDO.

Now let’s get down to the problem:

The mysqli_stmt_bind_param (or $mysqli_stmt->bind_param) accepts variables by reference, but does not accept string directly in it.

$insert = $con->prepare("INSERT INTO conta (conta, senha, email, acesso) VALUES(?, ?, ?, ?)");

// Pode definir antes do bind_param:
//$numero = 1;

$insert ->bind_param("sssi",
    $conta,
    $senha,
    $email,
    $numero
);

// Pode definir depois do bind_param, mas antes do execute:
$numero = 1;

$senha = md5( $usuario->getSenha() );
$email = $usuario->getEmail();
$conta = $usuario->getConta();

$insert ->execute();

Now the 1 is being passed by $numero, the value of the $numero may be defined before the execute();, but not necessarily before the bind_param.

The first argument of bind_param, the "sssi", shall be determined as follows::

+-------+-------------------------------+----------------------------------------+
| Letra |          Descricao            |                Exemplo                 |
+-------+-------------------------------+----------------------------------------+
| i     | Define variável como Inteiro  | INT, TIMESTAMP, BIT...                 |
| s     | Define variável como String   | CHAR, VARCHAR, TEXT, DATETIME, JSON... |
| d     | Define variável como Double   | DOUBLE, FLOAT, DECIMAL...              |
| b     | Define variável como Blob     | BLOB, BINARY...                        |
+-------+-------------------------------+----------------------------------------+

The definition should be exactly in the order it is in query with the parameters, if set si must inform a variable being $string, $int.

I do not recommend using the MD5 or SHA1 for passwords. Use instead the Bcrypt which is already included in PHP in the functions of password_hash() if you really want different password protection systems there is a Libsodium (that supports the SCrypt and also the Argon2), the Libsodium is now available, using the \Sodium\crypto_pwhash_str(), this will be included "already factory" in PHP 7.2.