Hi guys, I'm trying to be smart and conserve MySQL database space. I have a very simple table with 2 columns, dm_id and dm_str. I'm trying to check it with user input, but the user input would be the value that has gone through a function. Make sense?

User inputs: blah blah

SQL would be: "SELECT dm_str FROM table WHERE function(dm_str) = $userinput"

Is there any way to do this?

That's how passwords and other built in functions work. However, your fuction call is altering the column value and not the user input so step one would be something like this:

"SELECT dm_str FROM table WHERE dm_str = function($userinput)"

But that returns the string where it equals the string. I'm guessing you probably mean something closer to this:

"SELECT dm_id FROM table WHERE dm_str = function($userinput)"

Unless I'm missing the point.

That's how passwords and other built in functions work. However, your fuction call is altering the column value and not the user input so step one would be something like this:

"SELECT dm_str FROM table WHERE dm_str = function($userinput)"

But that returns the string where it equals the string. I'm guessing you probably mean something closer to this:

"SELECT dm_id FROM table WHERE dm_str = function($userinput)"

Unless I'm missing the point.

Thanks for the reply. I could easily alter the user input, but the function is an encyrption (thats one way).

So the user will enter an encrypted string, and in a sense it would decrypt it.

Example:

I enter the string "Hello" as its an encrypted message.

The program matches it with the message in the database, but inorder to do that, it must first encrypt the value. Then it returns the value that it just encrypted.

I'm now realizing I'll need to make another column that holds the encrypted value.

A word of clarification, my above example assumed the content of the data column was already encrypted... or at least matched what the function was looking for. As long as your saving function encrypts before saving and the query function encryptes in order to match, you should have no trouble with just the one column. Unless of course you want the decrypted string returned. In that case, you'd probably want something like this:

SELECT DECODE(dm_str) FROM table WHERE dm_str = ENCODE($userinput)"

The trouble here is that the encrypt/decrypt functions must be built-in MySQL functions

http://dev.mysql.com/doc/mysql/en/Encryption_functions.html

I don't yet know how to define custom functions

A word of clarification, my above example assumed the content of the data column was already encrypted... or at least matched what the function was looking for. As long as your saving function encrypts before saving and the query function encryptes in order to match, you should have no trouble with just the one column. Unless of course you want the decrypted string returned. In that case, you'd probably want something like this:

SELECT DECODE(dm_str) FROM table WHERE dm_str = ENCODE($userinput)"

The trouble here is that the encrypt/decrypt functions must be built-in MySQL functions

http://dev.mysql.com/doc/mysql/en/Encryption_functions.html

I don't yet know how to define custom functions

Thanks Phineus.
Yeah, the content wouldn't be encrypted in the database. I solved the problem with just creating a new column. Whats 2 more bits of data? :)