Encrypt/ Decrypt MYSQL/ PHP autoincrement ID

Asked

Viewed 1,821 times

-1

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.

  • 8

    There are many ways, but probably all wrong. In a normal application no one should need this, Probably if someone can use the ID to do something wrong, it is lack of checking the user permissions . Maybe it would help to edit the question and explain the real problem you’re trying to solve, instead of the way you thought it would.

  • If there’s anything I can do to help with the question, thank you. As I said, I need some way to encrypt the ID displayed in the HTML and decrypt the ID in the back-end.

  • 7

    The best help I think is to discourage wasting time with an outline that is probably not a real solution to the problem, for the reasons already mentioned. If the ID cannot be seen, it should not even be in HTML (either obfuscated or not). And probably not the case to hide, any basic application shows the Ids for deletion, editing, etc without any problem. But if it is, I’m really curious to know the application scenario, because the last questions that asked the same started from the wrong premise. If yours is different, it really must be an atypical scenario.

  • 5

    If you are using a secure connection (SSL) you are already encrypting. If it is something that no one can know should not be sent to a customer.

  • I improved the question, explained the scenario. Thank you!

  • 4

    It really is what I had thought, there is no need to hide anything in this scenario. Basically what you need is to check the permissions of who is doing the deletion and change operation on that record.

  • I will continue to use Cipher, I had no pretension to make use of more classes in the code, because I am paranoid with security, if something goes wrong, other than through my code, which is for server failures or other levels, rs.

  • I believe what you’re looking for is actually to use what they call UUID (Universal Unique Identifier), something similar to this token you decided to use. Take a look at the function UUID() in Mysql, you may find it more interesting to use it.

  • 1

    @Eliseub., what you want is to obfuscate the ID so as not to allow the user, for example, to derive information from your system, such as number of requests, etc. If that is so then, depending on the context, it is recommended: https://philsturgeon.uk/http/2015/09/03/auto-incrementing-to-destruction/

  • 2

    Gives a look here, can help you.

  • Ivan Ferrer, I know the majority, but I believe that everything was misinterpreted in the way I explained my question, but it was already, security in several layers implemented and working. Thanks for everything. Namaste.

Show 6 more comments

3 answers

5

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 ] )
  • Thank you for your opinion, but missed to read completely what you had requested, said encrypt, not encrypt, IE, mask only the ID so that in the back end can reverse and read the original ID, and it is mysql autoincrement, I know hash’s and encryption, but what I asked was to encrypt/mask and reverse. PS: I currently make use of 256bit Cipher and use public and private key and token to validate Encrypt/Decrypt, it’s not crap, but I would like something simpler as it’s not a complex project that requires so much security.

  • 3

    Sorry for YOUR ignorance. Encrypting and encrypting are synonymous, and encryption is a foreigner (of the word Encrypt, in English). EDIT: read the title of your post.

  • For a good understanding enough, nonsense waste your time with irrelevant comment, focus on collaboration and not on pins, the level is low.

  • @Eliseub. I’m not poking you. I don’t even know you! This isn’t personal. If your argument is technically flawed, you want me to corroborate?

  • No, ignore it and skip to the next "collaboration," as you said, nothing personal, but nothing relevant. And speaking of collaboration, is there any way to lock the question, or just delete it? I already solved my problem with the code and have nothing to add to other users in fact.

  • 3

    If you have already solved your problem, the ideal is to answer it right here. You can answer your own question and then mark it.

  • 2

    sorry but I believe your concept of cryptography is mistaken when you say "If you can decrypt anything, your encryption algorithm sucks anyway" There is encryption hash, whose goal is not to be reversed, but if it were only so, as you would in an encrypted communication channel, an ssl for example? you encrypt the message and then you can’t decrypt, you’ll never know what the other end sent you?

Show 2 more comments

3


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.

  • Useless in my application, it only makes use of more code, and besides having used a random number, I also made use as if, you must have read the whole conversation, the use of Cipher, with user session check and validation of token and registration id and login user id.

  • 1

    @Eliseub. Your code used a mt_rand (and the date, but I will ignore this one). The mt_rand is a mere PRNG is not a CSPRNG with is the random_bytes, both the /dev/urandom as to the /dev/random are a CSPRNG and it is used by such a function. The use of encryption, in my view, is useless. Except in two cases: it uses encryption with authentication (guaranteed by a MAC, preferably based on Carter-Wegman) and the key is in the user’s possession. Or, instead of the user having the key, you store it in a programmable HSM. Out of these two options, I see no advantage at all.

  • 3

    Your answer is interesting as theoretical exercise (so much so that has my up since posting) but here is really overengineering, there is even a real need. There is a "reply-comment" from Cantoni about one of the few plausible reasons to do what the OP said, but even adding a large fixed prime would solve to get the Ids out of sequence without generating significant computational effort. For anything much more complicated than that it makes no sense to even talk about autoincrement, as asked in the question.

  • @Bacchus , my answer was based on the reply of the Elisha, who was excluded. That seemed another context. I think in the context of Cantoni it seems that simply generating a random number solves, since the problem is being sequential. In the case of the Elysée (the answer) it seems to me something like "authentication". Particularly, I use something very close to what I wrote above for "forgot password", in this case the "random code" itself is security. On the computational cost, I think Blake2/Blake3 are fast enough.

  • 4

    Well, needless to say, answers should be based on the question, but somehow that doesn’t change what I said. It was just an observation even, in the case here my up remains taking into consideration the general aspect (tou taking into account the time that was posted). Although for the case of the question is an unreal solution, it can serve as a reference to other people by detailing (in the rarest - not to say almost nonexistent - cases where this is necessary).

1

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.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.