How to search by the date?

Question

I am trying to search records between two date range from database. Here is my code views:

def profile(request):
    if request.method == 'POST':
        fromdate = request.POST.get('from')
        todate = request.POST.get('to')
        cursordate = connection.cursor()
        cursordate.execute('SELECT * FROM Lesson WHERE StartDateTime between "'+fromdate+'" and "'+todate+'"'
            )
        data = dictfetchall(cursordate)
        return render(request,'personal.html',{'data':data})
    else:
        cursordate = connection.cursor()
        cursordate.execute('SELECT * FROM Lesson')
        data = dictfetchall(cursordate)
        return render(request,'personal.html',{'data':data})

html:

{% for lesson in data1 %}

<div class="card mb-3 mt-1 shadow-sm">
    <div class="card-body">
        <p class="card-text">
        <div class="row">
                <div class="col-2">
                    <div>{{ lesson.StartDateTime}} - {{ lesson.EndDateTime}}</div>
                </div>
                <div class="col-4">
                    <div>{{ lesson.Name }}</div>
                </div>
        </div>
        <div class="mt-5"></div>
        </p>

    </div>
</div>
{% endfor %}

But I get the following error:

Invalid column name "2021-03-02". (207) (SQLExecDirectW); [42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name "2021-03-03". (207)')

Is it connected with the fact that type of the 'StartDateTime' is DateTime and not Date? However,I tried to hardcord dates with time in sql query it still failed. (I know that it may be done with the help of ORM Django, but I need to use raw SQL)

You here make it possible to use SQL injection... – Willem Van Onsem Apr 13 '21 at 18:50 — Willem Van Onsem, Apr 13 '21 at 18:50

score 0 · Answer 1 · answered Apr 13 '21 at 18:53

0

You can achieve above query through ORM also and it is written as

Lesson.objects.filter(StartDateTime__gte=fromdate, StartDateTime__lt=to_date)

answered Apr 13 '21 at 18:53

Shubham Agrawal

417
2
5

score 0 · Answer 2 · answered Apr 13 '21 at 19:02

You can pass the parameters as parameters, so:

fromdate = request.POST.get('from')
todate = request.POST.get('to')
with connection.cursor() as cursordate:
    cursordate.execute(
        'SELECT * FROM Lesson WHERE StartDateTime BETWEEN %s AND %s;'
        [fromdate, todate]
    )

In case fromdate is for example '2021-03-25' and the todate is '2021-04-13', then we can filter for Lessions that start between these dates.

It however is not a good idea to work with raw queries. By formatting queries with strings as in the question, you make the web application vulnerable to SQL injection [wiki]. Furthermore it requires doing all sorts of deserializing manually.

By using the Django ORM, we can work with:

fromdate = request.POST.get('from')
todate = request.POST.get('to')
Lession.objects.filter(StartDateTime__range=(fromdate, todate))

Since we here retrieve data, and not alter/update/remove/create data, this is normally done through a GET request, not a POST request.

How to search by the date?

2 Answers2