#112 Database Error with BASE when viewing unique events

BASE
closed-fixed
Nerveup
Interface (166)
1
2006-01-27
2005-09-28
wavecrazed
No

I am using Windows 2003 with MS SQL 2000 SP4,
PHP 5.0.5, Base 1.1.4, and snort 2.4.0 for windows.
Everything works well except of anytime I try to query
unique alerts (or have any reason to call a function in
base_stat_alerts.php).

The exact database error I get is:

Database ERROR:Database ERROR:The text, ntext,
and image data types cannot be compared or sorted,
except when using IS NULL or LIKE operator.

Any ideas?

Thanks

Discussion

1 2 > >> (Page 1 of 2)
  • Kevin Johnson
    Kevin Johnson
    2005-09-29

    Logged In: YES
    user_id=836228

    It appears that we have an error with MSSQL support on that
    page. The big problem is that I do not believe any of the
    developers have MSSQL to test against. I know that I don't
    due to the licensing costs....

    We will try to fix it but we would need help from someone
    who either has MSSQL or can somehow get us a LEGAL copy of
    the software.

    Kevin

     
  • Kevin Johnson
    Kevin Johnson
    2005-09-29

    • priority: 5 --> 1
     
  • Logged In: NO

    MSDE 2000 is available for download from MS at no charge
    and has a lighter footprint than SQL Standard. It should
    suffice for testing.

     
  • Kevin Johnson
    Kevin Johnson
    2005-11-01

    Logged In: YES
    user_id=836228

    Yes, MSDE is available for download, but it does require
    Windows to test on.... I will see if I can find a old copy
    of Windows to install in a VM.

    Kevin

     
  • Logged In: NO

    Microsoft has eval copies of Windows 2003 Server for 180
    days of use. I can send you a copy of one of mine.
    Otherwise you can request one from there website. I think
    you have to pay shipping but it is minimal. Let me know if
    you would like me to provide. I would be happy to contribute
    in that way.

     
  • Kevin Johnson
    Kevin Johnson
    2005-11-08

    Logged In: YES
    user_id=836228

    I appreciate the offer... BUT, we need to be able to support
    this into the future. I am looking at my options and I do
    not believe that using trial software is a long term
    solution. I am looking into purchasing the software needed,
    Unless someone would like to take responsibilty for testing
    under Windows with MSSQL.

    Kevin

     
  • monkey tribe
    monkey tribe
    2005-11-24

    Logged In: YES
    user_id=1152576

    Having the same problem myself.
    Appears to happen in different places in Kris 1.2.1 than
    in other versions.
    (Schema Version: 106)
    MS SQL 2000 SP4
    2003 SP 1
    IIS 6.0 PHP 5.0.4.4 ISAPI

    Error appears in Kris :
    - Today's alerts: unique
    - Most recent 15 Unique Alerts
    - Most frequent 5 Unique Alerts
    - Last 72 hours :unique
    - Unique Alerts:
    Everything else from the front page is fine
    Some Drill down Examples
    Sensors/Total: / unique events
    Categories: / Signature
    Src IP addrs: / Unique Alerts
    Unique IP links / Unique Alerts

    and get
    Source Ports: / Unique Alerts
    Database ERROR:Database ERROR:ORDER BY items must appear
    in the select list if SELECT DISTINCT is specified.

    Errors In Cheryl 1.1.4 :
    - Today's alerts: unique
    - Last 24 Hours alerts: unique
    - Last 72 hours alerts :unique
    - Most recent 15 Unique Alerts
    - Most frequent 5 Unique Alerts
    Unique Alerts: XXX

    Errors in Lynn 1.1.3 :
    - Today's alerts: unique
    - Last 24 Hours alerts: unique
    - Last 72 hours alerts :unique
    - Most recent 15 Unique Alerts
    - Most frequent 5 Unique Alerts
    Unique Alerts: XXX
    Sensor/ Unique events

    I am currently using Zora 1.1.2
    Which seems to work well on most things, some errors if
    you drill down through several selects but can't remember
    which ones exactly.
    Am not really a programmer, so I am currently using
    windiff
    to see what's different are ill let you know if anything
    obvious leaps out.
    I don't mind testing some thing if any one can think of a
    fix.
    Fantastic system mind you.

     
  • monkey tribe
    monkey tribe
    2005-11-24

    Logged In: YES
    user_id=1152576

    Think I may have found the bit that's creating the error
    but don't really know what to do with it.
    In Base_stat_alerts.php
    In Cheryl its
    "min(timestamp), max(timestamp), sig_name, count(DISTINCT
    (sid)), count(DISTINCT(ip_src)), count(DISTINCT(ip_dst)) ".
    $sort_sql[0].$from.$where." GROUP BY signature,
    sig_name ".$sort_sql[1];
    in Zora its
    "min(timestamp), max(timestamp) ".
    $sort_sql[0].$from.$where." GROUP BY signature ".$sort_sql
    [1];

    Works fine in Cheryl so far but haven't done a lot of
    testing.
    Tried swapping them in Kris but it just gives me one sig
    output and strange numbers.
    Displaying 15 Last Alerts

    < Signature > < Classification > < Total # >
    Sensor # < Source Address > < Dest. Address > <
    First > < Last >
    [local] [snort] BLEEDING-EDGE HTTP CONNECT Tunnel Attempt
    misc-activity 3960(36%) 169141810 6 66 2005-11-23
    10:14:27
    [local] [snort] BLEEDING-EDGE HTTP CONNECT Tunnel Attempt
    misc-activity 3961(36%) 3368576125 6 83 2005-11-23
    10:14:42
    [local] [snort] BLEEDING-EDGE HTTP CONNECT Tunnel Attempt
    misc-activity 3962(36%) 3368576125 6 83 2005-11-23
    10:14:42
    [local] [snort] BLEEDING-EDGE HTTP CONNECT Tunnel Attempt
    misc-activity 3963(36%) 3368576125 6 83 2005-11-23
    10:16:48
    [local] [snort] BLEEDING-EDGE HTTP CONNECT Tunnel Attempt
    misc-activity 3964(36%) 3368576125 6 83 2005-11-23
    10:16:48

    Hope this helps, if I am way off course just say.

     
  • Nerveup
    Nerveup
    2006-01-21

    • assigned_to: nobody --> nerveup
     
  • Nerveup
    Nerveup
    2006-01-27

    Logged In: YES
    user_id=1429350

    Thankyou, wavecrazed and m0nk3yb0y for reporting!
    Bug is fixed in cvs version.
    It would be good, if you could test and report back.
    Latest snapshot available at:
    ftp://ftp.secure.lv/pub/BASE/base-SNAP-20060127.tar.gz

     
1 2 > >> (Page 1 of 2)