I want to bounce some ideas off of MySQL developers that use it for web
development. Maybe I'm a little paranoid, but when dealing with the
Internet, I want to make my web app as secure as possible. I'm hoping some
of you can offer me some ideas in this respect.
I am building a web application that uses MySQL 5.5 with Innodb tables and
I don't want the user to see the actual primary key value on the web page.
The primary key could be the cust_id, bill_id etc and is usually auto
increment. This primary key can appear in the url and will be used to pull
up a record and display it on the web page.
So I need some efficient way of 'cloaking' the real primary key so a hacker
won't try to generate random values to access info he shouldn't have access
to. How do most web sites handle this?
I thought of using UUID_Short() for the primary key instead of an auto-inc,
and this isn't really random. It generates near sequential numbers based on
So I need a way of encrypting the cust_id before sending it to the web
page. The user can bookmark this page in his browser so I need to be able
to decrypt it back to the real cust_id to retrieve the data. Doing the
encryption and decryption is easy enough for me to do on the web server.
I have tried Hex(AES_Encrypt(Cust_Id,'secret')) and this works fine except
the string is very long at 64
characters. hex(DES_Encrypt(Cust_Id,'secret')) generates a smaller string.
Another alternative is to store an MD5 hash value of Cust_Id in the table
under a different column "Cust_Id_Hash" and display that on the web
page. So the table joins would still use Cust_Id and Cust_Id_Hash would be
used only as a lookup when communicate with the web page. But Innodb's
ability to store large random strings will slow down inserts and will
consume more disk space.
What is the best way to solve the problem? I don't want to re-invent the
wheel because I'm sure this problem has been solved by other web
developers. Maybe an efficient solution is staring me in the face, so I'm
open to some suggestions. :-)