Click here to Skip to main content
15,041,788 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I am inserting bulk data(serial number,pin number) to DB.Before inserting,the data from datatable is binded into XML tag.Here the pin number is encrypted follows,
strXml = "<?xml version=" + @"""1.0"" encoding=" + @"""iso-8859-1""?><batch>";
strPinXml += "<data cardid="+@"""" +strid+@""""+
                         " pinnumber=" + @"""" + myRC4Engine.CryptedText + @"""" + "></data>";

Problem is after inserting into db, to verify whether the actual pinnumber(encrypted format in db) is inserted, i decrypted the pinnumber and found that,

Please provide the solution to resolve this issue

->The first digit in all data are displaced by (’)single quote and last digit for some pinnumber is empty (if the pinnumber is-œA_¡/Ì·ÞvËÛ (ie)ending in Û for that pins last digit is empty).

Result as follows

Pins before inserting into db
(Encrypted format) --- (Decrypted format)
šA [¦,ȵØzËÚ <--eg--> 7613051524692
œA _¡/Ì•ÞvËÛ <--eg--> 1687765748683
™@ X¦!Ï´ÝÎÛ <--eg--> 4770086471383
žA Z¡+ɹÝwÏÒ <--eg--> 3642720979218
•O Q¢(˹Þ{ËÛ <--eg--> 8879412945686
ŸO_^¡,ȶÝ}Î× <--eg--> 2846751673342

Pins retrieved from db after insertion
(Encrypted format) ---- (Decrypted format)
šA [¦,ȵØzËÚ <--eg--> ’613051524692
œA _¡/Ì•ÞvËÛ <--eg--> ’68776574868
™@ X¦!Ï´ÝÎÛ <--eg--> ’77008647138
žA Z¡+ɹÝwÏÒ <--eg--> ’642720979218
•O Q¢(˹Þ{ËÛ <--eg--> ’879412945686
ŸO ^¡,ȶÝ}Î× <--eg--> ’846751673342

XML tag data sending to db from code-behind
<data cardid="008900320000" pinnumber="šA[¦,ȵØzËÚ"></data>
<data cardid="008900320001" pinnumber="œA_¡/Ì·ÞvËÛ"></data>
<data cardid="008900320002" pinnumber="™@X¦!Ï´ÝÎÛ"></data>
<data cardid="008900320003" pinnumber="žAZ¡+ɹÝwÏÒ"></data>
<data cardid="008900320004" pinnumber="•OQ¢(˹Þ{ËÛ"></data>
<data cardid="008900320005" pinnumber="ŸO^¡,ȶÝ}Î×"></data>

Application coding as follows
        RC4Engine myRC4Engine = new RC4Engine();
        myRC4Engine.EncryptionKey = "ab48495fdjk4950dj39405fk";

        strXml = "<?xml version=" + @"""1.0"" encoding=" + @"""iso-8859-1""?> <batch>";

        foreach (DataRow lobjbaseBatchDetail in dt.Rows)
            myRC4Engine.InClearText = lobjbaseBatchDetail[3].ToString();

            strCardid = lobjbaseBatchDetail[0].ToString();
            strBatchid = lobjbaseBatchDetail[1].ToString();
            strid = strCardid + strBatchid + lobjbaseBatchDetail[2].ToString();
            strPinXml += "<data cardid="+@"""" +strid+@""""+
                 " pinnumber=" + @"""" + myRC4Engine.CryptedText + @"""" + "></data>";
            strXml = strXml + strPinXml + "</batch>";
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@BATCHUPLOAD_XML", SqlDbType.Text );
            arrParam[0].Direction = ParameterDirection.Input;
            arrParam[0].Value = strXml;

            iResult = SqlHelper.ExecuteNonQuery(objTrans, CommandType.StoredProcedure, "test_proc", arrParam);

    catch(Exception ex)
        throw new Exception("Upload failed :" + ex.Message);

stored procedure
create procedure test_proc
EXEC sp_xml_preparedocument @idoc OUTPUT, @BATCHUPLOAD_XML
insert into test_table_new
SELECT cardid,pinnumber
FROM OPENXML (@idoc, '/batch/data')
WITH (cardid varchar(100) '@cardid', pinnumber nvarchar(200) '@pinnumber')
EXEC sp_xml_removedocument @idoc

For Encryption and Decryption

public class RC4Engine
	   #region Costructor
	   public RC4Engine()
	    #region Public Method
	    public bool Encrypt()
		bool toRet = true;

		     long i=0;
	     	     long j=0;
		     Encoding enc_default = Encoding.Default;
		     byte[] input  = enc_default.GetBytes(this.m_sInClearText);
		     byte[] output = new byte[input.Length];
		     byte[] n_LocBox = new byte[m_nBoxLen];
		     long ChipherLen = input.Length + 1;
		     for ( long offset = 0; offset < input.Length ; offset++ )
			  i = ( i + 1 ) % m_nBoxLen;
			  j = ( j + n_LocBox[i] ) %  m_nBoxLen; 
			  byte temp =  n_LocBox[i];
			  n_LocBox[i] = n_LocBox[j];
			  n_LocBox[j] = temp;
			  byte a = input[offset];
			  byte b = n_LocBox[(n_LocBox[i]+n_LocBox[j])% m_nBoxLen];
			  output[offset] = (byte)((int)a^(int)b);	
			char[] outarrchar = new char[enc_default.GetCharCount(output,0,output.Length)];
			this.m_sCryptedText = new string (outarrchar);
		    toRet = false;
		return ( toRet );

public bool Decrypt()
		 bool toRet = true;

		    this.m_sInClearText = this.m_sCryptedText;
		    m_sCryptedText = "";
		    if (toRet = Encrypt())
			m_sInClearText = m_sCryptedText;
			toRet = false;
		    return toRet;

1 solution

What the hell. Oddly enough you and[^] this guy are working on the exact same code from opposite sides of the planet.

Since your code does not compile I had to do things the hard way. I really doubt that your code is the problem. If you look at your input to the database and what you are getting out of it you will observe that the strings are not the same. Since ISO-8859-1 is for a single byte character set, my guess would be that if you remove that attribute from your XML it might start working.

See here[^]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900