As I understand it, "IN" presents an exact match, case sensitive
comparison. Using LIKE was thought to get a list of substrings that could
be anywhere in any string found within the Skip field. So if a row in the
Skip field contained "%Apache HTTP%" then I'd like to see, or not see,
depending on the NOT being present in the outer query, the appropriate
results. For example, if I did not want to see any Remediation with the
text "Apache HTTP", I'd enter as a row in Skip "Apache HTTP" or "%Apache
HTTP%" and the query I provided would give me all results that do NOT
contain "Apache HTTP" in PMEScan. The "Not seeing" portion was to get rid
of visual garbage so I can further refine the result set, and make sure I'm
adding criteria I need to tag into the ticket.
Everything is plain text, defined in the table def as just CHAR, probably
stored as Latin1 or UTF8, except the Ticket which is defined as an
integer... but.. knowing SQLite and it not really caring about field
types....
An example row in TWScan (Renamed from PMEScan) would be:
ExtIP IntIP Service VulnName Desc Remediation Port
Severity CVE Ticket
x.x.x.x y.y.y.y SomeWebService_Public "Apache HTTP Server
mod_log_config Denial of Service Vulnerability" "Apache HTTP Server
contains a vulnerability in the mod_log_config component that could allow a
remote attacker to crash the web server." "This vulnerability was fixed
with the release of versions 2.4.9 and 2.2.27 of Apache HTTP Server.
However, it is recommended that you upgrade to the latest available
release." 443 M CVE-2014-0098 0
An example row in SkipRemed would be
Skip
Apache HTTP
%Apache HTTP%
The query I'm using to get the list of services & existing Remediations:
select distinct VulnName,Remediation from TWScan where Remediation not in
(select distinct Skip from SkipRemed) and Severity in ('M','H') and Ticket
is null order by Remediation like '%Apache%' desc, upper(Remediation);
(This sorts anything to do with Apache near the top of the result list,
then sorts based on text)
On Wed, Apr 19, 2017 at 3:30 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 19 Apr 2017, at 8:12pm, Stephen Chrzanowski <pontia...@gmail.com>
> wrote:
>
> > The query I've been messing with is this:
> >
> > *select distinct ExtIP, IntIP, Service,Remediation from PMEScan where
> > Remediation not like (select distinct Skip from SkipRemed) order by
> > upper(Desc),upper(Service)*
>
> I would have expected
>
> select distinct ExtIP, IntIP, Service,Remediation from PMEScan where
> Remediation NOT IN (SELECT Skip from SkipRemed) order by
> upper(Desc),upper(Service)
>
> > I don't get the results I want, unless I use the actual full text of the
> > Remediation text. I've changed Skip to '%'||Skip||'%' in the subquery
> but
> > that doesn't get me the results I want either.
>
> What are the affinities of the Remediation and Skip columns ?
>
> What is it doing ? Skipping ones you want to include, or including ones
> you want to skip ? Both ? Can you give examples of the Remediation and
> Skip values ?
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
