How to insert a string containing #0 in a NVARCHAR() column in MSSQL?
ado, delphi, delphi-xe2, sql-server, sql-server-2008
Solution
If you want to store an string encrypted by any means that could have NULLs in it, encode and decode the encrypted data as Base64 using the routines in Soap.EncdDecd.pas (EncdDecd.pas in older versions).
And DO USE proper encryption, either from Microsoft CryptoAPI, or any of the native Delphi implementations such as DCPCrypt.
If you are storing passwords for access to your system, consider hashing (and salting) them instead. Obviously if they are passwords to connect to other systems you can't do that.
var
B64: string;
begin
B64 := EncodeString(#6'r'#0'abc');
ADOQuery1.Append;
ADOQuery1S.Value := B64;
ADOQuery1.Post;
end;
Problem
I'm sure this has a very simple solution, but I've exhausted my resources. I need to insert a bunch of `WideString` variables which may contain `#0` into a `NVARCHAR(1024)` column in MSSQL2k8. `#0` is treated as the string terminator somewhere in the transition... So, when I store `#6'r'#0'abc'`, only `#6'r'` is stored. I'm using `TADOQuery`. What can I do about this? Here's the code I use: ``` var S: string; begin S := #6'r'#0'abc'; ADOQuery1.Append; ADOQuery1S.Value := S; { At this point, S = #6'r'#0'abc' and ADOQuery1S.Value = #6'r';} ADOQuery1.Post; end; ```