Open Eagleman7 opened 7 years ago
It looks like https://code.djangoproject.com/ticket/16426. So it is a sqlite limitation hit by Django code. I'm gonna look if there is some workaround to that.
@Eagleman7 I've just pushed a workaround attempt at https://github.com/StamusNetworks/scirius/tree/sqlite-delete
I still don't know why you are hitting that. Just tested it using Amsterdam and it works fine.
Same issue after adding your new code:
OperationalError at /rules/source/29/delete
too many SQL variables
Request Method: POST
Request URL: http://ips.corp.domain.com:8000/rules/source/29/delete
Django Version: 1.8.17
Exception Type: OperationalError
Exception Value:
too many SQL variables
Exception Location: /usr/lib/python2.7/site-packages/django/db/backends/sqlite3/base.py in execute, line 318
Python Executable: /bin/python
Python Version: 2.7.5
Python Path:
['/usr/lib/python2.7/site-packages/git/ext/gitdb',
'/opt/scirius',
'/usr/lib64/python27.zip',
'/usr/lib64/python2.7',
'/usr/lib64/python2.7/plat-linux2',
'/usr/lib64/python2.7/lib-tk',
'/usr/lib64/python2.7/lib-old',
'/usr/lib64/python2.7/lib-dynload',
'/usr/lib64/python2.7/site-packages',
'/usr/lib64/python2.7/site-packages/gtk-2.0',
'/usr/lib/python2.7/site-packages',
'/usr/lib64/python2.7/site-packages/gitdb/ext/smmap']
Server time: Sun, 5 Feb 2017 20:52:23 +0100
Traceback Switch to copy-and-paste view
/usr/lib/python2.7/site-packages/django/core/handlers/base.py in get_response response = wrapped_callback(request, *callback_args, **callback_kwargs) ... ▶ Local vars /opt/scirius/rules/views.py in delete_source source.delete() ... ▶ Local vars /opt/scirius/rules/models.py in delete category.delete() ... ▶ Local vars /usr/lib/python2.7/site-packages/django/db/models/base.py in delete collector.delete() ... ▶ Local vars /usr/lib/python2.7/site-packages/django/db/models/deletion.py in delete qs._raw_delete(using=self.using) ... ▶ Local vars /usr/lib/python2.7/site-packages/django/db/models/query.py in _raw_delete sql.DeleteQuery(self.model).delete_qs(self, using) ... ▶ Local vars /usr/lib/python2.7/site-packages/django/db/models/sql/subqueries.py in delete_qs self.get_compiler(using).execute_sql(NO_RESULTS) ... ▶ Local vars /usr/lib/python2.7/site-packages/django/db/models/sql/compiler.py in execute_sql cursor.execute(sql, params) ... ▶ Local vars /usr/lib/python2.7/site-packages/django/db/backends/utils.py in execute return super(CursorDebugWrapper, self).execute(sql, params) ... ▶ Local vars /usr/lib/python2.7/site-packages/django/db/backends/utils.py in execute return self.cursor.execute(sql, params) ... ▶ Local vars /usr/lib/python2.7/site-packages/django/db/utils.py in exit six.reraise(dj_exc_type, dj_exc_value, traceback) ... ▶ Local vars /usr/lib/python2.7/site-packages/django/db/backends/utils.py in execute return self.cursor.execute(sql, params) ... ▶ Local vars /usr/lib/python2.7/site-packages/django/db/backends/sqlite3/base.py in execute return Database.Cursor.execute(self, query, params) ... ▶ Local vars Request information
GET
No GET data
POST
Variable Value
comment
u''
csrfmiddlewaretoken
u'YFgaqp48yOl5T8cWrYBcJx1v88tOfgnb'
FILES
No FILES data
COOKIES
Variable Value
csrftoken
'YFgaqp48yOl5T8cWrYBcJx1v88tOfgnb'
sessionid
'm40xxwtqpehxxtdt82ei6xwetvsr4bq4'
META
Variable Value
RUN_MAIN
'true'
HTTP_REFERER
'http://ips.corp.domain.com:8000/rules/source/29/delete'
SERVER_PROTOCOL
'HTTP/1.1'
SERVER_SOFTWARE
'WSGIServer/0.1 Python/2.7.5'
SCRIPT_NAME
u''
REQUEST_METHOD
'POST'
LOGNAME
'root'
USER
'root'
HTTP_ORIGIN
'http://ips.corp.domain.com:8000'
PATH
'/sbin:/bin:/usr/sbin:/usr/bin'
QUERY_STRING
''
HOME
'/root'
MAILTO
'root'
SHELL
'/bin/bash'
TZ
'UTC'
HTTP_COOKIE
'sessionid=m40xxwtqpehxxtdt82ei6xwetvsr4bq4; csrftoken=YFgaqp48yOl5T8cWrYBcJx1v88tOfgnb'
SERVER_NAME
'ips.corp.domain.com'
REMOTE_ADDR
'192.168.2.12'
SHLVL
'1'
wsgi.url_scheme
'http'
SERVER_PORT
'8000'
CONTENT_LENGTH
'61'
LANG
'en_US.UTF-8'
HTTP_CONNECTION
'keep-alive'
HTTP_PRAGMA
'no-cache'
XDG_RUNTIME_DIR
'/run/user/0'
wsgi.input
<socket._fileobject object at 0x7f76e4366e50>
HTTP_USER_AGENT
'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Safari/537.36'
HTTP_HOST
'ips.corp.domain.com:8000'
wsgi.multithread
True
HTTP_UPGRADE_INSECURE_REQUESTS
'1'
HTTP_CACHE_CONTROL
'no-cache'
XDG_SESSIONID
'1'
'/bin/python'
HTTP_ACCEPT
'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,/;q=0.8'
wsgi.version
(1, 0)
GATEWAY_INTERFACE
'CGI/1.1'
wsgi.run_once
False
CSRF_COOKIE
u'YFgaqp48yOl5T8cWrYBcJx1v88tOfgnb'
wsgi.errors
<open file '
False
HTTP_ACCEPT_LANGUAGE
'nl-NL,nl;q=0.8,en-US;q=0.6,en;q=0.4'
PWD
'/root'
DJANGO_SETTINGS_MODULE
'scirius.settings'
CONTENT_TYPE
'application/x-www-form-urlencoded'
wsgi.file_wrapper
''
REMOTE_HOST
''
HTTP_ACCEPT_ENCODING
'gzip, deflate'
PATH_INFO
u'/rules/source/29/delete'
Settings
Using settings module scirius.settings
Setting Value
SECURE_BROWSER_XSS_FILTER
False
USE_THOUSAND_SEPARATOR
False
CSRF_COOKIE_SECURE
False
LANGUAGE_CODE
'en-us'
ROOT_URLCONF
'scirius.urls'
MANAGERS
()
BASE_DIR
'/opt/scirius'
SILENCED_SYSTEM_CHECKS
[]
DEFAULT_CHARSET
'utf-8'
INFLUXDB_PASSWORD
u'****'
SESSION_SERIALIZER
'django.contrib.sessions.serializers.PickleSerializer'
STATIC_ROOT
None
ELASTICSEARCH_HOSTNAME
'host'
ALLOWED_HOSTS
['ips.corp.domain.com']
SCIRIUS_VERSION
'1.2.0'
EMAIL_HOST
'localhost'
MESSAGE_STORAGE
'django.contrib.messages.storage.fallback.FallbackStorage'
EMAIL_SUBJECT_PREFIX
'[Django] '
SERVER_EMAIL
'root@localhost'
SECURE_HSTS_SECONDS
0
KIBANA_INDEX
'.kibana'
STATICFILES_FINDERS
('django.contrib.staticfiles.finders.FileSystemFinder',
'django.contrib.staticfiles.finders.AppDirectoriesFinder')
SESSION_CACHE_ALIAS
'default'
SESSION_COOKIE_DOMAIN
None
SESSION_COOKIE_NAME
'sessionid'
LOCALE_PATHS
()
TIME_INPUT_FORMATS
('%H:%M:%S', '%H:%M:%S.%f', '%H:%M')
SECURE_REDIRECT_EXEMPT
[]
DATABASES
{'default': {'ATOMIC_REQUESTS': False,
'AUTOCOMMIT': True,
'CONN_MAX_AGE': 0,
'ENGINE': 'django.db.backends.sqlite3',
'HOST': '',
'NAME': '/opt/scirius/db.sqlite3',
'OPTIONS': {},
'PASSWORD': u'****',
'PORT': '',
'TEST': {'CHARSET': None,
'COLLATION': None,
'MIRROR': None,
'NAME': None},
'TIME_ZONE': 'UTC',
'USER': ''}}
EMAIL_SSL_KEYFILE
u'****'
FILE_UPLOAD_DIRECTORY_PERMISSIONS
None
FILE_UPLOAD_PERMISSIONS
None
SURICATA_UNIX_SOCKET
None
FILE_UPLOAD_HANDLERS
('django.core.files.uploadhandler.MemoryFileUploadHandler',
'django.core.files.uploadhandler.TemporaryFileUploadHandler')
TEMPLATE_CONTEXT_PROCESSORS
('django.core.context_processors.request',
'django.contrib.auth.context_processors.auth',
'django.template.context_processors.tz')
DEFAULT_CONTENT_TYPE
'text/html'
APPEND_SLASH
True
FIRST_DAY_OF_WEEK
0
DATABASE_ROUTERS
[]
ELASTICSEARCH_VERSION
2
USE_INFLUXDB
False
ELASTICSEARCH_KEYWORD
u'****'
DBBACKUP_STORAGE_OPTIONS
{'location': '/opt/scirius/backups/'}
YEAR_MONTH_FORMAT
'F Y'
STATICFILES_STORAGE
'django.contrib.staticfiles.storage.StaticFilesStorage'
CACHES
{'default': {'BACKEND': 'django.core.cache.backends.db.DatabaseCache',
'LOCATION': 'my_cache_table'}}
SESSION_COOKIE_PATH
'/'
SECURE_CONTENT_TYPE_NOSNIFF
False
MIDDLEWARE_CLASSES
('django.contrib.sessions.middleware.SessionMiddleware',
'django.middleware.common.CommonMiddleware',
'django.middleware.csrf.CsrfViewMiddleware',
'django.contrib.auth.middleware.AuthenticationMiddleware',
'django.contrib.messages.middleware.MessageMiddleware',
'django.middleware.clickjacking.XFrameOptionsMiddleware',
'scirius.loginrequired.LoginRequiredMiddleware',
'scirius.utils.TimezoneMiddleware')
USE_I18N
True
THOUSAND_SEPARATOR
','
SECRET_KEY
u'****'
LANGUAGE_COOKIE_NAME
'django_language'
DEFAULT_INDEX_TABLESPACE
''
USE_PROXY
False
DBBACKUP_STORAGE
'dbbackup.storage.filesystem_storage'
DEFAULT_TABLESPACE
''
LOGGING_CONFIG
'logging.config.dictConfig'
TEMPLATE_LOADERS
('django.template.loaders.filesystem.Loader',
'django.template.loaders.app_directories.Loader')
WSGI_APPLICATION
'scirius.wsgi.application'
TEMPLATE_DEBUG
True
X_FRAME_OPTIONS
'SAMEORIGIN'
CSRF_COOKIE_NAME
'csrftoken'
INFLUXDB_HOST
'localhost'
INFLUXDB_USER
'grafana'
USE_X_FORWARDED_HOST
False
KIBANA_DASHBOARDS_PATH
'/opt/KTS/'
EMAIL_TIMEOUT
None
SECURE_SSL_HOST
None
SIGNING_BACKEND
'django.core.signing.TimestampSigner'
SESSION_COOKIE_SECURE
False
CACHE_MIDDLEWARE_KEY_PREFIX
u'****'
CSRF_COOKIE_DOMAIN
None
USE_EVEBOX
True
FILE_CHARSET
'utf-8'
DEBUG
True
LANGUAGE_COOKIE_DOMAIN
None
DEFAULT_FILE_STORAGE
'django.core.files.storage.FileSystemStorage'
INSTALLED_APPS
('django.contrib.admin',
'django.contrib.auth',
'django.contrib.contenttypes',
'django.contrib.sessions',
'django.contrib.messages',
'django.contrib.staticfiles',
'django_tables2',
'bootstrap3',
'dbbackup',
'rules',
'suricata',
'accounts',
'revproxy')
LANGUAGES
(('af', 'Afrikaans'),
('ar', 'Arabic'),
('ast', 'Asturian'),
('az', 'Azerbaijani'),
('bg', 'Bulgarian'),
('be', 'Belarusian'),
('bn', 'Bengali'),
('br', 'Breton'),
('bs', 'Bosnian'),
('ca', 'Catalan'),
('cs', 'Czech'),
('cy', 'Welsh'),
('da', 'Danish'),
('de', 'German'),
('el', 'Greek'),
('en', 'English'),
('en-au', 'Australian English'),
('en-gb', 'British English'),
('eo', 'Esperanto'),
('es', 'Spanish'),
('es-ar', 'Argentinian Spanish'),
('es-mx', 'Mexican Spanish'),
('es-ni', 'Nicaraguan Spanish'),
('es-ve', 'Venezuelan Spanish'),
('et', 'Estonian'),
('eu', 'Basque'),
('fa', 'Persian'),
('fi', 'Finnish'),
('fr', 'French'),
('fy', 'Frisian'),
('ga', 'Irish'),
('gl', 'Galician'),
('he', 'Hebrew'),
('hi', 'Hindi'),
('hr', 'Croatian'),
('hu', 'Hungarian'),
('ia', 'Interlingua'),
('id', 'Indonesian'),
('io', 'Ido'),
('is', 'Icelandic'),
('it', 'Italian'),
('ja', 'Japanese'),
('ka', 'Georgian'),
('kk', 'Kazakh'),
('km', 'Khmer'),
('kn', 'Kannada'),
('ko', 'Korean'),
('lb', 'Luxembourgish'),
('lt', 'Lithuanian'),
('lv', 'Latvian'),
('mk', 'Macedonian'),
('ml', 'Malayalam'),
('mn', 'Mongolian'),
('mr', 'Marathi'),
('my', 'Burmese'),
('nb', 'Norwegian Bokmal'),
('ne', 'Nepali'),
('nl', 'Dutch'),
('nn', 'Norwegian Nynorsk'),
('os', 'Ossetic'),
('pa', 'Punjabi'),
('pl', 'Polish'),
('pt', 'Portuguese'),
('pt-br', 'Brazilian Portuguese'),
('ro', 'Romanian'),
('ru', 'Russian'),
('sk', 'Slovak'),
('sl', 'Slovenian'),
('sq', 'Albanian'),
('sr', 'Serbian'),
('sr-latn', 'Serbian Latin'),
('sv', 'Swedish'),
('sw', 'Swahili'),
('ta', 'Tamil'),
('te', 'Telugu'),
('th', 'Thai'),
('tr', 'Turkish'),
('tt', 'Tatar'),
('udm', 'Udmurt'),
('uk', 'Ukrainian'),
('ur', 'Urdu'),
('vi', 'Vietnamese'),
('zh-cn', 'Simplified Chinese'),
('zh-hans', 'Simplified Chinese'),
('zh-hant', 'Traditional Chinese'),
('zh-tw', 'Traditional Chinese'))
USE_L10N
True
KIBANA_PROXY
True
SECURE_HSTS_INCLUDE_SUBDOMAINS
False
STATICFILES_DIRS
()
PREPEND_WWW
False
KIBANA_URL
'http://elk.corp.domain.com:5601'
LANGUAGE_COOKIE_AGE
None
SESSION_COOKIE_HTTPONLY
True
DEBUG_PROPAGATE_EXCEPTIONS
False
CSRF_COOKIE_AGE
31449600
MONTH_DAY_FORMAT
'F j'
LOGIN_URL
'/accounts/login/'
SESSION_EXPIRE_AT_BROWSER_CLOSE
False
TIME_FORMAT
'P'
AUTH_USER_MODEL
'auth.User'
DATE_INPUT_FORMATS
('%Y-%m-%d',
'%m/%d/%Y',
'%m/%d/%y',
'%b %d %Y',
'%b %d, %Y',
'%d %b %Y',
'%d %b, %Y',
'%B %d %Y',
'%B %d, %Y',
'%d %B %Y',
'%d %B, %Y')
INFLUXDB_PORT
8086
AUTHENTICATION_BACKENDS
('django.contrib.auth.backends.ModelBackend',)
EMAIL_HOST_PASSWORD
u'****'
PROXY_PARAMS
{'http': 'http://proxy:3128', 'https': 'http://proxy:3128'}
PASSWORD_RESET_TIMEOUT_DAYS
u'****'
SESSION_FILE_PATH
None
CACHE_MIDDLEWARE_ALIAS
'default'
SESSION_SAVE_EVERY_REQUEST
False
NUMBER_GROUPING
0
RULESET_TRANSFORMATIONS
(('reject', 'Reject'), ('drop', 'Drop'), ('filestore', 'Filestore'))
SESSION_ENGINE
'django.contrib.sessions.backends.db'
CSRF_FAILURE_VIEW
'django.views.csrf.csrf_failure'
CSRF_COOKIE_PATH
'/'
LOGIN_REDIRECT_URL
'/accounts/profile/'
USE_ELASTICSEARCH
True
DECIMAL_SEPARATOR
'.'
IGNORABLE_404_URLS
()
INFLUXDB_DATABASE
'scirius'
TEMPLATE_STRING_IF_INVALID
''
SURICATA_BINARY
'suricata'
USE_SURICATA_STATS
True
LOGOUT_URL
'/accounts/logout/'
EMAIL_USE_TLS
False
GIT_SOURCES_BASE_DIRECTORY
'/opt/scirius/git-sources/'
FIXTURE_DIRS
()
FORCE_SCRIPT_NAME
None
DATE_FORMAT
'N j, Y'
MEDIA_ROOT
''
DEFAULT_EXCEPTION_REPORTER_FILTER
'django.views.debug.SafeExceptionReporterFilter'
ADMINS
()
FORMAT_MODULE_PATH
None
DEFAULT_FROM_EMAIL
'webmaster@localhost'
USE_KIBANA
True
MEDIA_URL
''
DATETIME_FORMAT
'N j, Y, P'
TEMPLATE_DIRS
()
DISALLOWED_USER_AGENTS
()
ALLOWED_INCLUDE_ROOTS
()
ELASTICSEARCH_LOGSTASH_INDEX
'logstash-'
LOGGING
{}
SHORT_DATE_FORMAT
'm/d/Y'
IPWARE_PRIVATE_IP_PREFIX
()
TEMPLATES
[]
SECURE_PROXY_SSL_HEADER
None
TEST_RUNNER
'django.test.runner.DiscoverRunner'
KIBANA_DASHBOARDS_COUNT
20
SECURE_SSL_REDIRECT
False
TIME_ZONE
'UTC'
FILE_UPLOAD_MAX_MEMORY_SIZE
2621440
SCIRIUS_FLAVOR
'Scirius CE'
EMAIL_BACKEND
'django.core.mail.backends.smtp.EmailBackend'
EMAIL_USE_SSL
False
DATETIME_INPUT_FORMATS
('%Y-%m-%d %H:%M:%S',
'%Y-%m-%d %H:%M:%S.%f',
'%Y-%m-%d %H:%M',
'%Y-%m-%d',
'%m/%d/%Y %H:%M:%S',
'%m/%d/%Y %H:%M:%S.%f',
'%m/%d/%Y %H:%M',
'%m/%d/%Y',
'%m/%d/%y %H:%M:%S',
'%m/%d/%y %H:%M:%S.%f',
'%m/%d/%y %H:%M',
'%m/%d/%y')
KIBANA_VERSION
4
EVEBOX_ADDRESS
'ips.corp.domain.com:5636'
SESSION_COOKIE_AGE
1209600
SETTINGS_MODULE
'scirius.settings'
USE_ETAGS
False
LANGUAGES_BIDI
('he', 'ar', 'fa', 'ur')
FILE_UPLOAD_TEMP_DIR
None
INTERNAL_IPS
()
STATIC_URL
'/static/'
ELASTICSEARCH_LOGSTASH_TIMESTAMPING
'daily'
EMAIL_PORT
25
RULESET_MIDDLEWARE
'suricata'
USE_TZ
True
SHORT_DATETIME_FORMAT
'm/d/Y P'
TEST_NON_SERIALIZED_APPS
[]
ELASTICSEARCH_ADDRESS
'elk.corp.domain.com:9200'
PASSWORD_HASHERS
u'****'
ABSOLUTE_URL_OVERRIDES
{}
LANGUAGE_COOKIE_PATH
'/'
MIGRATION_MODULES
{}
CACHE_MIDDLEWARE_SECONDS
600
EMAIL_SSL_CERTFILE
None
CSRF_COOKIE_HTTPONLY
False
ELASTICSEARCH_LOGSTASH_ALERT_INDEX
'logstash-alert-'
USE_LOGSTASH_STATS
True
EMAIL_HOST_USER
''
Git version, I did test on 1.1.3. Not on this one. I'm gonna do it later tonight.
OK, I did just do on succesful source deletion with branch of scirius I asked you to test.
When running this patch against the SSLBL abuse.ch Sigs source which contains 93 pages of rules I am getting the same error.
insert me too
I manually imported the VRT registered rules (catted them into one large file) and I'm encountering this when attempting to delete that source from scirius.
I am currently experiencing the very same issue while trying to delete a huge sources list.
Which Scirius version are you using ? That should be fixed in the latest updates. Can you please confirm ?
Scirius CE v2.0.1
OperationalError at /rules/source/3/delete
too many SQL variables
Django Version: 1.11.11
Exception Type: OperationalError
Exception Value: too many SQL variables
Exception Location: /usr/share/python/scirius/lib/python2.7/site-packages/django/db/models/sql/compiler.py in execute_sql, line 899
Python Version: 2.7.13
The imported file has more than 300k rules (~150MB). It cannot be removed nor updated.
Out of curiosity - Is that on SELKS? (if it i you could try the latest scirius 3.0.0 available from the test repositories)
Yes, I'm currently using SELKS 4.0 but I'm considering to try the new beta release.
Should give it a try - it would be easy to test out if the issue appears with the new Scirius version.
Unfortunately, it still does not work using the latest Scirius CE v3.0.1 included in SELKS5 Beta. The signatures file cannot be uploaded through a POST request nor downloaded using an authorization key. You could try yourself and reproduce it with a large signatures file as I already said - 300k rules (~150MB). But first you must add "client_max_body_size 150M;" to the nginx.conf. After failing for several times, this is the exception you'll get:
Django Version: 1.11.16
Exception Type: OperationalError
Exception Value: database is locked
Exception Location: /usr/share/python/scirius/lib/python2.7/site-packages/django/db/backends/base/base.py in _commit, line 236
Python Version: 2.7.13
It seems (as pointed privately by @sonicold ) it is an sqlite limitation - that limitation is not present in Scirius Enterprise however as the model is a bit different - https://stackoverflow.com/questions/3172929/operationalerror-database-is-locked
If you dont mind me asking - what is the reason for 300k rules? Can that number be optimized ?
Unfortunately, no. The feed cannot be optimized at the moment.
I made 2 ET Open ruleset sources in scirius, when removing either one of them I am getting:
Environment:
Request Method: POST Request URL: http://ips.corp.domain.com:8000/rules/source/29/delete
Django Version: 1.8.17 Python Version: 2.7.5 Installed Applications: ('django.contrib.admin', 'django.contrib.auth', 'django.contrib.contenttypes', 'django.contrib.sessions', 'django.contrib.messages', 'django.contrib.staticfiles', 'django_tables2', 'bootstrap3', 'dbbackup', 'rules', 'suricata', 'accounts', 'revproxy') Installed Middleware: ('django.contrib.sessions.middleware.SessionMiddleware', 'django.middleware.common.CommonMiddleware', 'django.middleware.csrf.CsrfViewMiddleware', 'django.contrib.auth.middleware.AuthenticationMiddleware', 'django.contrib.messages.middleware.MessageMiddleware', 'django.middleware.clickjacking.XFrameOptionsMiddleware', 'scirius.loginrequired.LoginRequiredMiddleware', 'scirius.utils.TimezoneMiddleware')
Traceback: File "/usr/lib/python2.7/site-packages/django/core/handlers/base.py" in get_response
Exception Type: OperationalError at /rules/source/29/delete Exception Value: too many SQL variables