I am currently trying to figure out how to implement aes encryption and decryption within a database.

I am kind of new at this so please forgive my ignorance, What i would like to do is have it setup so after the user is authenticated using there username and password,
They then have the ability to read encrypted data stored in another table.

I have a basic understanding of how aes encryption /decryption works i am just unsure how i would go passing an the required decryption key without storing it in the database.

Like i said i am ignorant when it comes to this.

Any help would be greatly appreciated.

    I'm not sure what you mean by AES encryption. I see to recall this term appearing with wireless routers? You can find a list of implementations of AES encryption on this page:
    http://en.wikipedia.org/wiki/Advanced_Encryption_Standard

    It sounds like you want to create an encrypted database that your users can access by first authenticating through a web browser. Does that sound right?

    If this is the case, and you are really paranoid about security, you need to make sure that data in transit between someone's browser and the webserver hosting the encrypted data is encrypted...this means your page should be hosted as HTTPS. This involves putting a security certificate on your web server and configuring it so that it serves https pages using the cert. OpenSSL is good for this if you are using apache.

    Once a given user can securely communicate with your web server, you need to set up some pages to authenticate the user. PHP does this rather well and it's generally done by requiring them to enter a username and password. Apache allows you to authenticate users but if you have a really large number of users, you probably want to set up a database of users and their associated passwords and then you create a form to prompt users for user/pass (served over https of course). Once they enter a valid user/pass combination, you set a cookie on their machine (which expires instantly when the browser is closed or 'logout' is clicked) which reminds PHP that they are logged in. Try reading about [man]session_start[/man] and the other session functions.

    Finally, if you want your data encrypted in the database, you need a server-side encryption mechanism. Given that your pages are to be served over https, the data is encrypted in transit from a browser to the server. Once user-submitted data arrives at the server and shows up in PHP, it is no longer encrypted but this is no big deal. Before inserting (and after retrieving) from your database of choice, you can choose to encrypt (and decrypt) the data using some fixed, key which never gets transmitted (and is known only to your server until your server gets compromised) using things like [man]mcrypt[/man] or whatever. If someone happens to view your database, they would just see gibberish. On the other hand, if your machine is compromised they can find your key and figure it out. But if your machine is compromised, you're screwed anyway.

    I hope that's helpful.

      sneakyimp,

      Thank you for your reply,

      When i say AES i am referign to the Rijndael Cipher which is built into mysq. It allows two way encryption of data.

      However you are correct i would need to use https in order to encrypt the data over the wire.

      Once again thank you for your reply.

        You should probably google for +Rijndael +PHP and you might find a PHP implementation of the cipher.

        And you will probably have to store the key for your database encryption somewhere on your site. I really can't envision a situation where you have more than one key unless you have a key for each user which would need to be stored in the database.

          I wouldn't use just AES. I'd use something more secure like AES with the key wrapped in RSA security. That way you can save the RSAed key to your server (above the doc-root) then transfer the key to the user with the encrypted information. Or use that key to decrypt it. If your server is set up right in the first place, you should be able to store a plain-text IV outside the doc-root and remain relatively sure that your data in the DB is secure.

          Before you go and implement something like this, I'd suggest picking up a book or two on security. One I suggest is Pro PHP Security published by Apress. They spend two parts on Hashing and Encryption. A very thorough understanding and history of encryption and hashing is received.

            bpat1434,

            I will definatly pick up that book.

            Also what i was considering doing was basically what you just described. However without the use of RSA,

            I am very limited in what i can use for encryption due to the limitations of the php version and mysql version(Which can not be updated)

            Also due to the size of the database and the amount of data that will end up being encrypted. System preformance is a rather large concern.

            I was just planning on encrypting the aes keys with one master encryption key which would be held on the server with read only access given to apache within a shadow file.

            This is not ideal however this is the only solution i have come up with.

              I'd question your motivation. Encrypting data inside the database is normally counter-productive. You should probably assume the database is secure and take whatever steps are necessary to achieve that.

              With MySQL you can encrypt data going into and out of the database transparently - see its documentation on this subject. This is completely transparent to the application, which does not need to make any special measures after connecting with the appropriate options, certificates etc.

              Encrypting data on the disc can be done by your operating system - this is transparent to MySQL.

              Encrypting data between the user and web server can be done with SSL. This is the right and proper way.

              Encrypting the value of a column in a database mostly defeats the purpose of having one in the first place. It breaks indexing and makes the database unusable by applications which can't reproduce the encryption algorithm (e.g. data warehouse etc) and/or don't have the key.

              Mark

                MakR,

                My motives are not malicious. First off this is not a forward facing design. It is on a intranet.

                The need to read the encrypted information is required by one of our departments,

                They are also the ones that require it be encrypted within the DB.

                  Write a Reply...