MySQL invalid Query syntax sometimes

Question

trying to encrypt a social security number and the query works occassionally but once every 5 to 10 times it will throw an in

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 '�9~���d�~/C�yK�~�� _', ' 50', '62' )' at line 1
SSN: _mosPEdaXEsB7zJCQe5aymaCmkcSl9bnPL5ClLXcz2QA=_ 
IV: _ �6�k���_O�'�9~���d�~/C�yK�~�� _

And here's the code

    //Encryption/Decryption key
     $Key = hash("SHA256", $Lname, true);
    //Encryption Algorithm
    $cipher_alg = MCRYPT_RIJNDAEL_256;

    $iv =  mcrypt_create_iv(mcrypt_get_iv_size($cipher_alg, MCRYPT_MODE_ECB), MCRYPT_RAND);

    $ESSN = mcrypt_encrypt($cipher_alg, $Key, $SSN, MCRYPT_MODE_CBC, $iv);
    $ESSN = '_' . base64_encode($ESSN) . '_';
     $iv = '_' . $iv . '_';

if(!mysqli_query($connection, "CALL Update_ClientSSN('$ESSN','$iv', ' $AppID',  '" . $_SESSION['ID'] . "' );")){
        $Save .= " Error saving Client SSN";
    echo $connection->error;
    echo $ESSN . ' IV: ' . $iv;
    }

also originally I was just concatenating the IV and cipher text then exploding, but that still threw errors so the underscores are just me experimenting. I'm stumped

score 2 · Accepted Answer · answered Apr 14 '13 at 00:03

You should try to base64_encode the $iv variable just like you did with $ESSN or at least convert it to hex format before you store it in the DB and see if it helps. The reason is that the $iv probably contains an illegal character such as a ' (single quote)

score 0 · Answer 2 · answered Apr 14 '13 at 00:03

0

Your problem is, that the generated value $iv contains a single quote (somewhere in the middle: O�'�9), which leads to an invalid insert statement. Using prepared statements (PDO or Prepared statements) or encoding $iv should solve your problem

answered Apr 14 '13 at 00:03

agim

1,841
12
19

encoding the iv did the trick and before when i was concatenating them I was doing it after the encode, thank you – user2008572 Apr 14 '13 at 00:06

MySQL invalid Query syntax sometimes

2 Answers2