Error inserting to MySQL

[jack5100nv]

I have code that encrypts the text using mcrypt and then update it into the database. It works fine except when i try to enter dxb123 it fails

 

Orig: dxb123

Encrypted text: aVí7âcäËå÷ ’³•tg)NÏ퉣:+'\|óß

Result: Insertion Failed.

 

Any idea why I cannot insert aVí7âcäËå÷ ’³•tg)NÏ퉣:+'\|óß into the database?

 

Following is my code. I have tried both of the following sql statements

 

---------------------------------------------------------------------

 

$mykey = "Photo25878Gallery55785";

 

function myencrypt($pass)

{

        $iv_size = mcrypt_get_iv_size(MCRYPT_RIJNDAEL_256, MCRYPT_MODE_ECB); //get vector size on ECB mode

        $iv = mcrypt_create_iv($iv_size, MCRYPT_RAND); //Creating the vector

        $cryptedpass = mcrypt_encrypt (MCRYPT_RIJNDAEL_256, $mykey, $pass, MCRYPT_MODE_ECB, $iv); //Encrypting using MCRYPT_RIJNDAEL_256 algorithm

        return $cryptedpass;

}

 

$optionnewpass1 = "dxb123";

$optionnewpass1 = myencrypt($optionnewpass1);

 

/* Satement 1 */

$insert_query = "UPDATE UserList SET password='$optionnewpass1' WHERE userid = '1'";

 

/* Statement 2 */

// $insert_query = "UPDATE UserList SET password='".$optionnewpass1."' WHERE userid = '1'";

 

if(mysql_query($insert_query))

{

echo("Success");

}

else

{

echo("Fail");

}

[DyslexicDog]

The what's the datatype and length of the column in your table?

[jack5100nv]

I have tried both text and varchar(128), same result.

[jack5100nv]

Can a blackslash in the encrypted text cause any problem?

[DyslexicDog]

It looks like you have some items in the string that need to be escaped. Use the mysql_real_escape() function on the string before you set it in the database. In the future it will help you to use the mysql_error() function on failures.

 

i.e.

<?php
$mykey = "Photo25878Gallery55785";

function myencrypt($pass)
{
        $iv_size = mcrypt_get_iv_size(MCRYPT_RIJNDAEL_256, MCRYPT_MODE_ECB); //get vector size on ECB mode
        $iv = mcrypt_create_iv($iv_size, MCRYPT_RAND); //Creating the vector
        $cryptedpass = mcrypt_encrypt (MCRYPT_RIJNDAEL_256, $mykey, $pass, MCRYPT_MODE_ECB, $iv); //Encrypting using MCRYPT_RIJNDAEL_256 algorithm
        return $cryptedpass;
}

$optionnewpass1 = "dxb123";
$optionnewpass1 = myencrypt($optionnewpass1);

/* Satement 1 */
$insert_query = "UPDATE UserList SET password='$optionnewpass1' WHERE userid = '1'";

/* Statement 2 */
// $insert_query = "UPDATE UserList SET password='".$optionnewpass1."' WHERE userid = '1'";

if(mysql_query($insert_query))
{
   echo("Success");
}
else
{
   echo("Fail". mysql_error());
}
?>

 

 

[jack5100nv]

 

 

i used mysql_real_escape(), now it lets me enter the string into the database but I cannot decrypt it since it changes slightly when i use splitslashes on it after getting it out of the db

 

Pass: dxb123

Pass Encrypted: aVí7âcäËå÷ ’³•tg)NÏ퉣:+'\|óß

Pass Encrypted Mysql_real_escape: aVí7âcäËå÷\\r’³•tg)NÏ퉣:+\\\'\\\\|óß

Pass Encrypted Stripslashes: aVí7âcäËå÷\r’³•tg)NÏ퉣:+\'\\|óß

Pass Decrypted: ’ßNyt@QÌÌóYv1¦.ÜÚýYÚ•üh¯…ú®µ˜d"åcRÜÞ‚gÿÅ•’1ö쉭§»ÑX\Ý-™‹

 

Same using a different text

 

Pass: ss

Pass Encrypted: õ!g²…CÛ¹é!ÄÁ+=i&}w´½]VT’ìì“DâÀª

Pass Encrypted Mysql_real_escape: õ!g²…CÛ¹é!ÄÁ+=i&}w´½]VT’ìì“DâÀª

Pass Encrypted Stripslashes: õ!g²…CÛ¹é!ÄÁ+=i&}w´½]VT’ìì“DâÀª

Pass Decrypted: ss

 

 

Here is the code

 

                $optionnewpass1="dxb123";

                $optionnewpass1orig=$optionnewpass1;

 

                // Encrypting passwords

                $optionnewpass1 = myencrypt($optionnewpass1);

               

                $optionnewpass1enc=$optionnewpass1;

 

                $db = mysql_connect("$hostname", "$dbuser", "$dbpass");

                mysql_select_db("$dbname",$db);

               

                $optionnewpass1 = mysql_real_escape_string($optionnewpass1);

 

//$insert_query = "UPDATE UserList SET username='$optionusername', password='$optionnewpass1', email='$optionemail', firstname='$optionfirstname', lastname='$optionlastname' WHERE userid = '1'";

$insert_query = "UPDATE UserList SET username='".$optionusername."', password='".$optionnewpass1."', email='".$optionemail."', firstname='".$optionfirstname."', lastname='".$optionlastname."' WHERE userid = '1'";

 

if(mysql_query("$insert_query"))

{

echo ("Success");

}

else

{

echo("Fail");

                                      echo "<br><br>".mysql_error()."<br>";

}

mysql_close($db);

 

/* For Troubleshooting only */

echo("<br>Username: $optionusername");

echo("<br>Pass: $optionnewpass1orig");

echo("<br>Pass Encrypted: $optionnewpass1enc");

echo("<br>Pass Encrypted Mysql_real_escape: $optionnewpass1");

 

                if(get_magic_quotes_gpc())

                {

                        $optionnewpass1=stripslashes($optionnewpass1);

                }

               

                echo("<br>Pass Encrypted: $optionnewpass1");

 

                $optionnewpass1 = mydecrypt($optionnewpass1);

 

                echo("<br>Pass Decrypted: $optionnewpass1");

[DyslexicDog]

You could try using url_encode() instead of mysql_real_escape(). That will make your string pretty large though.

[PFMaBiSmAd]

Are you 100% sure about the actual code. The Pass Encrypted Mysql_real_escape: value appears like it has had mysql_real_escape_string() applied twice, so that after the stripslashes() function it still has escaped data.

 

For your test code, you should not be checking get_magic_quotes_gpc(), because that only affects GET, POST, and COOKIE data. get_magic_quotes_runtime() would be what affects data retrieved from a database or a file, however, your test code is not retrieving the data from the database, so the test code should unconditionally run stripslashes().

[jack5100nv]

You could try using url_encode() instead of mysql_real_escape(). That will make your string pretty large though.

 

Urlencode and urldecode worked great. The script is running smooth now. Thanks DyslexicDog, you are a genius. Also, thank you PFMaBiSmAd  for your help.