I was working on developing a RESTful service where the Id of a particular table in the database needs to be returned to the client for future access to the particular entry in the database. I was advised against using auto increment and returning the id directly and hence I went for encrypting and sending the Id in the following manner instead -
SET @secretKey1 = "some key";
SET @secretKey2 = CONCAT("some other key", AccountNo);
SET @encryptedAccountNo = TO_BASE64(AES_ENCRYPT(AccountNo, @secretKey1));
SET @encryptedId = TO_BASE64(AES_ENCRYPT(Id, @secretKey));
RETURN CONCAT(@encryptedAccountNo, @encryptedId);
(Note: The Account Number is a sufficiently random field not exposed to the client)
Would using UUID as the primary key be more appropriate? If so, is using the UUID enough or should that be encrypted too?
Or would you use some other design entirely?
Also, do you have any tips for improving the security or performance aspects of the existing design?
I think you may have misunderstood that advice....
When you say "I was advised against using auto increment and returning the id directly".
You should certainly always CHECK the Id is correct to use server side, before using it;
(i.e. check that the document with that Id belongs to the user who is trying to edit it),
but encrypting the ID itself really serves no useful purpose as your API will have to deal with the encrypted one (unless you are encrypt/decrypt client side, which does nothing for security), so the (malicious) client can just send the encrypted version and have the exact same result...
We cannot comment of the security of your code from looking at a (small) piece of your database logic. If you want proper review you will have to post the code that actually does the interaction...