0

I would just like to know if it is or is not possible to use t-SQL (SQL Server 2012) to store a MD5 hash using HASHBYTES for a file not in the database?

I am only trying to achieve what is stated above without using anything other then T-SQL. That is the constraint weather or not there is a better way. It's a inflexible constraint/mandate that nothing but T-SQL is to be used. No addons no powrshell no nothing but T-SQL.

Can anyone provide the T-SQL code that would achieve the goal of putting the hash of some file (whatever name you want) into a field in a table (use whatever names you prefer)?

I know some might think there is a better way but that better way is not an option like it or not. So please refrain for offering any non 100% T-SQL solutions or comments.

John
  • 131
  • 1
  • 1
  • 8

2 Answers2

0

see if this works for you.(tested both text and binary file types)

Declare @x  varbinary
Declare  @y table ( C1 varbinary(max)) 

SELECT @x = convert(varbinary , BulkColumn )
FROM OPENROWSET (BULK 'c:\temp\MyFile.txt', SINGLE_CLOB) MyFile

select HASHBYTES('MD5', @x) 
insert into @y select  HASHBYTES('MD5', @x)     
select * from @y

output:

0x93B885ADFE0DA089CDF634904FD59F71

msdn Hashbyte ref:

https://msdn.microsoft.com/en-us/library/ms174415.aspx?f=255&MSPPError=-2147217396

objectNotFound
  • 1,683
  • 2
  • 18
  • 25
0
DECLARE @csCommand  varchar(4000);
DECLARE @csResult   varchar(4000);
DECLARE @result     table
    ([ID]       int identity(1,1),
     [Output]   varchar(4000));

SET @csCommand = 'certutil -hashfile ''C:\Temp\MyFile.txt'' MD5';

INSERT INTO @result ([Output])
EXEC master..xp_cmdshell @csCommand

SELECT @csResult=[Output] FROM @result WHERE ID=2;
Joundill
  • 6,828
  • 12
  • 36
  • 50
Jerome Roosan
  • 1