Storing information ecoded in mysql

Question

Hello!

I have a field in my database and i want to store the information encoded. So i would like use the AES_ENCRYPT(), AES_DECRYPT() functions.

I have a little problem about the right usage...

So this is my code what i need to modify, but i try it many ways and always get SQL syntax error.

 $query="update users set test='".$_POST['info']."' where name='".$_SESSION['user']."'";
 mysql_query($query) or die(mysql_error());

I tried like this:

  $query="update users set (test='".$_POST['info']."',  AES_ENCRYPT('".$_POST['info']."', 'secretkey') ) where name='".$_SESSION['user']."'";
     mysql_query($query) or die(mysql_error());

and this:

 $query="update users set test='".$_POST['info']."' where name='".$_SESSION['user']."'";
     mysql_query(AES_ENCRYPT('$query','secretkey') or die(mysql_error());

Thanks for the help.

Answer 1

Well, try this:

mysql> create table test(user varchar(100), password varchar(100));
Query OK, 0 rows affected (0.09 sec)

mysql> insert into test(user,password) values('patrick', AES_ENCRYPT('password', 'mykey'));
Query OK, 1 row affected (0.00 sec)

mysql> select * from test where user = 'patrick';
+---------+------------------+
| user    | password         |
+---------+------------------+
| patrick | fucked stuff     |
+---------+------------------+

mysql> update test set password = AES_ENCRYPT('new password', 'mykey') where user = 'patrick';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select AES_DECRYPT(password, 'mykey') from test where user = 'patrick';
+--------------------------------+
| AES_DECRYPT(password, 'mykey') |
+--------------------------------+
| new password                   |
+--------------------------------+

Answer 2

The second code snippet does not assign the return value of AES_ENCRYPT to any column, thus your UPDATE syntax is incorrect. See http://dev.mysql.com/doc/refman/5.1/en/update.html .

If you want to store the encrypted data in a column named "info", your update statement at would be ... info=AES_ENCRYPT( ...

The third code snipped is not valid because you're trying to evaluate a function named AES_ENCRYPT in PHP. Most likely there is no such function in your PHP environment, so the PHP interpreter aborts with an error.

Answer 3

`$query="update users set (test='".$_POST['info']."',  test_encrypted = AES_ENCRYPT('".$_POST['info']."', 'secretkey') ) where name='".$_SESSION['user']."'";
 mysql_query($query) or die(mysql_error());`

If you want to encrypt sql query entirely, use ssl support on server, not in php client-side.