Postgresql – Wrong return results

postgresqlpostgresql-9.2

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.                                                                 +|

Best Answer

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.

Related Question