Decrypting a value - urgent plz

• venkatesh.kuppili

  Mr or Mrs. 500

  Points: 579

  More actions

  January 20, 2009 at 6:42 pm

  #133712

  i have a table with

  testtable

  var1 int pk,

  prize_id int

  prize_encrypt varbinary

  i am storing encrypted value of prizE_id in prize_encrypt

  by the following statement.

  DECLARE @pass VARCHAR(50)

  SET @pass = 'WOhPorN5VIKZl$Eqcz4CR104O'

  DECLARE @prize_encrypt VARBINARY(256)

  Set @prize_encrypt = EncryptByPassphrase(@pass,Cast(@prize_id as varbinary))

  values are

  12sdf2342sdgdh243gdfb3445

  21dfgkfgjh94334fghfdghi95

  32sdf2342sdgdh243gdfb3445 (repating as first record)

  in order to aviod repeation

  DECLARE @pass VARCHAR(50)

  DECLARE @prize_pass VARCHAR(50)

  SET @pass = 'WOhPorN5VIKZl$Eqcz4CR104O'

  DECLARE @prize_encrypt VARBINARY(256)

  SELECT @maxval=IDENT_CURRENT('testtable') +1

  set @prize_id_pass = @pass +','+CAST(@maxval AS VARCHAR)

  Set @prize_encrypt = EncryptByPassphrase(@prize_pass,Cast(@prize_id as varbinary))

  Encrypting is in one stroed proc..

  and decryption is in another stored proc..

  the above one is working with out repeating..

  but now if i try to decrypt the prize_encrypt i am getting null value.

  DECLARE @pass VARCHAR(50)

  DECLARE @prize_pass VARCHAR(50)

  SET @pass = 'WOhPorN5VIKZl$Eqcz4CR104O'

  DECLARE @prize_encrypt VARBINARY(256)

  SELECT CAST(DecryptByPassphrase(@prize_pass, prize_encrypt) AS varchar) as val2

  from testtable

  i am getting NULL value if i do like this..

  please help me on this..

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 20, 2009 at 8:30 pm

  #930048

  i suspect it has something to do with being loos-goosy on your varbinary and varchar sizes:

  DECLARE @prize_encrypt VARBINARY(256)

  Set @prize_encrypt = EncryptByPassphrase(@pass,Cast(@prize_id as varbinary))

  you try to use a varbinary(256 in one spot, but by casting without a size, it defaults to a varbinary \(30)? (or is it varbinary(1)?)

  same thing when you are decrypting...varchar(50) explicitly, but the unsized cast defaults to varchar(30).

  what happens when you change your code to use the same size consistently?

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!