1

I have a problem. When I do an insert like so in php:

sql = "INSERT INTO mytable (id, value)
VALUES ('sds83','".$EncryptedString."')";

When I run the following query it sometimes works and sometimes it doesn't. The problem is that sometimes the $EncryptedString contains characters like this: ')') which causes syntax errors. The $EncryptedString contains binary data, how can I go about this issue?

user962449
  • 3,743
  • 9
  • 38
  • 53

3 Answers3

2

Escape your encrypted string

mysql-real-escape-string

mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

See StripSlashes

Mob
  • 10,958
  • 6
  • 41
  • 58
  • The original string was already escaped, but then it was encrypted using mcrypt and the new encrypted value was placed in $EncryptedString. If I escape it again will that preserve all the characters so the string can be decrypted when I pull the data from the db? – user962449 Sep 26 '11 at 02:17
  • No, you wouldn't escape it when getting the data out, you would use stripslashes http://www.php.net/manual/en/function.stripslashes.php and everything would be fine – Mob Sep 26 '11 at 02:21
  • I don't get it. Are you recommending to escape the string or stripslashes. I don't think either would work because it would change the value of $EncryptedString. If I escape or stripslashes when inserting $EncryptedString it would change the value of the encrypted string, then when I pull it from the database later on it will be missing slashes which would be crucial in decrypting the string. Am I missing something? or does the mysql escape and strip slashes work differently? – user962449 Sep 26 '11 at 02:31
  • Please can you post what a sample of the `$EncryptedString` – Mob Sep 26 '11 at 02:47
  • I was using mcrypt to store the encrypted value in $EncryptedString. After searching online a bit I found you can use base64_encode to make it more elegant to insert into mysql. Then just use base64_decode when you need the value back. – user962449 Sep 26 '11 at 02:50
  • Okay then where does `')')` come from? If you're using base 64 it wouldn't generate `')')`. Why don't you quote the SQL query with `"` and everything else inside the query with `'` – Mob Sep 26 '11 at 02:58
  • I have an mcrypt function the encrypts and string and stores it in $EncryptedString, but that problem was that sometimes its value had an ' or ) in it, which cause syntax errors when performning the query. I found another Q on stackoverflow with my problem. One of the answers what to use base64_encode on the binary data so it can be inserted into mysql cleanly. Then when I need to decrypt the value, I would just pull the data from mysql and perform base64_decode and finally run it through my decrypt function. I hope that seems a bit clearer. – user962449 Sep 26 '11 at 03:04
  • Also, what do you mean by quoting the sql? I used " on the query and ' for my strings. – user962449 Sep 26 '11 at 03:05
  • Ok. I get you. Its okay to encrypt the binary data in base64 and insert it into the db. Are you having any problems with that? – Mob Sep 26 '11 at 03:08
  • Seems to work fine thus far. Thanks for taking the time to help me out though. Really appreciate it. – user962449 Sep 26 '11 at 03:11
1

Use PDO (or another database layer) that supports prepared statements.

When you use query parameters instead of executing raw SQL, you gain speed improvements (the database only has to plan and optimize for one query) and all the data you write to it's parameters are immediately and completely isolated from the query itself.

It's surprising how many people don't have this in place! Take the initiative and update your code.

jmkeyes
  • 3,751
  • 17
  • 20
0

You need to escape your $EncryptedString. Depending on the type of MySQL connection object/functions you are using, it could be like this:

$sql = "
    INSERT INTO mytable (id, value)
    VALUES ('sds83','" . mysql_real_escape_string($EncryptedString) . "')";
Rusty Fausak
  • 7,355
  • 1
  • 27
  • 38