Encrypt/ Decrypt MYSQL/ PHP autoincrement ID

Question

I need to encrypt/decrypt the original Mysql ID printed in HTML, to later revert and receive the original ID in the back end.

Ex:

$var = $arr['id']; // 120

<table>
  <tr>
    <td>Logradouro tal e tal, 123</td>
    <td> <button class='excluirRegistro' data-id='<?php echo $var; ?>'>Excluir</button> </td> // data-id='120'
  </tr>
</table>

I’d like to encrypt for something like:

$varCrypt = funcao( $var, 'crypt' ); // 012980123324312

<table>
  <tr>
    <td>Logradouro tal e tal, 123</td>
    <td> <button class='excluirRegistro' data-id='<?php echo $var; ?>'>Excluir</button> </td> // data-id='012980123324312'
  </tr>
</table>

Later receive in variable:

$id = $_GET['idregistro']; // 012980123324312
$id = funcao( $id, 'decrypt' );
$id = "120"; // ID original

The scenario is as follows, a table with the address records registered by the user, with a related button to edit and delete the record.

Answer 1

Not to be rude, but already being: if you can decrypt something, your encryption algorithm sucks anyway.

Today’s most secure algorithms are one-way. That means you can’t go back. Once encrypted, you can’t "decrypt".

What the applications do is compare the hash of what is encrypted, if you give a match okay, if I’m not sorry.

What you can do, at most, is encrypt the ID that is being sent in the form. The generated hash will be compared to the hash stored in the database and then you do what you want. Remember that you have to use the same encryption functions.

If you have stored the ID in the database with SHA256, you must encrypt the form with SHA256.

In PHP:

<?php

$hash_id_do_formulario = hash("sha256", $_POST['id_do_formulario']);

// Procura no banco de dados pelo mesmo hash depois
$query = "DELETE FROM minha_tabela WHERE ID = :hash_formulario";

$statement = $minha_conexao_pdo->prepare($query);

$statement->bindParam(':hash_formulario', $hash_id_do_formulario);

$statement->execute();

Search by function hash in PHP.

Description of PHP documentation:

string hash ( string $algo , string $data [, bool $raw_output = FALSE ] )

Answer 2

To correct marked response has some errors, in my opinion, that I decided to give another answer.

---

The mt_rand() (and the str_shuffle()) is predictable, a person with access to some answers may be able to get the used Seed, for example using the Untwister or the PHP_MT_SEED Cracker, out other several ways.

Your code is public, so something like (date('s') + date('i') * 10000) is useless, after all the chances of our watches being different is despicable.

The strrev() will not add any security, this is worse than the Cesar Cipher, because she at least had "some key".

When you run the SELECT, as SELECT * FROM tabela WHERE token = $token, you will be clearly vulnerable to timing-Attack, a user will be able to obtain information through the time consumed to run queries.

---

A solution would be to create two identifiers and search for them, one of them being compared securely with the hash_equals.

Create two identifiers:

$pesquisar = random_bytes(8);
$confirmar = random_bytes(24);
$confirmar_hashed = hash("sha256", $confirmar, true);

Then insert it into the database, like:

INSERT tabela (`pesquisar`, `confirmar`) VALUES ($pesquisar, $confirmar_hashed)

The user must receive the values of $pesquisar and $confirmar, whereas this is passed in URL, it would be like:

seusite.com/?pesquisar=$pesquisar&confirmar=$confirmar

Remembering that both should be unique, the $pesquisar has the same capacity as an int64, or BIGINT in the case of Mysql.

Search for value:

To search, use the pesquisar as the main parameter, such as:

SELECT * FROM tabela WHERE pesquisar = $pesquisar

Then compare the confirmar using the hash_equals, as:

$stmt = $mysqli->prepare('SELECT confirmar FROM tabela WHERE pesquisar = ?')
$stmt->bind_param("s", $_GET['pesquisar']);

$stmt->execute();
$stmt->bind_result($confirmar_hashed);
$stmt->fetch();

if(hash_equals($confirmar_hashed, hash("sha256", $_GET['confirmar'], true)) === false){
    echo 'Valor de "confirmar" é incorreto';
}

For me this would be minimally safe. Of course, assuming you are using a secure operating system, the random_bytes props on the /dev/urandom.

EDIT (2020-08-22): After reviewing, I noticed that it was not assumed that there could be a "read-only" attack. I modified the answer to assume this possibility. Added use of the hash("sha256", ...) to prevent a read-only attack, such as a "Read-Only SQL Injection". This assumes that the attacker has some read access to the databases, but not editing. In this scenario and with the use of hash, the attacker will only have access to data that is the result of a hash (the $confirmar_hashed), therefore it will not have the original value (the $confirmar). In the previous version, and under these conditions, the attacker would have access to the codes, normally.

Answer 3

There are scenarios where obfuscating the ID can make sense. Sequential Ids can reveal much of your data set. An example is the Integrated Store, which shows the purchase ID at the end. It is a sequential number that reveals the amount of purchases already made in a specific store. A competitor could make small purchases over time to find out how the sales of the competing store are going.

Another example would be a URL with the client ID. Someone with access to the system could be "playing around" with the Ids to know how many customers there are in the database.

This post describes in more detail: Auto-Incrementing Ids: Giving your Data Away

In addition, it shows solutions to obfuscate Ids in some languages, among them PHP: Tiny

Note that it obviously needs to be something reversible, otherwise you won’t be able to get the ID back yourself and update some information based on it.