Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to grab all the rows that have a risk of critical or high, with the discription or synopsis or solution or cve like password. But it keeps showing all rows not just rows with a risk of critical or high.

SELECT host,plugin_id,min(cve) as cve,risk,synopsis,description,solution, count(distinct(plugin_id)) from internal_network where risk in ('Critical','High') and description like '%password%' or synopsis like '%password%' or solution like '%password%' or cve like '%password%' group by risk,plugin_id,host,synopsis,description,solution;

192.168.21.128 |     17651 |               | None     | It is possible to retrieve the remote host's password policy using the+| Using the supplied credentials it was possible to extract the                  +|
n/a                                                                    |     1
            |           |               |          | supplied credentials.                                                     | password policy for the remote Windows host.  The password policy must         +|
                                                                    | 
            |           |               |          |                                                                        | conform to the Informational System Policy.                                     |
                                                                    | 
192.168.25.126 |     17651 |               | None     | It is possible to retrieve the remote host's password policy using the+| Using the supplied credentials it was possible to extract the                  +|
n/a                                                                    |     1
            |           |               |          | supplied credentials.                                                  | password policy for the remote Windows host.  The password policy must         +|
                                                                    | 
            |           |               |          |                                                                        | conform to the Informational System Policy.                                     |

If I execute the follow query I get the correct return.

 SELECT host,plugin_id,min(cve) as cve,risk,synopsis,description,solution, count(distinct(plugin_id)) from internal_network where risk in ('Critical','High') and description like '%password%' group by risk,plugin_id,host,synopsis,description,solution;


      host      | plugin_id |      cve      |   risk   |                            synopsis                            |                                   description                                   |        
           solution                        | count 
----------------+-----------+---------------+----------+----------------------------------------------------------------+---------------------------------------------------------------------------------+--------
-----------------------------------------------+-------
172.18.12.150  |     11255 | CVE-1999-0502 | Critical | An account on the remote host uses a known password.           | The account 'root' on the remote host has the password 'root'. An              +| Change 
the password for this account or disable it.   |     1
            |           |               |          |                                                                | attacker may leverage this issue to gain total control of the affected         +|        
                                           | 
            |           |               |          |                                                                | system.                                                                         |        
                                           | 
10.124.2.10    |     61708 |               | Critical | A VNC server running on the remote host is secured with a weak+| The VNC server running on the remote host is secured with a weak               +| Secure 
the VNC service with a strong password.        |     1
            |           |               |          | password.                                                      | password.  Nessus was able to login using VNC authentication and a             +|        
                                           | 
            |           |               |          |                                                                | password of 'password'.  A remote, unauthenticated attacker could              +|        
                                           | 
            |           |               |          |                                                                | exploit this to take control of the system.                                     |        
                                           | 
172.18.12.130  |     10205 | CVE-1999-0651 | High     | The rlogin service is listening on the remote port.            | The remote host is running the 'rlogin' service.  This service is dangerous in +| Comment
 out the 'login' line in /etc/inetd.conf       |     1
            |           |               |          |                                                                | the sense that it is not ciphered - that is, everyone can sniff the data that  +|        
                                           | 
            |           |               |          |                                                                | passes between the rlogin client and the rloginserver. This includes logins    +|        
                                           | 
            |           |               |          |                                                                | and passwords.                                                                 +|        
share|improve this question
3  
It's good that you tried to post sample data - but this is formatted in a way that it's completely unreadable. Please fix this. –  a_horse_with_no_name Jun 25 '13 at 19:59

1 Answer 1

up vote 3 down vote accepted

Your first query is equivalent of the following (notice the parentheses in the WHERE clause):

SELECT 
    host,
    plugin_id,
    min(cve) as cve,
    risk,
    synopsis,
    description,
    solution, 
    count(distinct(plugin_id)) 
from internal_network 
where 
    (risk in ('Critical','High') and description like '%password%') 
    or synopsis like '%password%' 
    or solution like '%password%' 
    or cve like '%password%' 
group by risk, plugin_id, host, synopsis, description, solution;

This is because the AND operator has higher precedence than the OR, that is, it 'glues' its operands stronger. I think what you wanted is

...
where 
    risk in ('Critical','High') 
    and 
    (
    description like '%password%' 
    or synopsis like '%password%' 
    or solution like '%password%' 
    or cve like '%password%' 
    )
...

instead.

Unless you have only ANDs (or ORs), it is always adviseable to use parentheses. It helps understand the expression, especially if it is formatted correctly.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.