SQL Server - Hasbytes in function gives different result then in select
I created a function like this:
CREATE FUNCTION dbo.HashCheeseName (@CheeseName NVARCHAR(40))
RETURNS VARCHAR(40)
AS BEGIN
DECLARE @Salt VARCHAR(25)
DECLARE @CheeseName NVARCHAR(40)
DECLARE @output VARCHAR(40)
SET @Salt = '123abc11aa'
SET @output = HASHBYTES('SHA2_256', CAST(@CheeseName as VARCHAR(40)) + @Salt)
RETURN @output
END
;
When I just run
SELECT HASHBYTES('SHA2_256', CAST('SwissCheese' as VARCHAR(40)) + @Salt)
I get an expected result like 0xF456D41144584064AC5456B7E3...
However, when I run the function in a query
SELECT dbo.HashCheeseName('SwissCheese')
I get a result like this: h:Mó!yýŠù’p» ªu_aøP¾æhw
Any ideas on why it would result in something like this? At first sight it looks like a conversion issue, but I don't see the problem
sql-server tsql salt asked Nov 15 '18 at 12:33
DenStudentDenStudent
5691625
add a comment |
I created a function like this:
CREATE FUNCTION dbo.HashCheeseName (@CheeseName NVARCHAR(40))
RETURNS VARCHAR(40)
AS BEGIN
DECLARE @Salt VARCHAR(25)
DECLARE @CheeseName NVARCHAR(40)
DECLARE @output VARCHAR(40)
SET @Salt = '123abc11aa'
SET @output = HASHBYTES('SHA2_256', CAST(@CheeseName as VARCHAR(40)) + @Salt)
RETURN @output
END
;
When I just run
SELECT HASHBYTES('SHA2_256', CAST('SwissCheese' as VARCHAR(40)) + @Salt)
I get an expected result like 0xF456D41144584064AC5456B7E3...
However, when I run the function in a query
SELECT dbo.HashCheeseName('SwissCheese')
I get a result like this: h:Mó!yýŠù’p» ªu_aøP¾æhw
Any ideas on why it would result in something like this? At first sight it looks like a conversion issue, but I don't see the problem
sql-server tsql salt asked Nov 15 '18 at 12:33
DenStudentDenStudent
5691625
4
...but you said the function returnsVARCHAR(40). Of course SQL Server is going to convert it to that, then. (The actual type isVARBINARY(8000).)
– Jeroen Mostert
Nov 15 '18 at 12:38
add a comment |
I created a function like this:
CREATE FUNCTION dbo.HashCheeseName (@CheeseName NVARCHAR(40))
RETURNS VARCHAR(40)
AS BEGIN
DECLARE @Salt VARCHAR(25)
DECLARE @CheeseName NVARCHAR(40)
DECLARE @output VARCHAR(40)
SET @Salt = '123abc11aa'
SET @output = HASHBYTES('SHA2_256', CAST(@CheeseName as VARCHAR(40)) + @Salt)
RETURN @output
END
;
When I just run
SELECT HASHBYTES('SHA2_256', CAST('SwissCheese' as VARCHAR(40)) + @Salt)
I get an expected result like 0xF456D41144584064AC5456B7E3...
However, when I run the function in a query
SELECT dbo.HashCheeseName('SwissCheese')
I get a result like this: h:Mó!yýŠù’p» ªu_aøP¾æhw
Any ideas on why it would result in something like this? At first sight it looks like a conversion issue, but I don't see the problem
sql-server tsql salt asked Nov 15 '18 at 12:33
DenStudentDenStudent
5691625
I created a function like this:
CREATE FUNCTION dbo.HashCheeseName (@CheeseName NVARCHAR(40))
RETURNS VARCHAR(40)
AS BEGIN
DECLARE @Salt VARCHAR(25)
DECLARE @CheeseName NVARCHAR(40)
DECLARE @output VARCHAR(40)
SET @Salt = '123abc11aa'
SET @output = HASHBYTES('SHA2_256', CAST(@CheeseName as VARCHAR(40)) + @Salt)
RETURN @output
END
;
When I just run
SELECT HASHBYTES('SHA2_256', CAST('SwissCheese' as VARCHAR(40)) + @Salt)
I get an expected result like 0xF456D41144584064AC5456B7E3...
However, when I run the function in a query
SELECT dbo.HashCheeseName('SwissCheese')
I get a result like this: h:Mó!yýŠù’p» ªu_aøP¾æhw
Any ideas on why it would result in something like this? At first sight it looks like a conversion issue, but I don't see the problem
sql-server tsql salt sql-server tsql salt asked Nov 15 '18 at 12:33
DenStudentDenStudent
5691625
asked Nov 15 '18 at 12:33
DenStudentDenStudent
5691625
asked Nov 15 '18 at 12:33
DenStudentDenStudent
5691625
asked Nov 15 '18 at 12:33
DenStudentDenStudent
5691625
asked Nov 15 '18 at 12:33
DenStudentDenStudent
5691625
5691625
4
...but you said the function returnsVARCHAR(40). Of course SQL Server is going to convert it to that, then. (The actual type isVARBINARY(8000).)
– Jeroen Mostert
Nov 15 '18 at 12:38
add a comment |
4
...but you said the function returnsVARCHAR(40). Of course SQL Server is going to convert it to that, then. (The actual type isVARBINARY(8000).)
– Jeroen Mostert
Nov 15 '18 at 12:38
4
4
...but you said the function returns
VARCHAR(40). Of course SQL Server is going to convert it to that, then. (The actual type is VARBINARY(8000).)– Jeroen Mostert
Nov 15 '18 at 12:38
...but you said the function returns
VARCHAR(40). Of course SQL Server is going to convert it to that, then. (The actual type is VARBINARY(8000).)– Jeroen Mostert
Nov 15 '18 at 12:38
add a comment |
1 Answer
1
active
oldest
votes
What you are doing there isn't the same. HASHBYTES returns a varbinary (Hashbytes (Transact-SQL) - Return Values) where as your function is returning a varchar. Those are different datatypes. As a result, the last line of your function is effectively:
SET @output = CONVERT(varchar(40),HASHBYTES('SHA2_256', CAST(@CheeseName as VARCHAR(40)) + @Salt));
The varchar representation of a varbinary will not be the same. Either return a varbinary in your function, or your test SELECT with a CONVERT to a varchar(40).
Edit: To confirm, the solution is to simply change the return type of your function and variable:
CREATE FUNCTION dbo.HashCheeseName (@CheeseName NVARCHAR(40))
RETURNS varbinary(8000)
AS BEGIN
DECLARE @Salt VARCHAR(25);
DECLARE @CheeseName NVARCHAR(40);
DECLARE @output varbinary(8000) ;
SET @Salt = '123abc11aa';
SET @output = HASHBYTES('SHA2_256', CAST(@CheeseName as VARCHAR(40)) + @Salt);
RETURN @output;
END
answered Nov 15 '18 at 12:40
LarnuLarnu
20.4k51732
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53319627%2fsql-server-hasbytes-in-function-gives-different-result-then-in-select%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
What you are doing there isn't the same. HASHBYTES returns a varbinary (Hashbytes (Transact-SQL) - Return Values) where as your function is returning a varchar. Those are different datatypes. As a result, the last line of your function is effectively:
SET @output = CONVERT(varchar(40),HASHBYTES('SHA2_256', CAST(@CheeseName as VARCHAR(40)) + @Salt));
The varchar representation of a varbinary will not be the same. Either return a varbinary in your function, or your test SELECT with a CONVERT to a varchar(40).
Edit: To confirm, the solution is to simply change the return type of your function and variable:
CREATE FUNCTION dbo.HashCheeseName (@CheeseName NVARCHAR(40))
RETURNS varbinary(8000)
AS BEGIN
DECLARE @Salt VARCHAR(25);
DECLARE @CheeseName NVARCHAR(40);
DECLARE @output varbinary(8000) ;
SET @Salt = '123abc11aa';
SET @output = HASHBYTES('SHA2_256', CAST(@CheeseName as VARCHAR(40)) + @Salt);
RETURN @output;
END
answered Nov 15 '18 at 12:40
LarnuLarnu
20.4k51732
add a comment |
What you are doing there isn't the same. HASHBYTES returns a varbinary (Hashbytes (Transact-SQL) - Return Values) where as your function is returning a varchar. Those are different datatypes. As a result, the last line of your function is effectively:
SET @output = CONVERT(varchar(40),HASHBYTES('SHA2_256', CAST(@CheeseName as VARCHAR(40)) + @Salt));
The varchar representation of a varbinary will not be the same. Either return a varbinary in your function, or your test SELECT with a CONVERT to a varchar(40).
Edit: To confirm, the solution is to simply change the return type of your function and variable:
CREATE FUNCTION dbo.HashCheeseName (@CheeseName NVARCHAR(40))
RETURNS varbinary(8000)
AS BEGIN
DECLARE @Salt VARCHAR(25);
DECLARE @CheeseName NVARCHAR(40);
DECLARE @output varbinary(8000) ;
SET @Salt = '123abc11aa';
SET @output = HASHBYTES('SHA2_256', CAST(@CheeseName as VARCHAR(40)) + @Salt);
RETURN @output;
END
answered Nov 15 '18 at 12:40
LarnuLarnu
20.4k51732
add a comment |
What you are doing there isn't the same. HASHBYTES returns a varbinary (Hashbytes (Transact-SQL) - Return Values) where as your function is returning a varchar. Those are different datatypes. As a result, the last line of your function is effectively:
SET @output = CONVERT(varchar(40),HASHBYTES('SHA2_256', CAST(@CheeseName as VARCHAR(40)) + @Salt));
The varchar representation of a varbinary will not be the same. Either return a varbinary in your function, or your test SELECT with a CONVERT to a varchar(40).
Edit: To confirm, the solution is to simply change the return type of your function and variable:
CREATE FUNCTION dbo.HashCheeseName (@CheeseName NVARCHAR(40))
RETURNS varbinary(8000)
AS BEGIN
DECLARE @Salt VARCHAR(25);
DECLARE @CheeseName NVARCHAR(40);
DECLARE @output varbinary(8000) ;
SET @Salt = '123abc11aa';
SET @output = HASHBYTES('SHA2_256', CAST(@CheeseName as VARCHAR(40)) + @Salt);
RETURN @output;
END
answered Nov 15 '18 at 12:40
LarnuLarnu
20.4k51732
What you are doing there isn't the same. HASHBYTES returns a varbinary (Hashbytes (Transact-SQL) - Return Values) where as your function is returning a varchar. Those are different datatypes. As a result, the last line of your function is effectively:
SET @output = CONVERT(varchar(40),HASHBYTES('SHA2_256', CAST(@CheeseName as VARCHAR(40)) + @Salt));
The varchar representation of a varbinary will not be the same. Either return a varbinary in your function, or your test SELECT with a CONVERT to a varchar(40).
Edit: To confirm, the solution is to simply change the return type of your function and variable:
CREATE FUNCTION dbo.HashCheeseName (@CheeseName NVARCHAR(40))
RETURNS varbinary(8000)
AS BEGIN
DECLARE @Salt VARCHAR(25);
DECLARE @CheeseName NVARCHAR(40);
DECLARE @output varbinary(8000) ;
SET @Salt = '123abc11aa';
SET @output = HASHBYTES('SHA2_256', CAST(@CheeseName as VARCHAR(40)) + @Salt);
RETURN @output;
END
answered Nov 15 '18 at 12:40
LarnuLarnu
20.4k51732
answered Nov 15 '18 at 12:40
LarnuLarnu
20.4k51732
answered Nov 15 '18 at 12:40
LarnuLarnu
20.4k51732
answered Nov 15 '18 at 12:40
LarnuLarnu
20.4k51732
20.4k51732
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53319627%2fsql-server-hasbytes-in-function-gives-different-result-then-in-select%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
4
...but you said the function returns
VARCHAR(40). Of course SQL Server is going to convert it to that, then. (The actual type isVARBINARY(8000).)– Jeroen Mostert
Nov 15 '18 at 12:38