Re: [sqlite] decoding a bitmask

[Paul Sanderson]

Thanks all - agree rookie mistake with xor, had this been a C++ exercise I
would have have been OK - SQL seems to make my mind go blank...

Thanks for the case explanation Mark - v helpful.

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit

-Forensic Toolkit for SQLite
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy


On 13 October 2014 16:30, Mark Lawrence  wrote:

> My apologies for the previous completely wrong mesage. I got mixed up
> with operator meaning & precedence...
>
> On Mon Oct 13, 2014 at 02:39:40PM +0100, Paul Sanderson wrote:
> >
> > My actual code is as folows
> >
> >   (CASE visits.transition & 0xFF00  WHEN 0x0080 THEN 'Blocked'
> > ELSE '' END ||
>
> But I can at least put some better light on your issue. SQLite returns
> different results for your style of case/when construct:
>
> CASE expr WHEN val THEN...
>
> and the alternative which I find reads easier:
>
> CASE WHEN truth_expr THEN...
>
> Here's a demonstration:
>
> WITH x
> AS (
> SELECT
> 0x01 AS a,
> '0x01' AS txt
> UNION ALL SELECT
> 0x10,
> '0x10'
> UNION ALL SELECT
> 0x01 | 0x10,
> '0x01 | 0x10'
> )
> SELECT
> x.txt,
>
> CASE
> WHEN x.a & 0x01
> THEN 'A'
> ELSE ''
> END
> || ' ' ||
> CASE
> WHEN x.a & 0x10
> THEN 'B'
> ELSE ''
> END AS result1,
>
> CASE x.a & 0xff
> WHEN 0x01
> THEN 'A'
> ELSE ''
> END
> || ' ' ||
> CASE x.a & 0xff
> WHEN 0x10
> THEN 'B'
> ELSE ''
> END AS result2
> FROM
> x
> ;
>
> And the results:
>
> txt result1 result2
> --  --  --
> 0x01A   A
> 0x10 B   B
> 0x01 | 0x1  A B
>
> --
> MarkeLawrence
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] decoding a bitmask

[Mark Lawrence]

My apologies for the previous completely wrong mesage. I got mixed up
with operator meaning & precedence...

On Mon Oct 13, 2014 at 02:39:40PM +0100, Paul Sanderson wrote:
> 
> My actual code is as folows
> 
>   (CASE visits.transition & 0xFF00  WHEN 0x0080 THEN 'Blocked'
> ELSE '' END ||

But I can at least put some better light on your issue. SQLite returns
different results for your style of case/when construct:

CASE expr WHEN val THEN... 

and the alternative which I find reads easier:

CASE WHEN truth_expr THEN...

Here's a demonstration:

WITH x
AS (
SELECT
0x01 AS a,
'0x01' AS txt
UNION ALL SELECT
0x10,
'0x10'
UNION ALL SELECT
0x01 | 0x10,
'0x01 | 0x10'
)
SELECT
x.txt,

CASE
WHEN x.a & 0x01
THEN 'A'
ELSE ''
END
|| ' ' ||
CASE
WHEN x.a & 0x10
THEN 'B'
ELSE ''
END AS result1,

CASE x.a & 0xff
WHEN 0x01
THEN 'A'
ELSE ''
END
|| ' ' ||
CASE x.a & 0xff
WHEN 0x10
THEN 'B'
ELSE ''
END AS result2
FROM
x
;

And the results:

txt result1 result2   
--  --  --
0x01A   A 
0x10 B   B
0x01 | 0x1  A B   

-- 
MarkeLawrence
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] decoding a bitmask

[Doug Currie]

>
> The query is on a visits table from a google chrome history database. The
> query seems to work OK if a single bit is set, but fails (a blank string is
> returned) when multiple bits are set. Any ideas why?
>

It's because none of the WHEN 0x... cases, except 0xC0...,  have multiple
bits set. The approach you've chosen requires enumerating all the possible
combinations (all 2^5 of them in this case). You are better off with one of
the other suggested approaches by Richard Hipp or RSmith.

e
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] decoding a bitmask

[Clemens Herschel]

Hi,
I used to bitwise stuff in Assembler  in the 60's when disk space was 
dear. Do not know your environment. But maybe my observations will help.
CASE stops after one conditions is met and does not test other cases. 
The next line executed is after the CASEs
 When I worked with bits, I would look at the sum of the bits. So a 1st 
bit and a 2nd bit set has a sum  3. A 1st and a 3rd has a sum of 5.

