CODEWITHSUNDEEP
sql-servert-sql
Slime recipe
Slime recipe

Reputation: 2283

Store such characters in SQL Server 2008 R2

I'm storing encrypted passwords in the database, It worked perfect so far on MachineA. Now that I moved to MachineB it seems like the results gets corrupted in the table.

For example: ù9qÆæ\2 Ý-³Å¼]ó will change to ?9q??\2 ?-³?¼]? in the table.

That's the query I use:

ALTER PROC [Employees].[pRegister](@UserName NVARCHAR(50),@Password VARCHAR(150))

AS
BEGIN

DECLARE @Id UNIQUEIDENTIFIER
SET @Id = NEWID()

SET @password = HashBytes('MD5', @password + CONVERT(VARCHAR(50),@Id))

SELECT @Password

INSERT INTO Employees.Registry (Id,[Name],[Password]) VALUES (@Id, @UserName,@Password) 

END

Thanks

Upvotes: 0

Views: 228

Answers (2)

gbn
gbn

Reputation: 432431

You are mixing 2 datatypes:

  • password need to be nvarchar to support non-Western European characters
  • literals need N prefix

Demo:

DECLARE @pwdgood nvarchar(150), @pwdbad varchar(150)
SET @pwdgood = N'ù9qÆæ\2 Ý-³Å¼]ó'
SET @pwdbad = N'?9q??\2 ?-³?¼]?'
SELECT @pwdgood, @pwdbad
  • HashBytes gives varbinary(8000) so you need this in the table

Note: I'd also consider salting the stored password with something other than ID column for that row

Upvotes: 2

marc_s
marc_s

Reputation: 755013

If you want to store such characters, you need to:

  • use NVARCHAR as the datatype for your columns and parameters (@Password isn't NVARCHAR and the CAST you're using to assign the password in the database table isn't using NVARCHAR either, in your sample ...)
  • use the N'....' syntax for indicating Unicode string literals

With those two in place, you should absolutely be able to store and retrieve any valid Unicode character

Upvotes: 0

Related Questions