search on encrypted data

Olan

I'm using getFormatter to get encrypted data in my table. This works perfectly but this data is not searchable. How can this be done? My code to get the data is:

->getFormatter( function ($val) { $salt = '123456'; return mcrypt_decrypt(MCRYPT_RIJNDAEL_128, $salt, $val, MCRYPT_MODE_ECB); }),

I'm using server side processing.
Hope someone can help me with this! Thanks

ThomD

You are pulling unencrypted data from your source and then presenting it in an encrypted format in the table? You then want to search the clear text?

Olan

No, the data is encrypted en with the mcrypt it is decrypted and shown as clear text in the table. Now i want to search on the clear text.

I forgot to mention that i'm using server side.

ThomD

Isn't that the encrypting function? Don't you need to use mcrypt_decrypt ?

Olan

you are right. I took the setFormatter and changed it to getFormatter here on the forum. I fixed my question.

Olan

Alan,

My question is not answered..... Any idea how to get this to work?

ThomD

Well, the simple answer is that unless there is a clear text version available to search, it cannot be done.

Olan

Is there no way to do mcrypt_encrypt(MCRYPT_RIJNDAEL_128, $salt, $val, MCRYPT_MODE_ECB); my search before actually search for it in the mysql database?

ThomD

Are you talking about DT decrypting the stored data for search, or encrypting the user input and searching with that? The first part one isn't going to work and the second would only work if the strings were a perfect match.

Olan

Let's say that the string is a perfect match, how do i do this?

ThomD

You can't use filtering on the client side because you are using the server side setup. I don't know anything about the server side setup, so I can't make any suggestions.

Olan

If I do a MySQL query like:

SELECT * FROM table WHERE AES_DECRYPT( column, 'salt' ) LIKE '%search_string%'

I get my results. Now a way to do it in datatables....

tangerine

Your query would reside in, or be passed to, your server-side script. There are plenty of server-side querying examples in here.

allan

Hi,

Currently the Editor libraries do not support the ability to execute SQL functions when retrieving data. As such, I'm afraid what you are looking for is currently not possible with the Editor PHP libraries. If this is something you require, you would need to perform the select statement outwith those libraries and return the data to the client as JSON.

Allan

tangerine

My apologies. I hadn't realised this involved the Editor.

Olan

Thank you Allan for the answer. This would be a nice new feature I think!

Is there a php JSON example file availiable? I will try to get this to work.

allan

Is there a php JSON example file availiable?

Not really as how you query the database will depend on what interface you are using - e.g. PDO or mysqli etc. Basically you just need to query the database and then dump the data back to the client using echo json_encode( $data );.

Allan