Generally do not answer to list but hope this helps.
Clemens Herschel ( a different Clemens)
On 10/13/14, 9:39 AM, Paul Sanderson wrote:

Thanks all

Clemens - I went initially for your solution as it fitsbetter with some
other work i have done

My actual code is as folows

   (CASE visits.transition & 0xFF00  WHEN 0x0080 THEN 'Blocked'
 ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x0100 THEN
'Forward_Back'  ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x0200 THEN
'From_Address_Bar' ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x0400 THEN 'Home_Page'
 ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x0800 THEN 'From_API'
 ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x1000 THEN 'Chain_Start'
 ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x2000 THEN 'Chain_end'
 ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x4000 THEN
'Client_Redirect'  ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x8000 THEN
'Server_Redirect'  ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0xC000 THEN
'Is_Redirect_Mask' ELSE '' END )
   AS Qualifiers

The query is on a visits table from a google chrome history database. The
query seems to work OK if a single bit is set, but fails (a blank string is
returned) when multiple bits are set. Any ideas why?

Thanks


Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit

-SQLite Forensic Toolkit
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy


On 13 October 2014 13:17, Clemens Ladisch  wrote:


Paul Sanderson wrote:

I have a table with an integer value which is a bitmask.

0c01 readonly
0x02 hidden
0x04 system
0x10 directory
0x20 archive

I'd like to create a query which would take an attribute, say 0x07 and

spit

out "system, hidden, readonly"

SELECT substr(CASE WHEN attr & 32 THEN ', archive'   ELSE '' END ||
   CASE WHEN attr & 16 THEN ', directory' ELSE '' END ||
   CASE WHEN attr &  4 THEN ', system'ELSE '' END ||
   CASE WHEN attr &  2 THEN ', hidden'ELSE '' END ||
   CASE WHEN attr &  1 THEN ', readonly'  ELSE '' END, 3)
FROM MyTable;


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] decoding a bitmask

[Igor Tandetnik]

On 10/13/2014 9:39 AM, Paul Sanderson wrote:

The
query seems to work OK if a single bit is set, but fails (a blank string is
returned) when multiple bits are set. Any ideas why?


CASE x WHEN y ... checks that x = y, not that x & y != 0. You are 
checking for equality with a one-bit value - so of course it only checks 
out when the other side of the comparison is also a one-bit value.


You want

CASE WHEN visits.transition & 0x0080 THEN 'Blocked' ELSE '' END

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] decoding a bitmask

[RSmith]

On 2014/10/13 15:39, Paul Sanderson wrote:

Thanks all

Clemens - I went initially for your solution as it fitsbetter with some
other work i have done

My actual code is as folows

   (CASE visits.transition & 0xFF00  WHEN 0x0080 THEN 'Blocked'
 ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x0100 THEN
'Forward_Back'  ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x0200 THEN
'From_Address_Bar' ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x0400 THEN 'Home_Page'
 ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x0800 THEN 'From_API'
 ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x1000 THEN 'Chain_Start'
 ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x2000 THEN 'Chain_end'
 ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x4000 THEN
'Client_Redirect'  ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x8000 THEN
'Server_Redirect'  ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0xC000 THEN
'Is_Redirect_Mask' ELSE '' END )
   AS Qualifiers

The query is on a visits table from a google chrome history database. The
query seems to work OK if a single bit is set, but fails (a blank string is
returned) when multiple bits are set. Any ideas why?


Rookie mistake :)

You are basically bitwise AND-ing 2 values together, such as (visits.transition & 0xFF00) and then checking the result against a 
single bit... this will never work unless the transition value has only a single bit set, I think you meant to AND the mask with the 
transition value and then check if the result is non-zero?


This works perfectly for me:

  (CASE WHEN (visits.transition & 0x0080)>0 THEN 'Blocked'
ELSE '' END ||... etc. etc.
 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] decoding a bitmask

[RSmith]

On 2014/10/13 16:51, Mark Lawrence wrote:

On Mon Oct 13, 2014 at 02:39:40PM +0100, Paul Sanderson wrote:

The query is on a visits table from a google chrome history database. The
query seems to work OK if a single bit is set, but fails (a blank string is
returned) when multiple bits are set. Any ideas why?

I suspect it is a bug with multiple bitwise ORs. Demonstration:

 WITH x
 AS (
 SELECT
 0x0080 | 0x0800 AS a
 )
 SELECT
 a & 0x0080,
 a & 0x0800,
 a & 0x0080 & 0x0800
 FROM
 x
 ;

