However, when you look at the value in SQL table it does not make ANY sense at all.
In order to reverse engineer you have to convert numbers to binaries in order to determine which bits are responsible for certain rights/operations.
At first I tried to do it manually, using calculator, but was quickly tired of it. So many copy-paste and switching modes between Decimal and Binary in addition to inability to process really big numbers bigger than "9223372036854775807", which is just NUMERIC(19) and you limited to see only 64 bits.
The best I've found in the Internet was post of Mark S. Rasmussen: http://improve.dk/converting-between-base-2-10-and-16-in-t-sql/
There he presented very simple conversion functions. The problem is - I do not like functions.
So I decided to reinvent the wheel, hoping my code would be better.
My approach is basing on preparing binary quartets from 0 to 15 (see the chart).
| Decimal Number | Binary Quartet |
|---|---|
| 0 | 0000 |
| 1 | 0001 |
| 2 | 0010 |
| 3 | 0011 |
| 4 | 0100 |
| 5 | 0101 |
| 6 | 0110 |
| 7 | 0111 |
| 8 | 1000 |
| 9 | 1001 |
| 10 | 1010 |
| 11 | 1011 |
| 12 | 1100 |
| 13 | 1101 |
| 14 | 1110 |
| 15 | 1111 |
The very first query was little ugly using While loop:
DECLARE @TheNumber NUMERIC(32) = 56346543654365645465463543439347;
DECLARE @Quartets TINYINT = DATALENGTH(@TheNumber)*2;
DECLARE @BitCode VARCHAR(136)='';
WHILE @Quartets > 0
BEGIN
SELECT @Quartets -=1, @BitCode = CASE @TheNumber % 16
WHEN 0 THEN '0000' WHEN 1 THEN '0001'
WHEN 2 THEN '0010' WHEN 3 THEN '0011'
WHEN 4 THEN '0100' WHEN 5 THEN '0101'
WHEN 6 THEN '0110' WHEN 7 THEN '0111'
WHEN 8 THEN '1000' WHEN 9 THEN '1001'
WHEN 10 THEN '1010' WHEN 11 THEN '1011'
WHEN 12 THEN '1100' WHEN 13 THEN '1101'
WHEN 14 THEN '1110' WHEN 15 THEN '1111'
END + @BitCode, @TheNumber = FLOOR(@TheNumber/16.);
END
PRINT @BitCode;
|
My Second try was based on recursion:
DECLARE @TheNumber NUMERIC(32) = 56346543654365645465463543439347;
DECLARE @Quartets TINYINT = DATALENGTH(@TheNumber)*2;
;WITH TheNumber AS (
SELECT 0 as Level, @TheNumber % 16 as Digit,
FLOOR(@TheNumber/16) as TheNumber
UNION ALL
SELECT Level + 1, TheNumber % 16, FLOOR(TheNumber/16.)
FROM TheNumber WHERE Level < @Quartets
), Quartet AS (
SELECT 0 as Digit, '0000' as Quartet UNION ALL SELECT 1, '0001'
UNION ALL SELECT 2, '0010' UNION ALL SELECT 3, '0011'
UNION ALL SELECT 4,'0100' UNION ALL SELECT 5, '0101'
UNION ALL SELECT 6, '0110' UNION ALL SELECT 7, '0111'
UNION ALL SELECT 8, '1000' UNION ALL SELECT 9, '1001'
UNION ALL SELECT 10, '1010' UNION ALL SELECT 11, '1011'
UNION ALL SELECT 12, '1100' UNION ALL SELECT 13, '1101'
UNION ALL SELECT 14, '1110' UNION ALL SELECT 15, '1111'
)
SELECT (
SELECT q.Quartet + ''
FROM TheNumber as n
INNER JOIN Quartet as q
ON n.Digit = q.Digit
ORDER BY n.Level DESC
FOR XML PATH('')
) as BNumber;
|
The problem was still not solved. How to massively decode numbers without calling functions?
And here is the final script I came up with:
|
;WITH Quartet AS (
SELECT 0 as Digit, '0000' as Quartet UNION ALL SELECT 1, '0001'
UNION ALL SELECT 2, '0010' UNION ALL SELECT 3, '0011'
UNION ALL SELECT 4,'0100' UNION ALL SELECT 5, '0101'
UNION ALL SELECT 6, '0110' UNION ALL SELECT 7, '0111'
UNION ALL SELECT 8, '1000' UNION ALL SELECT 9, '1001'
UNION ALL SELECT 10, '1010' UNION ALL SELECT 11, '1011'
UNION ALL SELECT 12, '1100' UNION ALL SELECT 13, '1101'
UNION ALL SELECT 14, '1110' UNION ALL SELECT 15, '1111'
)
SELECT TOP 10000 m.message_id, BinaryCode
= q7.Quartet + q6.Quartet + q5.Quartet + q4.Quartet
+ q3.Quartet + q2.Quartet + q1.Quartet + q0.Quartet
FROM sys.messages as m
INNER JOIN Quartet as q0 ON q0.Digit = m.message_id % 16
INNER JOIN Quartet as q1 ON q1.Digit = FLOOR(m.message_id/POWER(16,1)) % 16
INNER JOIN Quartet as q2 ON q2.Digit = FLOOR(m.message_id/POWER(16,2)) % 16
INNER JOIN Quartet as q3 ON q3.Digit = FLOOR(m.message_id/POWER(16,3)) % 16
INNER JOIN Quartet as q4 ON q4.Digit = FLOOR(m.message_id/POWER(16,4)) % 16
INNER JOIN Quartet as q5 ON q5.Digit = FLOOR(m.message_id/POWER(16,5)) % 16
INNER JOIN Quartet as q6 ON q6.Digit = FLOOR(m.message_id/POWER(16,6)) % 16
INNER JOIN Quartet as q7 ON q7.Digit = FLOOR(m.message_id/POWER(16,7)) % 16
|
That query looks even uglier and supports only data type "INT", but it can do inline conversions without using a function!
Later I tested that query against Mark S. Rasmussen's query within a function:
* I modified it little bit to produce the similar result and accept INT as a parameter.
CREATE FUNCTION [dbo].[DecimalToBinary]
(
@Input int
)
RETURNS varchar(32)
AS
BEGIN
DECLARE @Output varchar(32) = ''
WHILE @Input > 0 BEGIN
SET @Output = @Output
+ CAST((@Input % 2) AS varchar)
SET @Input = @Input / 2
END
RETURN RIGHT(REPLICATE('0',32) + REVERSE(@Output), 32)
END
GO
SELECT TOP 10000 message_id,
dbo.DecimalToBinary(message_id)
FROM sys.messages
|
My testing showed that usage of function was 3.5 times slower than just inline query.
The very old battle: Ugly code vs Slow code.
No comments:
Post a Comment