I develop a newsletter system and generate unique tokens using GUID and the current date:
public static string GetUniqueToken()
{
byte[] time = BitConverter.GetBytes(DateTime.UtcNow.ToBinary());
byte[] key = Guid.NewGuid().ToByteArray();
return Convert.ToBase64String(time.Concat(key).ToArray());
}I also have a function to retrieve this date and find out if it has expired or not (after 24 hours).
public static DateTime GetDateFromToken(string token)
{
byte[] data = Convert.FromBase64String(token);
return DateTime.FromBinary(BitConverter.ToInt64(data, 0));
}I'd now like to delete expired tokens via a single SQL query, to avoid having to loop through all the lines in C# and run a test each time. What I failed to do
Thanks in advance
I started to develop a piece of SQL code to do this supression work instead of using greedy C# code. I can't get it to work
DECLARE @limitDate DATETIME;
SET @limitDate = DATEADD(HOUR, -48, GETDATE());
DELETE FROM newsletter
WHERE DATEADD(SECOND,
CONVERT(BIGINT, CONVERT(VARBINARY(MAX), SUBSTRING(token, 37, 8), 2)) / 10000, '19700101') < @limitDate;Example of token generated:
Akias1XZ20jFoDOnWSZfRqgFHdv+UUVP
It says:
Msg 8114, Level 16, State 5, Line 6
Error converting data type nvarchar to varbinary.
2条答案
按热度按时间zte4gxcn1#
Okay, so for some reason you do not store the
datealong with the token. Then, let's take a look on how you compute the token in the first place:That is, you have a base 64 string, which will have its first 64 bits that represent the time. The remainder is uninteresting.
See: https://learn.microsoft.com/en-us/dotnet/api/system.datetime.tobinary?view=net-7.0
BASE64_DECODE will decode your base64 string in SQL Server for you. Now, the result, as per the documentation is
Return types
Now, you want to get the range of bits from the varbinary, more precisely the first 64 bits and then you intend to convert that into a date. After converting your value to
bigint, you can convert the result intodatetime, see Convert bigint to datetime .fwzugrvs2#
The ideal scenario would be to store the expiration date of the tokens in SQL alongside the token (or have a SQL routine capable of extracting the date) so that you can write a procedure (or parameterize a SQL statement) to pass in the date (or hard code the date in the proc), and just do something like
If that's not possible, and you need C# to do the date extraction so you can then come up with a list of tokens to delete, you have a few options:
1. Pass in a delimited list of tokens
Assuming you know a character which you can use as a delimiter (i.e. a character that will never occur in the token strings), you can just using C#
string.Join(myDelimiter, myListOfTokens)to serialize the tokens, then pass that into the sqlstatement; something like:2. Use XML or JSON
Similar in concept, you could serialize the list of tokens as either JSON or XML, then parse them inside of SQL. I think this might be overkill for your scenario, but it is better if you have difficulty coming up with a safe delimiter.
3. Use Table-Valued Parameters
This one is probably WAY overkill, but you can write a stored procedure to take in a user-defined Table-Valued Parameter which is a type you define in SQL that looks to SQL just like a table. You can load it up with your data in C# and pass it to the procedure. I have to admit, it's been a long time since I messed with TVPs so I don't know the syntax for that in C# right now, but it is an option.
Help with a SQL Routine
If you can provide some information about the format of the tokens, and some examples, we may be able to help extract the date component from the token using SQL. However I would recommend creating a new question for that.