Result:

 a & 0x0080  a & 0x0800  a & 0x0080 & 0x0800
 --  --  ---
 8388608 134217728   0


Those look like exactly the correct results to me, how did you expect the 
result to be?



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] decoding a bitmask

[Clemens Ladisch]

Paul Sanderson wrote:
>   (CASE visits.transition & 0xFF00  WHEN 0x0080 THEN 'Blocked'   
> ELSE '' END ||
>CASE visits.transition & 0xFF00  WHEN 0x0100 THEN 'Forward_Back'  
> ELSE '' END ||
>...
>
> The query seems to work OK if a single bit is set, but fails (a blank string
> is returned) when multiple bits are set. Any ideas why?

When multiple bits are set, the value of visits.transition & 0xFF00
is something like 0x0180.

Don't bother with the extra bit mask, just check individual bits:

  (CASE WHEN visits.transition & 0x0080 THEN 'Blocked'   ELSE '' END ||
   CASE WHEN visits.transition & 0x0100 THEN 'Forward_Back'  ELSE '' END ||
   ...


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] decoding a bitmask

[Mark Lawrence]

On Mon Oct 13, 2014 at 04:51:16PM +0200, Mark Lawrence wrote:
> On Mon Oct 13, 2014 at 02:39:40PM +0100, Paul Sanderson wrote:
> 
> Perl equivalent:
> 
> use feature 'say';
> my $a = 0x0080 | 0x0800;
> 
> say $a & 0x0080;
> say $a & 0x0800;
> say $a & 0x0800 & 0x0800;

Sorry, my comment was invalid. The above is incorrect - I mixed up the
8's and the 0's.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] decoding a bitmask

[Mark Lawrence]

On Mon Oct 13, 2014 at 02:39:40PM +0100, Paul Sanderson wrote:
> 
> The query is on a visits table from a google chrome history database. The
> query seems to work OK if a single bit is set, but fails (a blank string is
> returned) when multiple bits are set. Any ideas why?

I suspect it is a bug with multiple bitwise ORs. Demonstration:

WITH x
AS (
SELECT
0x0080 | 0x0800 AS a
)
SELECT
a & 0x0080,
a & 0x0800,
a & 0x0080 & 0x0800
FROM
x
;

Result:

a & 0x0080  a & 0x0800  a & 0x0080 & 0x0800
--  --  ---
8388608 134217728   0  

Perl equivalent:

use feature 'say';
my $a = 0x0080 | 0x0800;

say $a & 0x0080;
say $a & 0x0800;
say $a & 0x0800 & 0x0800;

Result:

8388608
134217728
134217728

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] decoding a bitmask

[Paul Sanderson]

Thanks all

Clemens - I went initially for your solution as it fitsbetter with some
other work i have done

My actual code is as folows

  (CASE visits.transition & 0xFF00  WHEN 0x0080 THEN 'Blocked'
ELSE '' END ||
   CASE visits.transition & 0xFF00  WHEN 0x0100 THEN
'Forward_Back'  ELSE '' END ||
   CASE visits.transition & 0xFF00  WHEN 0x0200 THEN
'From_Address_Bar' ELSE '' END ||
   CASE visits.transition & 0xFF00  WHEN 0x0400 THEN 'Home_Page'
ELSE '' END ||
   CASE visits.transition & 0xFF00  WHEN 0x0800 THEN 'From_API'
ELSE '' END ||
   CASE visits.transition & 0xFF00  WHEN 0x1000 THEN 'Chain_Start'
ELSE '' END ||
   CASE visits.transition & 0xFF00  WHEN 0x2000 THEN 'Chain_end'
ELSE '' END ||
   CASE visits.transition & 0xFF00  WHEN 0x4000 THEN
'Client_Redirect'  ELSE '' END ||
   CASE visits.transition & 0xFF00  WHEN 0x8000 THEN
'Server_Redirect'  ELSE '' END ||
   CASE visits.transition & 0xFF00  WHEN 0xC000 THEN
'Is_Redirect_Mask' ELSE '' END )
  AS Qualifiers

The query is on a visits table from a google chrome history database. The
query seems to work OK if a single bit is set, but fails (a blank string is
returned) when multiple bits are set. Any ideas why?

Thanks


Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit

-SQLite Forensic Toolkit
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy


On 13 October 2014 13:17, Clemens Ladisch  wrote:

