no such function: MONTH

[dccmx]

DatabaseError at /cmdb/changes/dashboard

Environment:

Request Method: GET
Request URL: http://10.13.37.21:8000/cmdb/changes/dashboard

Django Version: 1.4
Python Version: 2.7.3
Installed Applications:
[u'django.contrib.auth',
u'django.contrib.contenttypes',
u'django.contrib.sessions',
u'django.contrib.sites',
u'django.contrib.messages',
u'django.contrib.staticfiles',
u'django.contrib.admin',
u'djcelery',
u'south',
u'lck.django.common',
u'lck.django.activitylog',
u'lck.django.profile',
u'lck.django.score',
u'lck.django.tags',
u'gunicorn',
u'fugue_icons',
u'bob',
u'tastypie',
u'ralph.account',
u'ralph.business',
u'ralph.cmdb',
u'ralph.discovery',
u'ralph.integration',
u'ralph.ui',
u'ralph.dnsedit',
u'ralph.util',
u'ajax_select',
u'powerdns']
Installed Middleware:
(u'django.middleware.gzip.GZipMiddleware',
u'django.middleware.common.CommonMiddleware',
u'django.contrib.sessions.middleware.SessionMiddleware',
u'lck.django.common.middleware.TimingMiddleware',
u'django.middleware.locale.LocaleMiddleware',
u'django.middleware.csrf.CsrfViewMiddleware',
u'django.contrib.auth.middleware.AuthenticationMiddleware',
u'django.contrib.messages.middleware.MessageMiddleware',
u'lck.django.activitylog.middleware.ActivityMiddleware',
u'lck.django.common.middleware.ForceLanguageCodeMiddleware')

Traceback:
File "/home/ralph/lib/python2.7/site-packages/django/core/handlers/base.py" in get_response

1.                     response = callback(request, _callback_args, *_callback_kwargs)
   

   File "/home/ralph/lib/python2.7/site-packages/django/contrib/auth/decorators.py" in _wrapped_view

2.             return view_func(request, _args, *_kwargs)
   

   File "/home/ralph/lib/python2.7/site-packages/django/views/generic/base.py" in view

3.         return self.dispatch(request, _args, *_kwargs)
   

   File "/home/ralph/lib/python2.7/site-packages/django/views/generic/base.py" in dispatch

4.     return handler(request, _args, *_kwargs)
   

   File "/home/ralph/project/src/ralph/cmdb/views_changes.py" in get

5.     ''')
   

   File "/home/ralph/lib/python2.7/site-packages/django/db/backends/util.py" in execute

6.         return self.cursor.execute(sql, params)
   

   File "/home/ralph/lib/python2.7/site-packages/django/db/backends/sqlite3/base.py" in execute

7.         return Database.Cursor.execute(self, query, params)
   

Exception Type: DatabaseError at /cmdb/changes/dashboard
Exception Value: no such function: MONTH

[ambv]

Thanks for your feedback. Currently the CMDB module works only on the MySQL backend, as described in http://ralph.allegrogroup.com/doc/cmdb.html#installation. We're planning to enable this feature for SQLite and PostgreSQL in a later version. Patches welcome :)

[toudi]

There is a raw SQL query in this file. i don't know how it managed to pass the code review, however, if you stick to standard django querysetting, you're good to go.

therefore, instead of:

MONTH(ch.time)=%s

use

from ralph.cmdb.models import CI

then query for the data:

now = datetime.now()
objects = CI.objects.filter( ... )
.filter(time__month = month).filter(time__month = now.year)

and agregate ( https://docs.djangoproject.com/en/dev/topics/db/aggregation/ )

unless you validate the type / kwargs['type'] argument which is defined as \w+ in urls.py, this query is vunerable for SQL injection.

[deshipu]

The raw query is not pretty, and we will eventually replace it with something more integrated with the ORM (it's a little bit trickier than it looks), but you are mistaken about it being vulnerable to SQL injection, as cursor.execute() escapes the values that are passed into it, so don't panic.

[toudi]

indeed, i was mistaken by %s in the query and i automatically assumed there was % [params] somwhere further.

i noticed that you also use sqlalchemy, so even if django itself doesn't cut it for you, you can use sqlalchemy wrapper for django's models and do

model.sa.query()

and so on.

[deshipu]

We will certainly get to that and come up with a solution that works on all supported database backends.

[xor-xor]

I'm closing this issue - there was no activity here for long enough and AFAIK, we are not going for db backends other than MySQL in the nearest future.