0

Well, as a newcomer to encryption, I have finally got a great system worked out. The problem is, i'm pretty sure it's breaking my SQL queries. If any of you could help me out, I'm sure it's something I have easily overlooked. This is the code I used to generate the keys:

include('Crypt/RSA.php');

$rsa = new Crypt_RSA();

extract($rsa->createKey());

And this is the code for encryption:

openssl_public_encrypt($data, $encrypted, $publickey);
return $encrypted;

All of this above works fine, and I have it called via a function. All the encryption itself works. The problem is that when I attempt to store the values in the MySQL database, my query randomly fails.

When I print the query to debug, I see the encrypted string '��y�6u�������2�N���Ī�:������7�����Bï¿ ½ï¿½ï¿½ï¿½<�������7�;[�N\�����y/�X��߭֟*���=�-�����Z�EI)ß�'�cy/� I��#?��1Gh��$�d�

which will sometimes end the query string prematurely. I am wondering if there is a specific charset I should specify or if I need to change the MySQL datatype (it was set to LONGTEXT then LONGBLOB).

Thanks again in advance!

neubert
  • 14,208
  • 21
  • 90
  • 172
Elie Zeitouni
  • 231
  • 4
  • 13
  • You need to encode it as base64 or hex before using it in a query. Queries are not permitted to contain binary data, they must be printable, even though in the database you are permitted to store binary data for the query itself it must be 'sanitized' and 'printable'. – Patashu Jun 05 '13 at 23:25
  • The input sanitization that you're not doing in your query needs to be done everywhere in all queries to make your scripts secure. Special characters in your input shouldn't affect your queries. – Paul Jun 05 '13 at 23:26
  • 1
    Possible duplicate of [How to prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php). So far, you're obviously injecting raw input into your SQL. – Álvaro González Jun 06 '13 at 15:58

1 Answers1

0

Patashu's comment solved my problem. Using base64_encode() and base64_decode() prevented errors in the query, which were apparently caused by having binary sent in a query. Thanks Patashu!!!

Elie Zeitouni
  • 231
  • 4
  • 13
  • You should treat binary data as binary data! Store it in a BLOB column, **escape** it properly or use appropriate database APIs to pass binary data as BLOBs (depends on your database connector). Base64 encoding it is one way to fix a symptom, but it's not doing it properly. – deceze Jun 06 '13 at 16:00
  • @deceze I normally escape form inputs, but I have always worried that escaping encrypted data could corrupt it. And is there a reason that Base64 encoding and storing MEDIUMTEXT is a worse method than storing escaped binary data as MEDIUMBLOB? Thank you for your patience, I'm doing my best to learn the right way. – Elie Zeitouni Jun 06 '13 at 16:15
  • 1
    See http://stackoverflow.com/questions/7550030/mysql-insert-binary-data-to-db-without-errors. Treating the data as binary data at the very least gives you a size advantage, base64 encoded data takes up ~33% more space than binary data. It also lets the database treat the data the way it is, allowing you to use functions that act on binary data in the database itself (this may be more of a theoretical than a practical advantage, if you you don't *do* anything with the data in the database). – deceze Jun 06 '13 at 16:35
  • Thank you very much, I will do as you have suggested. Also, the link you posted seems to be the exact question that I was trying to ask (I guess I missed it since I wasn't searching binary.) Is there a way to list mine as a duplicate? I know asking an already answered question is frowned upon. – Elie Zeitouni Jun 06 '13 at 19:10