Search code examples
phpjavascriptmysqlencryptionmcrypt

mcrypt_encrypt on string containing ":" (colon)

My web application gets URL objects from clients, encrypts it using mcrypt_encrypt , and stores the result in a MySQL DB as type LONGTEXT. This entire thing is done as shown below:

    /* $copied below is received from user and can be any string. But I'm just using a URL as example,as that's where it fails */
    $copied = "http://www.google.com"
    $encCopied = mcrypt_encrypt(MCRYPT_RIJNDAEL_128,$encryption_key,$copied,MCRYPT_MODE_CFB);

    $copystuff = "UPDATE LinkTable set copied='$encCopied'";

    mysqli_query($db,$copystuff)

When this code is executed, the MYSQL query on the last line fails with a "syntax error". After a lot of hours of debugging, which involved manually changing the $copied variable in the above php, I finally figured out that it happens because of the colon (":") in $copied variable. When I change $copied to www.google.com , no SQL syntax error is thrown, everything works perfect. But, if I then change $copied to :www.google.com , SQL again gives syntax error.

Note that this code works for every other type of string, but fails only when a colon is present. My assumption is that mcrypt_encrypt encrypts the colon into some unworkable thing that MYSQL is not able to process.

The error displayed is like this,when there is a colon present:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'wcÀeHÿë%†‚;/æ1ÎíëO¤SÌ­ÎúR²#•Wwðg T¥4ª¨\£%@‚y' at line 1

Any inputs on why this could be happening? Right now, I'm making the application work by sending the URL after stripping of the http:// part from the URL.

Thanks.

Solution

  • I'm sure somewhere in there is a character that is borking the SQL. Try escaping your encrypted string:

    $copystuff = "UPDATE LinkTable set copied='" . mysqli_real_escape_string($encCopied) . "'";