CODEWITHSUNDEEP
pythonflasksqlalchemysql-injection
Dr. Funkenstein
Dr. Funkenstein

Reputation: 466

Preventing SQL Injection for online SQL querying

I have a small Python project site where a visitor can practice writing SQL code. This code actually runs and returns values. I know that I need to prevent SQL injection, but I'm not sure the best approach since the purpose of the site is that users should be able to write and execute arbitrary SQL code against a real database.

What should I look to do to prevent malicious behavior? I want to prevent statements such as DROP xyz;, but users should still be able to execute code. I think maybe the ideal solution is that users can only "read" from the database, ie. they can only run SELECT statements (or variations). But I'm not sure if "read only" captures all edge cases of malicious behavior.

Any ideas or suggestions would be helpful

Upvotes: 1

Views: 84

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562881

There is no way to prevent SQL injection for a site that takes SQL statements as user input and runs them verbatim. The purpose of the site is SQL injection. The only way you can prevent SQL injection is to not develop this site.

If you do develop the site, how can you prevent malicious SQL? Answer: don't let malicious users have access to this site. Only allow trusted users to use it.

Okay, I assume you do want to develop the site and you do want to allow all users, without doing a lot of work to screen them.

Then it becomes a task of limiting the potential damage they can do. Restrict their privileges carefully, so they only have access to create objects and run queries in a specific schema.

Or better yet, launch a Docker container for each individual to have their own private database instance, and restrict the CPU and memory the container can use.

Upvotes: 1

Related Questions