1

Would you help me to fix this, please:

SELECT
  (SELECT AES_DECRYPT(cryptoword, SHA2('DatabaseEncryption1', 512)) FROM file_tree) AS cryptoword1,
  (SELECT AES_DECRYPT(name, SHA2(cryptoword1, 512)) FROM file_tree) AS name;

As the topic says, I get error saying that my subquery returns more than 1 row. What I look for to achieve is:

  1. Get the cryptoword for the particular database record.
  2. Use that cryptoword to decrypt the rest of the record.
  3. Repeat the process for all the table records/multiple records satisfying WHERE condition, which I can add later

My query works, if I use the query for one record only. However, I need to get multiple records within from the table. Every record has its own cryptoword, which is different per each row. My task is therefore to get the cryptoword for particular record, and to use that one to decrypt the rest of the record. I need to repeat this process for all the table records.

Because of performance reasons, it all needs to be formatted within one query.

Thank you in advance.

Improve this question
11
  • 1
    You say for the particular database record yet you aren't filtering your results at all. You need a WHERE clause on both of your subqueries which will guarantee that only 1 record is returned. Commented Mar 8, 2014 at 23:29
  • 1
    or LIMIT 1 at the end of each query to tell mysql to return only 1 row Commented Mar 8, 2014 at 23:35
  • I see what you mean. The challenge is, that I would need to apply my subquery for the whole table. Commented Mar 8, 2014 at 23:38
  • @MatBailie, great comment. This was my goal initially. However, while decrypting name in your query, cryptoword1 was not recognize. At least MySql has refused such query. Hmm, maybe I had done a mistake anywhere and have overlooked it? Commented Mar 8, 2014 at 23:55
  • Why don't you just do it with php in the mix? Commented Mar 8, 2014 at 23:59

2 Answers 2

Reset to default
2

Work out the value of cryptoword1 in a sub-query, then you can reuse the result to work out the value of name in the outer query.

SELECT
  cryptoword1,
  AES_DECRYPT(name, SHA2(cryptoword1, 512))   AS name
FROM
(
  SELECT
    AES_DECRYPT(cryptoword, SHA2('DatabaseEncryption1', 512)) AS cryptoword1,
    name
  FROM
    file_tree
)
  AS sub_query
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

I have answered your commend below my question above. It looks that your initial suggestion has disappeared? Have you found a mistake in there? I would prefer what you suggested initially.
@Bunkai.Satori - My comment didn't notice that you needed to re-use the result of cryptoword1's calculation in another field. As you have discovered, that result doesn't exist at that point; you don't control calculation order, it is treated as if they are calculated in parallel. This means that you can not do SELECT f(x, y) AS a, f(z, a) AS b FROM table. You either need a sub-query, as in this answer, or you need to repeat yourself in your code; SELECT f(x, y) AS a, f(z, f(x, y)) AS b FROM table. However, even MySQL should optimise this SQL to avoid redundant effort.
Yes, the second, repeated solution works. I have tested it already. But it makes no sense to re-generate my cryptoword1 every time per record column, as I have multiple columns in the table. I believe the solution proposed in this answer above will work. I am testing it now.
@Bunkai.Satori - You're making the assumption that writing SELECT f(x, y) AS a, f(z, f(x, y)) AS b FROM table will cause f(x, y) to be calculated twice; most optimisers will see the repetition and reuse the value internally - SQL is effectively compiled and different RDBMS do lesser or better jobs than others. That said, it is still repetition of code which is often best avoided. The two answers should perform the same, it depends on your perspective as to which is more readable, maintainable, etc.
@Bunkai.Satori - If by safer you mean guaranteed a certain behaviour, then being explicit is always safer, yes; you don't run in to changes if you install a patch, for example. And the reason name is mentioned in the inner query, is because you use it in the outer query; the outer query knows nothing about the inner query except for the fields it explicitly selects.
|
0

Subqueries in the select statement must evaluate to a single value, or else you will get this error. This makes sense since you are looking to fill the value for one field.

Improve this answer

2 Comments

How does this answer the question though? Can you provide more detail?
@Yuck, hi Yuck. I have updated my question. I need to repeat the subquery for all the records within the table. I hope, the update will make my question better to comprehend.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.