SQL injection prone?

Question

I'm building a website with Django where there will be a sql console for performing raw queries on the database (SQLite3).

I know that this is a dangerous idea and that is why I'm wondering if my rather inelegant and brute solution will work.

I want the user to be able to perform only SELECT statements and therefore before the query is executed on the database I am simply making an assert statement which checks that the words 'DROP', 'UPDATE' or 'CREATE' are not in the query string and another one to assert that the word 'SELECT' is.

If this is not satisfied the query is not being processed.

Is it good enough or am I missing something here?

This is not enough. You are worried about SQL Injection, but your application is literally only SQL Injection. If you want to make sure they don't modify data, make the accessing SQL account only have read access, and only to the tables you want them to see. — Siyual, Feb 02 '17 at 15:04
Aside from the obvious exclusion of `ALTER`, You must be forever up to date with every possible security loophole that anyone ever finds in SQL - and be in a position to patch that immediately before any of your users ever find it. Personally, I'd prefer to be able to sleep — Sayse, Feb 02 '17 at 15:07
May i ask, why you want to do this? So what is the background for your decision? — trixn, Feb 02 '17 at 15:12
Well, the site is really simple. It's a home site of a science project concerned with developing couple of corpora. My idea was to made viewing and searching this corpora possible at the site level (official files are published in XML). So, the idea is that the database consists of two tables (that represensts the corpora) which will never change. All anyone would want to do is read from them. Since this is my first website and I am not very fluent in SQL I thought that maybe apart from simple UI to search tables it will be cool if there will be a console for more proficient users — tadek, Feb 03 '17 at 16:00

score 1 · Accepted Answer · edited May 23 '17 at 12:09

That would probably work fine if you're confident enough in your code. Personally, I could never be that confident.
It might be better to cut the permissions off at the user or file level, and since sqlite doesn't have users at all, we can cut off write capability on the file level.
Seen from here, if the database or folder that it resides in does not allow write permissions then they won't be able to do INSERT, UPDATE, DELETE, etc.

score 0 · Answer 2 · answered Feb 02 '17 at 15:37

From a user experience and security point of view you should consider the following points as well:

(A) Is this supposed to be an app like PHPAdmin where we talk about one user connecting to their own database?

(B) Is this a closed application where all users work on the same tables as a team and if the data is being corrupted this will affect all the users in the same way? You really must have confidence in the team that they do not want to bully each other by fishing around in Django's authentication tables.

(C) Or is this an application open for registration to the public where each user works on their own tables and should not have even the tiniest possibility to bring down the system by affecting tables vital to django (like auth_*).

If this is more about (C) than (A) then don't do this. If it's rather (B) than (A), even then - rather don't. If this is a paid project and you have to guarantee that the database won't get sniffed or corrupted and you don't want to write your own SQL parser and do sufficient security tests - you should think well about this type of feature.

Alternatively:

With Django as a tool, and depending on your business needs, it might be a valid option to create a complex form from which the user can select Django models (:= sql tables) and filters (:= joins/where clauses) to create their own reports.

If this is about a reporting tool - you might want to check out existing reporting modules for Django and the possibilities offered by the Django Admin.

SQL injection prone?

2 Answers2