> Paul Sanderson wrote:
> > I have a table with an integer value which is a bitmask.
> >
> > 0c01 readonly
> > 0x02 hidden
> > 0x04 system
> > 0x10 directory
> > 0x20 archive
> >
> > I'd like to create a query which would take an attribute, say 0x07 and
> spit
> > out "system, hidden, readonly"
>
> SELECT substr(CASE WHEN attr & 32 THEN ', archive'   ELSE '' END ||
>   CASE WHEN attr & 16 THEN ', directory' ELSE '' END ||
>   CASE WHEN attr &  4 THEN ', system'ELSE '' END ||
>   CASE WHEN attr &  2 THEN ', hidden'ELSE '' END ||
>   CASE WHEN attr &  1 THEN ', readonly'  ELSE '' END, 3)
> FROM MyTable;
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] decoding a bitmask

[Clemens Ladisch]

Paul Sanderson wrote:
> I have a table with an integer value which is a bitmask.
>
> 0c01 readonly
> 0x02 hidden
> 0x04 system
> 0x10 directory
> 0x20 archive
>
> I'd like to create a query which would take an attribute, say 0x07 and spit
> out "system, hidden, readonly"

SELECT substr(CASE WHEN attr & 32 THEN ', archive'   ELSE '' END ||
  CASE WHEN attr & 16 THEN ', directory' ELSE '' END ||
  CASE WHEN attr &  4 THEN ', system'ELSE '' END ||
  CASE WHEN attr &  2 THEN ', hidden'ELSE '' END ||
  CASE WHEN attr &  1 THEN ', readonly'  ELSE '' END, 3)
FROM MyTable;


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] decoding a bitmask

[RSmith]

On 2014/10/13 13:52, Paul Sanderson wrote:

I have a table with an integer value which is a bitmask. one or more of the
bits can be set and each bit has a corresponding meaning.

so using the windows file attribute as an example we have

0c01 readonly
0x02 hidden
0x04 system
0x10 directory
0x20 archive

none, any or all could be set

I'd like to create a query which would take an attribute, say 0x07 and spit
out "system, hidden, readonly"


Or you could make a table like this:

CREATE TABLE "fileFlags" (
"ID" INTEGER PRIMARY KEY,
 "Descr" TEXT
);

Populate it with the Mask descriptions like so:

INSERT INTO "fileFlags" ("ID","Descr") VALUES
(1,'readonly'),
(2,'hidden'),
(4,'system'),
(8,'unknown'),
(16,'directory'),
(32,'archive');

And then any query of the form:

SELECT F.Descr FROM fileFlags AS F WHERE (F.ID & [BitValue])>0;

Will work, as the following tests shown:

SELECT F.Descr FROM fileFlags AS F WHERE (F.ID & 11)>0
readonly
hidden
unknown


SELECT F.Descr FROM fileFlags AS F WHERE (F.ID & 7)>0
readonly
hidden
system

SELECT F.Descr FROM fileFlags AS F WHERE (F.ID & 4)>0
system

etc.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] decoding a bitmask

[Richard Hipp]

On Mon, Oct 13, 2014 at 7:52 AM, Paul Sanderson <
sandersonforens...@gmail.com> wrote:

> I have a table with an integer value which is a bitmask. one or more of the
> bits can be set and each bit has a corresponding meaning.
>
> so using the windows file attribute as an example we have
>
> 0c01 readonly
> 0x02 hidden
> 0x04 system
> 0x10 directory
> 0x20 archive
>
> none, any or all could be set
>
> I'd like to create a query which would take an attribute, say 0x07 and spit
> out "system, hidden, readonly"
>


WITH RECURSIVE
  all_bit_patterns(x) AS (VALUES(0) UNION ALL
  SELECT x+1 FROM all_bit_patterns
  WHERE x<0x1f)
SELECT x,
   (SELECT group_concat(name) FROM
  (SELECT 0x01 AS id, 'readonly' AS name
   UNION ALL SELECT 0x02, 'hidden'
   UNION ALL SELECT 0x04, 'system'
   UNION ALL SELECT 0x08, 'directory'
   UNION ALL SELECT 0x10, 'archive')
WHERE (x)!=0)
 FROM all_bit_patterns;

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] decoding a bitmask

[Paul Sanderson]

I have a table with an integer value which is a bitmask. one or more of the
bits can be set and each bit has a corresponding meaning.

so using the windows file attribute as an example we have

0c01 readonly
0x02 hidden
0x04 system
0x10 directory
0x20 archive

none, any or all could be set

I'd like to create a query which would take an attribute, say 0x07 and spit
out "system, hidden, readonly"

Cheers
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users